Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is not about reading an explain plan.

This is not about adding any hints or measuring performance of a specific query.

I have always been wondering whether it's possible for a given explain plan for a query (or an autotrace of the query) to "ask" the Optimizer why it chose this plan vs. other plans available to it.

  • Is there a way to ask the database to display a comparison of different plans that it considered for the same query?
  • Is there a way to view what calculations went into the cost displayed for a plan? (For examples, what the Optimizer assumed wrt. disk access vs. in memory stuff, etc.)

Rationale for this question: With the EXPLAIN PLAN as I know it, when the database chooses a certain execution plan, and this is insufficient/too slow/... at the first moment a DBA or developer does not ad hoc know: Whether there were any alternatives to this plan at all and furthermore doesn't know why, if the developer knows of alternatives (e.g. an unused index), the alternatives weren't used.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Yes, you can.

This is called tracing the Oracle optimizer. Doing so creates a trace file in which the optimizer dumps all reasoning done when composing a plan.

An example of generating a trace for a specific SQL can be found here how to trace optimizer for specific SQL system wide - 10053 trace event

You might need a little time to read it, given the amount of data found in the trace, it is amazing that the Oracle even finds time to do this and return in a decent time.

share|improve this answer

Oracle's Optimizer is an entire science in itself. In addition to @ik_zelf, you should look at TKPROF which helps make .trc files human-readable. Check out here and here for books by Cary Millsap (Oracle trace mainly). Check out Jonathan Lewis's book and his blog for lots about the Oracle optimizer. Lewis's book is 536 pages long (it's only called Fundamentals!) and he said he was going to write another one (hasn't appeared yet). There is also a concept called "plan stability" (Google it) where you can fix the plan if you're happy with performance and don't want the optimizer to "second-guess" you, thereby sometimes producing sub-optimal plans. This ideas seems to be along the lines of "better the devil you know than the one you don't" - i.e. better a sub-optimal plan that works reasonably well than one which _might_ run brilliantly, but at the risk of gumming up the works if the optimizer goes haywire.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.