Oracle High Performance Tuning for 9i and 10g

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;

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 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.

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.

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.

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

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.

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.