14.7 How do I…Pass hints to the optimizer?
Problem
I have used EXPLAIN PLAN, the AUTOTRACE option, or SQL*Trace—presented in How-To’s 14.2, 14.3, and 14.5, respectively—to identify some poorly performing SQL statements. I think that the optimizer is making the wrong decision about how to execute some of these statements, degrading performance. How do I suggest an alternate execution path to the optimizer?
Technique
Optimizer hints reside in comment blocks; must follow the SELECT, UPDATE, INSERT, or DELETE keywords; and change the execution plan for the statement containing the hint. The syntax of an optimizer hint is /*+ hint */. Table 14.11 lists Oracle8 optimizer hints. Use the AUTOTRACE option, EXPLAIN PLAN, or SQL*Trace to determine how optimizer hints change the execution plan.
Table 14.11 Oracle8 optimizer hints
Steps
Value Effect ALL_ROWS Optimize the statement for best total throughput. AND_EQUAL Use index merging on the specified table. APPEND Append data to a table, do not use existing free space. CACHE Look in the most recently used end of the buffer cache LRU list when performing a full table scan. CHOOSE Use cost-based optimization if ANALYZE statistics exist, otherwise use rule-based optimization. FIRST_ROWS Optimize the SQL statement for best response time. CLUSTER Use a cluster scan. COST Use cost-based optimization. FULL Use a full table scan on the specified table. HASH Use a hash search on the specified table. HASH_AJ Use a hash anti-join instead of NOT IN INDEX Use the specified index on the specified table. INDEX_ASC Use the specified index in ascending order. INDEX_DESC Use the specified index in descending order. INDEX_COMBINE Use a combination of bitmap indexes. INDEX_FFS Use a fast full index scan instead of a full table scan. MERGE_AJ Use a merge anti-join instead of a NOT IN subquery. NO_APPEND Do not append data to a table, use existing free space. NO_CACHE Look in the least recently used end of the buffer cache LRU list when performing a full table scan. NO_MERGE Join each table with a sort-merge join. NO_PARALLEL Do not scan the table in parallel. ORDERED Use the join sequence from the FROM clause. PARALLEL Specify the number of slave processes with which to accomplish the operation. PARALLEL_INDEX Parallelize fast full index scans. PUSH_SUBQ Evaluate non-merged subqueries as early in the execution plan as possible. ROWID Use the ROWID access method. RULE Use rule-based optimization. STAR Join the large table last with a nested loops join on an index. USE_HASH Join each specified table with a hash join. USE_MERGE Use sort-merge join techniques on the specified table. USE_NL Use nested-loop join techniques on the specified table. USE_CONCAT Use UNION ALL instead of the combined OR condition in the WHERE clause. 1. Run SQL*Plus and connect as the WAITE user. CHP14_14.SQL, shown in Figure 14.14, creates a sample table used in this How-To. Run this script using the START command.
The first SQL statement creates a table named EMP14_7. The second statement creates an index on the EMPNO column of the table, and the third statement block, an anonymous PL/SQL procedure, populates the table with 250 rows of sample data.
2. If you haven’t already done so, follow the directions in the “Comments” section of How-To 14.3 to enable the AUTOTRACE option for the WAITE account.
3. In SQL*Plus, issue the SET AUTOTRACE ON command to reveal the execution paths of the following statements.
4. Use the START command to load and execute the CHP14_15.SQL script. The script and the AUTOTRACE output appear in Figure 14.15. The 250 rows returned by the script’s query are not reproduced here to conserve space.
Assuming that no developer or DBA has applied the ANALYZE command to this table, the Oracle optimizer would normally use a full table scan for the query in the CHP14_15.SQL script. The index, however, contains all of the columns returned by the SELECT statement so an index scan would yield better performance. The hint in the SELECT statement forces the optimizer to perform a full index scan to complete the query. 5. Use the START command to load and execute the script CHP14_16.SQL. The script and its AUTOTRACE output appear in Figure 14.16.
Adding another optimizer hint can expedite this simple query even more. Through the INDEX_FFS hint, the CHP14_16.SQL script tells Oracle to perform a fast full index scan. This type of index scan offers a performance improvement over a normal full index scan because it can utilize multi-block reads. Comparison of the COST estimate in the AUTOTRACE output for this script and the script in the last step supports this assertion.
How It Works
Step 1 creates a sample table used throughout this How-To. Step 2 configures the WAITE account for AUTOTRACE analysis and Step 3 turns on the AUTOTRACE option. Step 4 passes a hint to the optimizer to enforce an index scan rather than a full table scan. Step 5 passes a similar hint forcing Oracle8 to perform a fast full index scan.
Comments
In the hands of skilled Oracle developers, optimizer hints can generate performance improvements. SQL statement writers should observe two features of hint usage, however. The first is that the optimizer will simply ignore hints that are syntactically or operationally incorrect. For example, the optimizer will pay no attention to a “use_nl” hint when the query involves only one table. Regardless of the reason, Oracle8 will issue no message when its optimizer ignores a hint. The second caveat is that Oracle’s optimizer, particularly the cost-based optimizer, usually chooses the best execution plan. Developers should only override the Oracle optimizer with hints when armed with in-depth knowledge of the database’s contents.