Why Good Optimizers Do Need a Hint

Why Good Optimizers Do Need a Hint (Sometimes!)

Some say that Oracle’s cost-based optimizer (CBO) has ended the need for manual SQL tuning. When I hear this perspective, I imagine telling a fellow running an auto-body repair shop, “Well, auto-body repair is completely unnecessary – people should just avoid accidents!” It may sound true in theory, yet somehow this non-existent need puts bread on the fellow’s table, just as manual SQL tuning has provided 75% of my own livelihood for years. A SQL statement that causes performance problems for the application can connect to two common root causes:

Someone outside of RDBMS development made a mistake. This might be bad application or database design, missing indexes, a subtle error in the SQL functionality, or a DBA mistake, among others. Even a perfect CBO would not solve these SQL tuning problems.
Even with a perfect configuration and statistics, the CBO is sometimes unable, today, to choose the right plan without a functionality-neutral change to the SQL, usually hints.

These are both common, real-world sources of poorly-performing SQL, with about 20% falling in type-2. Just as automobile accidents happen, these problems also happen, in the real world, and manual tuning of the worst-offending SQL is a highly efficient means to identify and resolve both types of problems, without wasting time on the 99% of the SQL that runs well under the usual application implementation and excellent CBO.

However, only problem 2 directly bears on the question “Does the optimizer need a hint?” (so it usually does not). There are many reasons even an excellent CBO can need hints to deliver a good enough plan. It is enough to know that the human tuner working at length on a short list of top-SQL can know much more than the optimizer can see or consider during a sub-second parse step – to ask the CBO to entirely replace human tuning even for just the type-2 problems, above, is to ask it to win (or tie) every time even against well-trained, experienced human tuners who enjoy enormous built-in advantages.

The uncontroversial “intentional” hints, such as FIRST_ROWS and ALL_ROWS, help even a hypothetical perfect optimizer, conveying the developers’ objective without limiting the CBO’s choices. In contrast, the more controversial plan-limiting hints may help, today, but they can prevent future releases of the optimizer from making even better choices. Furthermore, a plan constrained by hints has less freedom to adapt to changes to the data distributions that may make today’s good plan terrible a year from now. Plan-limiting hints are a two-edged sword, but if you limit hints as follows they do far more good than harm:

Don’t tune with hints just for minor improvements to the SQL runtime. A fix that is at least a two-fold runtime improvement is a big, juicy “bird in the hand,” compared to the merest possibility of still further improvements worth some fractional “bird” in some hypothetical future “bush.” Likely, the application evolution will change the SQL before it matters, anyway!
When manually tuning (which generally is only necessary for the top SQL), tune only to create SQL with robust execution plans. It is easy to choose an execution plan that is sensitively dependent on a dozen assumptions and data-points being precisely correct (the CBO does it often!). This plan may degrade horribly if any of those assumptions or data-points are ever even moderately wrong. Such plans are not robust, although they may be technically “optimal,” (fastest) for now. However, in my whole career focused on SQL tuning, I haven’t once needed a non-robust plan to get good enough performance. In practice, robust plans usually follow well-indexed paths and nested-loops joins to the larger tables, in a well-chosen join order, and these robust plans almost never need to change! (It is easy to create hypothetical counter-examples to that last statement. However, the statement is based not on theory, but on over a dozen years of tuning real SQL in dozens of real applications and not seeing a well-chosen, robust plan fail in response to data-distribution changes even once!)

Hint carefully, but, when justified, by all means hint!

Home