8.8 Overriding the Optimizer using Hints | Oracle High Performance Tuning for 9i and 10g

< Day Day Up >

As we have seen in numerous places in this book so far, a hint can be used to suggest an alteration to the way that the Optimizer creates a query plan for a query. OLTP databases rarely require use of hints if statistics can be regularly maintained. Hints are more commonly used in large data warehouse or reporting databases, rule-based databases or those lacking current or any statistics. Generating statistics can be time consuming. Using the DBMS_STATS package instead of the ANALYZE command may help to alleviate some potential problems. Hints can be used to control the Optimizer and to a certain extent freeze execution plans much like outlines were used in older versions of Oracle Database. Not all available Optimizer hints will be covered in this book, only those hints which are most useful.

The syntax of a hint is such that it is placed after a DML command between comments as shown, including the plus (+) sign.

SELECT /*+ RULE */ * FROM customer; INSERT /*+ RULE */ INTO customer(customer_id, name, ticker)       VALUES(1000000, 'New Customer', 'TICK'); UPDATE /*+ RULE */ customer SET name = 'A New Customer'       WHERE customer_id = 1000000; DELETE /*+ RULE */ FROM customer WHERE customer_id = 1000000;

Note

Oracle Database 10 Grid The RULE hint is desupported and scheduled for deprecation in a future release of Oracle Database.

If an alias is used in an SQL statement then any hints must refer to the alias and not the table. The first example following does not use any indexes on the customer table due to the NO_INDEX hint. The second does use an index and the hint suggested it not do as such. The hint in the second query is incorrectly specified since it uses the table name and not the alias for the Customer table, "c".

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ NO_INDEX(c) */ customer_id FROM customer c             NATURAL JOIN transactions;     Query                               Cost      Rows       Bytes -------------------------------   ------   -------   --------- 1. SELECT STATEMENT on               126     64599      322995 2.  HASH JOIN on                     126     64599      322995 3.   TABLE ACCESS FULL on CUSTOMER    16      2694        8082 3.   INDEX FAST FULL SCAN on         XFK_T_CUSTOM 44 64599 129198     EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ NO_INDEX(customer) */ customer_id FROM           customer c             NATURAL JOIN transactions;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on             44     64599      322995 2.  NESTED LOOPS on                44     64599      322995 3.   INDEX FAST FULL SCAN on         XFK_T_CUSTOM                44     64599      129198 3.   INDEX UNIQUE SCAN on         XPK_CUSTOMER                           1           3

It is always a good idea to check query plans for SQL statements if only to insure that hints are coded in a syntactically correct manner. Incorrectly coded hints do not produce SQL code parser errors and may never be detected. This SQL statement will execute.

SELECT /*+ STUPIDHINT */ * FROM customer;

Here is a query plan using an index fast full scan.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT customer_id FROM customer;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              3      2694        8082 2.  INDEX FAST FULL SCAN on        XPK_CUSTOMER                  3      2694        8082

Applying the RULE hint suggests that the Optimizer ignore statistics and use rule-based optimization.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ RULE */ customer_id FROM customer;

Note

Oracle Database 10 Grid The RULE hint is desupported and scheduled for deprecation in a future release of Oracle Database.

Note that cost, row, and byte figures are not shown using rule- based optimization because there are no statistics to work with. A full table scan is assumed to be the fastest option.

Query                                  Cost      Rows       Bytes ----------------------------------   ------   -------   --------- 1. SELECT STATEMENT on 2.  TABLE ACCESS FULL on CUSTOMER

We can influence the rule-based Optimizer to use any index on the Customer table.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ RULE INDEX(customer) */ customer_id FROM           customer;

Once again we get the index fast full scan except that the cost is a little higher to override the rule-based Optimizer.

Query                                  Cost      Rows       Bytes ----------------------------------   ------   -------   --------- 1. SELECT STATEMENT on                    7      2694        8082 2.  INDEX FULL SCAN on XPK_CUSTOMER       7      2694        8082

8.8.1 Classifying Hints

There are all sorts of hints for persuading the Optimizer to do things differently. I like to categorize hints as follows.

Influence the Optimizer.
Change table scans.
Change index scans.
Change joins.
Parallel SQL.
Change queries and subqueries.
Oracle Database 10 Grid Other hints.

This book will not cover all of the available hints in Oracle Database, only the interesting ones. Most importantly we will examine hints that are potentially useful within the scope of the subject matter presented in this book. Some hints have already been covered in this book, some even in prior chapters. Repetition is avoided where possible.

8.8.2 Influence the Optimizer

Available hints:

