Can I get some rationale from the Optimzer why it chose a certain explain plan? 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.
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.