Does the Optimizer Need a Hint?
Does the optimizer need a hint? That was the subject of the Ask the Oracles feature in the August 2006 issue of the NoCOUG Journal; the contributors were Gaja Krishna Vaidyanatha, Guy Harrison, Jonathan Lewis, Cary Millsap, Chris Lawson, and Dan Tow. Here is the complete article; Dan Tow later posted a longer version of his essay on his website.
Recently I used hints to tune a query that was using two minutes of execution time. The query was the union of more than a dozen subqueries and each subquery joined more than a dozen tables but the hinted version required only one hundredth of a second of execution time! When the developer made the changes to his program, the query still used 25 seconds of execution time. Was Oracle ignoring some of the hints?
Every hint constrains or directs the optimizer in some way and you need to completely constrain the optimizer completely if you want it to do precisely what you want. Jonathan’s recent presentation Hints on Hints provides more information on the subject. If you tell your son not to touch the cookie jar, you leave open the option that he could ask his friend to open the cookie jar and give him some cookies; this was the example used by Jonathan at the Collaborate 09 conference. And, of course, invalid hints have no effect on the optimizer.
In his essay, Gaja recommends that we always confirm that Oracle is doing what we want it to do. One obvious way is to check the query execution plan. Jonathan found that the undocumented OUTLINE and ADVANCED options of the DBMS_XPLAN.DISPLAY_CURSOR function will display “outline” information; that is, a complete set of hints that will reproduce a plan. It turned out that the developer had misspelled some of the hints and table aliases in the query.
The following query lists all employees who make more than their managers. You can try it for yourself if your database contains the HR sample schema. In keeping with Jonathan’s advice, I used multiple hints for each table. I also kept Dan’s advice in mind: “When performing manual tuning—which generally is only necessary for the worst SQL—tune only with the intention of creating 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 Optimizer does it often! This plan may degrade horribly if any of those assumptions or datapoints are even moderately wrong. Such plans are not robust, although they may be technically “optimal”—i.e., fastest—for now. However, in my whole career, 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!”
SET SQLBLANKLINES ON SELECT /*+ QB_NAME(main) LEADING(e1 e2 d) FULL(e1) INDEX(e2) USE_NL(e2) INDEX(d) USE_NL(d) */ e1.last_name || ', ' || e1.first_name AS employee_name , e1.salary AS employee_salary , e2.last_name || ', ' || e2.first_name AS manager_name , e2.salary AS manager_salary , d.department_name FROM employees e1 , employees e2 , departments d WHERE e2.employee_id = e1.manager_id AND e2.salary < e1.salary AND d.department_id = e1.department_id;
The following query should be executed immediately afterwards to retrieve the query plan and the execution statistics from the “fixed views.” You will need SELECT privilege on V$SESSION, V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN to execute this query.
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor( NULL, NULL, 'ADVANCED IOSTATS LAST'));
Here is what the “Outline Data” section showed when I tried the query. Notice that the original hints have been properly expanded to include the query block name wherever appropriate.
Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.1') ALL_ROWS OUTLINE_LEAF(@"MAIN") OUTLINE(@"MAIN") FULL(@"MAIN" "E1"@"MAIN") INDEX(@"MAIN" "E2"@"MAIN" ("EMPLOYEES"."EMPLOYEE_ID")) INDEX(@"MAIN" "D"@"MAIN" ("DEPARTMENTS"."DEPARTMENT_ID")) LEADING(@"MAIN" "E1"@"MAIN" "E2"@"MAIN" "D"@"MAIN") USE_NL(@"MAIN" "E2"@"MAIN") USE_NL(@"MAIN" "D"@"MAIN") END_OUTLINE_DATA */