ALL_ROWS. Suppresses indexes and favors full table scans to find all rows.
FIRST_ROWS(n). More likely to use indexing since favoring a few rows.
CHOOSE. Choose cost-based optimization when statistics present; otherwise use rule-based optimization.
RULE. Rule-based optimization.
CURSOR_SHARING_EXACT. Overrides behavior of configuration parameter CURSOR_SHARING=FORCE which changes literal values to bind variables.
DYNAMIC_SAMPLING. "On the fly" sampling if statistics not present or out of date.

Setting OPTIMIZER_MODE=CHOOSE is the most common setting for optimization in an OLTP database. The CHOOSE option will favor cost-based optimization and use of statistics. However, CHOOSE will tend to favor full scans rather than more precise index scans.

Tip

The OPTIMIZER_MODE parameter can be altered at the session level using a command such as ALTER SESSION SET OPTIMIZER_MODE=RULE;

Note

Oracle Database 10 Grid The RULE and CHOOSE hints are desupported and scheduled for deprecation in a future release of Oracle Database. The default for the OPTIMIZER_MODE parameter is now ALL_ROWS.

Let's experiment a little. My database is set to CHOOSE. Note the difference in cost in the two following queries. The chances are the second query will be slower since it is reading all the rows from the table and the hint is stating otherwise. These two examples demonstrate the difference made by the hint.

EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM     generalledger;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on           1128   1068929    24585367 2.  TABLE ACCESS FULL on        GENERALLEDGER              1128   1068929    24585367     EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ FIRST_ROWS(1) */ * FROM generalledger;

Using the hint the cost is misleading since the query will still return all rows in the GeneralLedger table, which is very large.

Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              2         1           2 2.  TABLE ACCESS FULL on        GENERALLEDGER                 2         1           2

8.8.3 Change Table Scans

Available hints:

FULL. Suggest a full table scan. Typically the FULL hint could be used on small static tables since it might sometimes be faster to read the entire table rather than read both the index and the table.

Let's play. The first query uses an index range scan. The second query suggests a full table scan, has fewer steps and lowers the number of bytes read.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM category WHERE category_id < 50;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              2        13         143 2.  TABLE ACCESS BY INDEX ROWID        on CATEGO                     2        13         143 3.   INDEX RANGE SCAN on         XPK_CATEGORY 1 13     EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ FULL(category) */ category_id FROM           category       WHERE category_id < 50;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              2        13          26 2.  TABLE ACCESS FULL on CATEGORY   2        13          26

8.8.4 Change Index Scans

Available hints:

INDEX[_ASC | _DESC]. Forces use of an index where if multiple indexes are specified then the lowest-cost index is selected. The selected index is scanned as it is sorted, or as specified in ascending or descending order.
INDEX_FFS. Forces a fast full index scan, reading the index in physical block order not key order.
NO_INDEX. Ignores the named indexes.
INDEX_COMBINE. Typically used to combine use of single-column bitmap indexes.
INDEX_JOIN. Suggests a join between indexes alone.
AND_EQUAL. Join multiple index single-column scans.

Note
Oracle Database 10 Grid The AND_EQUAL hint is deprecated.
Oracle Database 10 Grid INDEX_SS[_ASC | DESC]. Suggests an index skip scan.
Oracle Database 10 Grid NO_INDEX_FFS and NO_INDEX_SS. These hints suggest the Optimizer does not use index fast full scans or index skip scans respectively.
Oracle Database 10 Grid USE_NL_WITH_INDEX. Suggests a query uses a specific table as the inner loop of a nested loops join, with the option of using a particular index.

These are the indexes on the PeriodSum table.

PERIODSUM    BTree    XFK_PS_COA          COA#       1 PERIODSUM    BTree    XFK_PS_YEARPERIOD   YEAR       1 PERIODSUM    BTree    XFK_PS_YEARPERIOD   PERIOD     2 PERIODSUM    BTree    XPK_PERIODSUM       YEAR       1 PERIODSUM    BTree    XPK_PERIODSUM       PERIOD     2 PERIODSUM    BTree    XPK_PERIODSUM       COA#       3

The query plan for this SQL statement uses a full table scan because all the columns in all the rows are being read.

EXPLAIN PLAN SET statement_id= 'TEST' FOR SELECT * FROM        periodsum;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              2        45         675 2.  TABLE ACCESS FULL on PERIODSUM  2        45         675

The next query suggests use of the most efficient index available. The primary key index is unique and thus the most efficient. There is no change in cost. The Optimizer was correct to choose the full table scan by default since reading index and table is probably slightly slower than reading just the table.

