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.

Read more about the 11g CBO here:


Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link: