Toad World® Forums

Optimizer question


#1

Realizing this isn’t strictly a TOAD question, but you have “connections”…

Using the Quest SQL optimizer 8.1 for a poorly performing query… I started it up to see if it could do anything with a poorly performing query I’ve been puzzling over. It analyzed the query, came up with a ton of alternatives and started
testing. I notice that in the old “Auto Optimize” it didn’t seem to test them in any recognizable order, but in 8.1 it seems to be testing in the order they were generated, even though many early alternatives had costs in the millions higher and some later
alternates had lower cost. Is there a way to control the order it tests in? I couldn’t find one, or at least didn’t recognize anything.

Another tuning question: I know “cost” isn’t as meaningful an indicator as it used to be – sometimes high costs can be faster than low costs and vice versa, “it depends”, and sometimes you just have to test it, hence the auto optimizer,
but is “cost” even a meaningful yard stick anymore? Sometimes an awful looking explain plan blazes and forcing a plan that looks good performs poorly. I guess the question is – “What’s up with that?” (said in a very Jerry Seinfeld-ish voice)

Chris Johnson

IT Systems Analyst

Distribution Logistics

615-855-4371


#2

You are correct that cost is not an absolute. You can tell SQL Optimizer to sort instead on things like logical reads, elapsed time, physical reads or whatever you find most useful. However cost still does have some use. Let’s say I choose plan X because the elapsed time was best and other factors were not too bad. Now as I look into where plan X spends its time, seeing the breakdown of the cost vs. the steps could still be useful. So let’s not throw cost out the window entirely just yet :slight_smile:


#3

Well, for example, my original query with a cost of 161,433 runs in an attrocious 9:19. It’s a huge query linking almost a dozen tables, half of which have millions of rows each, a couple across a dblink, so all things considered, really not that bad, but not acceptable to me.

An alternative with a cost of 2,258,187,068 ran in 5:01, but an alternate with a cost of 49,994 took 9:24. What the what???


#4

Hi Chris,

The options to control the test run order can be found in the Options window under this page:

Optimize SQL | Batch Run | Order and Termination

The default is to use the “Intelligent order” in which Optimizer always tries to run the next SQL that has a cost different (in most) from those executed. Let say that you have these SQL generated:

SQL1 - cost = 1000

SQL2 - cost = 1001

SQL3 - cost = 99999

SQL4 - cost = 5000

Then the execution order would be like this: SQL1 -> SQL4 -> SQL3 -> SQL2

The other option available is to use the “Plan cost” in which Optimizer will run the SQL with the lowest cost first.

Thanks,

Alex


#5

www.toadworld.com/…/default.aspx

Please refer to my blog for detail discussion on this topic.

The current database cost estimation model has limitations, don’t expect it will always give good estimation especially for complex SQL. But most people don’t understand their limitations and take it too serious…

Richard


#6

As Client Eastwood as Dirty Harry famously said “A man’s got to know his limitations …”


#7

Bert, as always, you’re a hoot!

Thanks for the insights Richard. It sure seems like based on the reality of a cost of 196k running for 9 minutes and a cost of 2.7 Billion running in 2 1/2 minutes that costs are misleading at best and borderline useless without actual testing.

I have a new found respect for Quest SQL Optimizer 8.8.1 though. Previously I only resorted to the tuning lab if I couldn’t find a solution myself, but I always just looked at the cost and patted myself on the back when it never found a better cost than my manual solution, but I don’t think I ever really allowed it to test the alternatives due to ignorance. Now that I’ve taken some time to study it and look at the alternative models I understand what it’s doing. Although its’ approach seems random when you don’t know what you are looking at it’s actually quite logical and elegant. So far it found three faster solutions – 66-ish% faster, and looking at what it is doing it is using hints to do what would take me probably hours to do exactly the same thing breaking it down in pl/sql as nested loops. I have a long standing distrust of hints, but if it makes my code run 66% faster I’ll live with it. ;o)