EXPLAIN PLAN SET statement_id= 'TEST' FOR       SELECT /*+ INDEX(periodsum) */ * FROM periodsum;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              2        45         675 2.  TABLE ACCESS BY INDEX ROWID        on PERIOD                     2        45         675 3.   INDEX FULL SCAN on        XPK_PERIODSUM                1        45

This query removes the primary key index from the equation by suggesting that the Optimizer chooses between the more efficient of two others. The cost is slightly higher.

EXPLAIN PLAN SET statement_id= 'TEST' FOR       SELECT /*+ INDEX(periodsum, xfk_ps_coa           xfk_ps_yearperiod) */ *       FROM periodsum;     Query                               Cost      Rows       Bytes -------------------------------   ------   -------   --------- 1. SELECT STATEMENT on                 4        45         675 2.  TABLE ACCESS BY INDEX ROWID        on PERIOD 4 45 675 3.   INDEX FULL SCAN on XFK_PS_COA     3        45

Now we suggest the use of the foreign key to the PeriodSum table and show it is higher in cost than using the foreign key to the COA table. Thus the Optimizer selected the best key as being the foreign key to the COA table in the previous query.

EXPLAIN PLAN SET statement_id= 'TEST' FOR       SELECT /*+ INDEX(periodsum, xfk_ps_yearperiod) */             * FROM periodsum;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              5        45         675 2.  TABLE ACCESS BY INDEX ROWID        on PERIOD                     5        45         675 3.   INDEX FULL SCAN on         XFK_PS_YEARPERIOD            4        45

We could also use the INDEX_DESC hint to scan an index in reverse order.

EXPLAIN PLAN SET statement_id= 'TEST' FOR       SELECT /*+ INDEX_DESC(customer) */ customer_id FROM           customer;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on              7      2694        8082 2.  INDEX FULL SCAN DESCENDING on        XPK_CUS                       7      2694        8082

The AND_EQUAL hint has not been covered as of yet. This first example uses the foreign key index on the ORDER_ID column.

Note

Oracle Database 10 Grid The AND_EQUAL hint is deprecated.

EXPLAIN PLAN SET statement_id= 'TEST' FOR       SELECT * FROM transactions WHERE type = 'S' AND          order_id = 10;     Query                               Cost      Rows       Bytes -------------------------------   ------   -------   --------- 1. SELECT STATEMENT on                 2         1          36 2.  TABLE ACCESS BY INDEX ROWID        on TRANSA                        2         1          36 3.   INDEX RANGE SCAN on XFK_T_ORDERS            1           1

The AND_EQUAL hint causes an alteration to the previous example by suggesting use of both the TYPE and ORDER_ID foreign key indexes. It does not help the cost but this is what the hint does.

Tip

The cost-based Optimizer is very intelligent and sophisticated in Oracle9i Database. Do not ever assume that by using hints your SQL code will be faster. ALWAYS check the query plan with the EXPLAIN PLAN command when using hints.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ AND_EQUAL(transactions xfk_t_type           xfk_t_orders) */ *       FROM transactions WHERE type = 'S' AND order_id = 10;     Query                                 Cost      Rows       Bytes ---------------------------------   ------   -------   --------- 1. SELECT STATEMENT on                 258         1          36 2.  TABLE ACCESS BY INDEX ROWID        on TRANSA                        258         1          36 3.   AND-EQUAL on 4.    INDEX RANGE SCAN on          XFK_T_ORDERS 4.    INDEX RANGE SCAN on XFK_T_TYPE   256    121292

Note

Oracle Database 10 Grid Index hints include index names or table names with column lists. Table.COLUMN name settings can also be used even with columns in separate tables for join indexes.

8.8.5 Change Joins

Available hints:

ORDERED. Makes the Optimizer join tables in the sequence that tables appear in the FROM clause of an SQL statement, amongst other uses (refer to the section on DISTINCT on p. 276).
LEADING. Makes the Optimizer use a named table as the first table in the join, regardless of which table occurs first in the FROM clause.
USE_NL, USE_HASH and USE_MERGE. Suggest nested loops, hash join, or sort merge join, respectively.
Oracle Database 10 Grid NO_USE_NL, NO_USE_HASH, and NO_USE_MERGE. These hints suggest that the Optimizer does not use nested loops, hash joins, or sort merge joins respectively in a particular query.
NL_AJ, HASH_AJ, MERGE_AJ, NL_SJ, HASH_SJ, MERGE_SJ. All these hints change the join type for semi-and anti-join subqueries.

Oracle Database 10 Grid All of the nested loop, hash, and sort merge join anti-and semi-join hints are deprecated.

