As far as I understand, given a query, Oracle tries different approaches based on some heuristics, where I guess the most driving factor is minimizing the number of rows to work with, and then chooses the best one based on that and other factors. When you write EXPLAIN PLAN, it gives you the finally chosen plan.

Is it possible to see the other alternatives that Oracle has tried while choosing the estimated execution plan? To research things like: if I have a “theory” about this way being more efficient and I want to know why Oracle didn’t try it that way, I could “explore” the other alternatives, look for the one that matches my expectations, and then compare their estimated costs and cardinalities to understand why Oracle thought the other plan was worst.

Is it possible to get more info about the rationale behind the chosen estimated plan and/or compare the different plan alternatives to understand its final decision?