Ny way to trace the optimizer’s work in Oracle

The recent 5.6 version has added this feature.

See: MySQL Internals Manual ::chapter 9. Tracing the Optimizer

Typical Usage:

# Turn tracing on (it's off by default):
SET optimizer_trace="enabled=on";

SELECT ...; # your query here

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# possibly more queries...

# When done with tracing, disable it:
SET optimizer_trace="enabled=off";

You can see it in action in SQL-Fiddle. Not sure how the output can be beautified.

We know that the memo structure is pruned and some expensive alternative plans are discarded during optimization. I was wondering if there is any way to prevent this and let the optimizer just consider every possible plan and select the best from all alternatives?

There is, but I don't publicise it because it would be misunderstood and misapplied. In any case, it would not result in an exhaustive search of the plan space because only a limited set of transformations (ones that commonly produce good results) are implemented.

Preventing pruning and discarding would generally simply result in (much) longer compilation times without much of an improvement in final plan quality, if any.

Ultimately, the question is a natural and reasonable one, but it is predicated on a misunderstanding of the goals of the SQL Server query optimizer: it is designed to find good plans for common queries quickly. It is not built on a framework designed for exhaustive search.

If you have a real-world situation that would benefit from a different approach to optimization, you could make the case for it at the Connect website (though I do think it unlikely Microsoft would invest the necessary engineering resources).