We have already seen the ORDERED hint in this chapter but I will reiterate with a different example because it is important. This first example does not use the ORDERED hint and accesses tables with the smaller of the COA and GeneralLedger tables in the outer loop.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT * FROM generalledger gl, coa WHERE coa.coa# =           gl.coa#;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on           1642   1068929    50239663 2.  HASH JOIN on                 1642   1068929    50239663 3.   TABLE ACCESS FULL on COA       2        55        1320 3.   TABLE ACCESS FULL on         GENERALLEDGER             1128   1068929    24585367

This second example applies the ORDERED hint and changes the order in which the Optimizer accesses tables based on the sequence of tables in the FROM clause.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ ORDERED */ * FROM generalledger gl, coa       WHERE coa.coa# = gl.coa#;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on           4026   1068929    50239663 2.  HASH JOIN on                 4026   1068929    50239663 3.   TABLE ACCESS FULL on         GENERALLEDGER             1128   1068929    24585367 3.   TABLE ACCESS FULL on COA       2        55        1320

In the next example not all tables are switched according to the FROM clause. The ORDERED hint manages to switch the Transactions and TransactionsLine tables. It does not change the sequence for the Customer table.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ ORDERED */ *       FROM transactionsline tl, transactions t, customer c       WHERE t.transaction_id = tl.transaction_id       AND c.customer_id = t.customer_id;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on          27327    273552    50880672 2.  HASH JOIN on                27327    273552    50880672 3.   TABLE ACCESS FULL on         CUSTOMER                    16      2694      360996 3.   MERGE JOIN on              20905    273552    14224704 4.    SORT JOIN on              18997   1027251    16436016 5.     TABLE ACCESS FULL on           TRANSACTIONSLINE         800   1027251    16436016 4.    SORT JOIN on               1908     64599     2325564 5.     TABLE ACCESS FULL on           TRANSACTIONS             390     64599     2325564

The LEADING hint uses a named table as the first table in the join.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT /*+ LEADING(tl) */ *       FROM transactionsline tl, transactions t, customer c       WHERE t.transaction_id = tl.transaction_id       AND c.customer_id = t.customer_id;     Query                            Cost      Rows       Bytes ----------------------------   ------   -------   --------- 1. SELECT STATEMENT on          26143    273552    50880672 2.  HASH JOIN on                26143    273552    50880672 3.   MERGE JOIN on              20905    273552    14224704 4.    SORT JOIN on              18997   1027251    16436016 5.     TABLE ACCESS FULL on           TRANSACTIONSLINE         800   1027251    16436016 4.    SORT JOIN on               1908     64599     2325564 5.     TABLE ACCESS FULL on           TRANSACTIONS             390     64599     2325564 3.  TABLE ACCESS FULL on        CUSTOMER                     16      2694      360996

8.8.6 Parallel SQL

Available hints:

[NO]PARALLEL. Parallel execution on multiple CPUs or servers.
PQ_DISTRIBUTE. Improves a parallel join.
[NO]PARALLEL_INDEX. Process index scans for partitions in parallel.

Note
Oracle Database 10 Grid The NOPARALLEL and NOPARALLEL_INDEX hints are renamed to NO_PARALLEL and NO_PARALLEL_INDEX, respectively.

Previously in this chapter we examined performance using parallel settings on both full table scans and fast full index scans. Parallelism was detrimental to performance. Parallel SQL statements are only beneficial in very large data warehouse databases. Using parallelism with Oracle Partitioning and multiple disks will be examined in Part III.

8.8.7 Changing Queries and Subqueries

Available hints:

[NO]CACHE. CACHE can be used to suggest that data be forced into a Most Recently Used list (MRU). NOCACHE pushes data to a Least Recently Used list (LRU).
ORDERED_PREDICATES. Preserves SQL statement precedence of evaluation such as the sequence of comparisons in a WHERE clause.
[NO_]UNNEST. Undoes subquery layers by allowing the Optimizer to attempt to merge subqueries into a calling query. This is probably contrary to performance tuning of multi-layered SQL statements.
[NO_]PUSH_SUBQ. Resolves subqueries first or last (NO_).

8.8.8 Other Hints

Oracle Database 10 Grid The CPU_COSTING hint fills a column in the PLAN_TABLE called CPU_COST based on CPU cycles and I/O operations.

That is enough about hints. Remember one thing. A hint suggests rather than instructs the Optimizer. Therefore, the Optimizer may not necessarily take notice of the hint.

Tip

A hint is only a suggestion to the Optimizer, not an instruction.

This concludes this chapter of the Oracle Database internal specifics of constructing efficient SQL code. The next chapter will look at how to detect problems with SQL code in the database.

< Day Day Up >