Day 10: The Twelve Days of SQL: Sometimes the optimizer needs a hint
On the tenth day of Christmas, my true love gave to me
Ten lords a-leaping.
Day 10: Sometimes the optimizer needs a hint (Day 9: Physical database design matters)(Day 11: AWR and Statspack are a goldmine of historical performance data)
Questioning the capabilities of the Oracle optimizer is considered heresy by some. When I submitted a paper containing some of the material in this series to a large user group conference a few years ago, the reviewer took offence and responded: “I was disappointed that you quoted Fabian Pascal’s example of ‘8 Semantically Equivalent Queries’ asking whether they would all result in the same execution plan. Being involved in this kind of optimization, I can tell you that the answer depends upon which version of the DBMS you are using. Until quite recently, we frequently used inline queries but the Optimizer is significantly smarter in version 10g R2 and some complex SQL refactoring became unnecessary.”
Therefore if I were to claim that “No optimizer is perfect and directives such as Oracle’s hints provide the simplest workaround [in] situations in which the optimizer has chosen a suboptimal plan” then I might be booed for biting the hand that is good to me.
Ditto if I were to claim that “Hints are useful tools not just to remedy an occasional suboptimal plan, but also for users who want to experiment with access paths, or simply have full control over the execution of a query.”
Fortunately it is not I who makes these claims but Oracle Corp itself. I was quoting from the Oracle Corp white paper Query Optimization in Oracle Database 10g Release 2 (June 2005) and its previous incarnation Query Optimization in Oracle9i (February 2002). The white paper is still available on the Oracle Corp web site; for example, see http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/dbbi-tech-info-optmztn-092214.html. In other words, the public Oracle Corp stance on the subject of hints has been unchanged for ten years at time of writing.
I would prefer to boldly state what I have learned from experience instead of quoting others liberally as I have done in this series. As Emerson says in his essay on Self-Reliance: “Man is timid and apologetic; he is no longer upright; he dares not say ‘I think,’ ‘I am,’ but quotes some saint or sage. … We are like children who repeat by rote the sentences of grandames and tutors, and, as they grow older, of the men of talents and character they chance to see, – painfully recollecting the exact words they spoke.” But as he also pointed out “Yet see what strong intellects dare not yet hear God himself, unless he speak the phraseology of I know not what David, or Jeremiah, or Paul.” In other words, we trust saints and sages more than evidence and experience. So be it.
Instead of muddying the Oracle Corp message with additional commentary of my own, I will limit myself to repeating the message with a few choice words emphasized for your consideration: “No optimizer is perfect and directives such as Oracle’s hints provide the simplest workaround [in] situations in which the optimizer has chosen a suboptimal plan. Hints are useful tools not just to remedy an occasional suboptimal plan, but also for users who want to experiment with access paths, or simply have full control over the execution of a query.”
Hints must be understood in the broader context of manual SQL tuning. The best presentation I have ever seen on the subject of manual SQL tuning is Dan Tow’s presentation “The Case for Manual SQL Tuning” at NoCOUG Conference #100 at the Computer History Museum in November 2011 (click here to see all the presentations). With the permission of Dan, I have reproduced the content of his first two slides below but encourage you to download and read his entire presentation. I also recommend Dan’s book which is simply called SQL Tuning.
- Oracle’s Cost-based Optimizer (CBO) does a perfectly good job on most SQL, requiring no manual tuning for most SQL.
- The CBO must parse quickly, use the data and indexes that it has, make assumptions about what it does not know, and deliver exactly the result that the SQL calls for.
- On a small fraction of the SQL, the constraints on the CBO result in a performance problem.
- Find SQL worth tuning, ignoring the great majority that already performs just fine.
- Find the true optimum execution plan (or at least one you verify is fast enough), manually, without the CBO’s constraints or assumptions.
- Compare your manually chosen execution plan, and its resulting performance, with the CBO’s plan and consider why the CBO did not select your plan, if it did not.
- Choose a solution that solves the problem.
You should begin your study of hints by reading the chapter on hints in the Performance Tuning Guide. You can then refer to the detailed explanations of each hint in the SQL Reference Manual. Not all hints are documented though.
P.S. The cutest trick that I have ever seen with hints, is injecting a hint into a SQL statement that cannot be modified. Refer to Randolf Geist’s post http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html and Kerry Osborne’s post http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/. If using SQL Plan Management in Oracle Database 11g you can use the procedure provided in the Oracle Corp white paper “Upgrading from Oracle Database 10g to 11g: What to expect from the Optimizer” to add a hinted plan to the plan history of a SQL statement.