Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Doug.

Asked: June 02, 2004 - 9:17 am UTC

Last updated: June 02, 2004 - 3:40 pm UTC

Version: 9204

Viewed 1000+ times

You Asked

Tom - if asked to explain exactly why the optimizer has chosen a particular course of action, what do you do? The only way I know is with a 100053 trace and that could require an issue with Oracle support or someome like yourself to decipher all the results. I've read that the "view selection problem" and the "index selection" problem are NP-Hard problems. Isn't optimizing a query also NP-Hard? In other words, isn't there a certain amount of experience that goes into designing the optimizer which literally has hundreds of choices to make on a complicated query. Not select * from dept; mind you but 4 way joins based on views of views joined to something else. Is it really feasible to be able to explain exactly why the optimizer did what it did?

and Tom said...

Is it feasible to explain why the optimizer did what it did?

Absolutely -- the guy I know that is best at that is Jonathan Lewis. He can say why better than most anyone else I know.

The optimizer is a mathematical model, it operates on certain inputs and gives a predicable output. The problem is the model is tweaked to the left and to the right over time (so what you knew last year might not apply this year -- things change, hopefully for the better).

I usually approach the problem query from the "optimizer assumption" point of view. That is -- give me an autotrace (which has guesstimates as to cardinality) and a TKPROF of the same. Now, look for really wide variations. If there are some -- we need to fix that (either by gathering stats 'better' or filing a 'bug' if the stats are perfect). Most of the time, that gets it.

To go deeper, the 10053 trace can be useful (i've used that to see that the optimizer wasnt even CONSIDERING a certain index the customer felt should certainly be used. That made me ask the basic question "what are the datatypes here, ah hah, implicit conversion -- it is as if there were a to_number(string) in there -- not just 'string', thats why it was so bad...) -- but you don't even need a support analyst to read much of it. Just seeing that something wasn't even considered is useful information.




Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Optimizer

Mikito Harakiri, June 02, 2004 - 3:40 pm UTC

"Database System Implementation" textbook by Garcia-Molina et al gives very good descriptin how complex query optimization really is.

Basically, if we focus onto very tiny fragment of the whole process -- enumerating physical plans -- then the number of all possible join combinations is humangous. If we assume that selection operator is aleways pushed down, and that projection is applied "in place" as well, then the number of plans reduced to number of join trees. For the query joining n tables the number of plans is n!*C_n, where C_n is Catalan number (which grows even faster than factorial). Therefore, the second simplification -- considering only left-join trees -- reduces the number of choices to n!.

"Is it really feasible to be able to
explain exactly why the optimizer did what it did?"
10053 exposes enumerating physical plans phase. All other optimiser decisions (e.g. query transformations) are untraceable as of today:-(



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.