Sub-optimal SQL and the 11g CBO

Sub-optimal SQL and the 11g CBO

The execution path for any SQL statement is only as good as the underlying statistics and it’s been long understood that histograms are the solution.

It’s very difficult for any SQL optimizer to accurately predict the cardinality of an operation, and the problem is aggravated by queries that have complex WHERE clause predicates. The goal of any SQL optimizer is to join the tables together with the proper “driving table”, such that the first join has the smallest possible result set size (cardinality), resulting in less baggage that must be passed-on to subsequent table joins.

This is especially problematic for Oracle queries that join many tables together, and DBA’s now understand that optimal table join order is not automatic. Instead, the DBA is forced to perform complex manual tuning, examining popular SQL statements and applying histograms as needed to ensure that the 11g CBO joins the tables together in an optimal fashion.

In the real world, there exists one, and only one, optimal table join order, and rather than undertake a time-consuming exercise in histogram generation, they lock-down the table join orders with the ORDERED hint or by using SQL profiles.