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.