Improving on EXPLAIN PLAN Results

If you don't like the results you get from EXPLAIN PLAN, you can change how Oracle executes your query. Generally speaking, these things fall into the following three categories:

• Restating the query
• Creating or modifying indexes
• Using hints

First, however, you have to be sure the default execution path is a bad one. This isn't as easy as you may think.

12.4.1 Knowing Good Results from Bad

Knowing a good execution plan from a bad one requires some degree of experience and judgment. It helps to understand your data. In many cases, it may not be enough to look at the plan. You may have to do some benchmarking as well.

Consider the issue of doing a full table scan, reading all the rows in the table, to find rows for a query. On the surface, reading the entire table to find the desired rows seems like an inefficient approach. Many people avoid it, thinking that an indexed retrieval is better. But this isn't necessarily the case. If you have a reasonably large table and are searching for one or two rows, then a full table scan is an inefficient approach. However, if you are retrieving or summarizing a large percentage of the rows in the table, then a full table scan will likely outperform an indexed retrieval. The problem is that somewhere between these two extremes lies a large gray area. That's where you have to do some benchmarking and use some judgment based on your expectations of what the query will be asked to do when it is in production.

Here are some questions to ask yourself as you look at an execution plan:

• Is a table scan being used to retrieve only a small percentage of rows from a large table? If so, you may want to create an index.
• Is an index scan being used when you are retrieving, or summarizing, a large percentage of a table's rows? If so, you may be better off forcing a full table scan.
• Is Oracle using the most selective index? An index on a YES/NO field would typically be much less selective than an index on last name , for example.
• Is Oracle joining the largest table last, and the most selective table first? It's generally better to eliminate as many rows as possible prior to any joins.

Determining an execution plan to use for a statement that isn't performing well is perhaps the most crucial step in any tuning exercise. The only author I've ever seen address this problem by providing any kind of repeatable and deterministic methodology is Dan Tow in his book SQL Tuning (O'Reilly). If you are tuning SQL statements and require a reliable method for determining the optimal execution plan for a statement, I recommend that you read Dan's book.

12.4.2 Creating Indexes

Creating indexes is an easy way to affect a query. If, for example, you have a large employee table (much larger than the one used in this book), keyed on employee_id , and your query is searching for employees by name, then Oracle will do a full table scan for each name lookup. The response time will be poor, and your users will be unhappy . Creating an index on the employee_name column would improve your results a great deal.

Don't overlook the possibility of creating a multicolumn index even if you don't use all the index columns in your query's WHERE clause. Suppose that you frequently execute the following query, which searches for an employee by name and displays that employee's current billing rate:

SELECT employee_name, employee_billing_rate FROM employee WHERE employee_name = :emp_name;

If you index the employee table by name, Oracle will look up the name in the index, get the ROWID, read the correct employee row, and return the billing rate. However, it takes an extra read to fetch the employee record. You can eliminate that extra read by creating an index such as this one:

CREATE INDEX employee_by_name ON employee (employee_name, employee_billing_rate);

Because the index contains the employee_billing_rate column, Oracle doesn't need to read the actual employee record to retrieve it. Oracle recognizes that all the columns needed to satisfy the query are in the index, and it will take advantage of that fact.

12.4.3 Rewriting the Query

Sometimes you can restate a query, get the results that you want, and have it run much more efficiently . Example 12-8 shows a query listing all employees who have ever charged time to project 1001. To generate that listing, the query joins employee and project_hours .

Example 12-8. The join approach to listing employees who have charged time to a project

SET AUTOTRACE ON SELECT DISTINCT employee.employee_id, employee.employee_name FROM employee, project_hours WHERE employee.employee_id = project_hours.employee_id AND project_hours.project_id = 1001; EMPLOYEE_ID EMPLOYEE_NAME ----------- ---------------------------------------- 101 Marusia Churai 102 Mykhailo Hrushevsky 104 Pavlo Virsky 105 Mykola Leontovych 107 Lesia Ukrainka 108 Pavlo Chubynsky 110 Ivan Mazepa 111 Taras Shevchenko 112 Igor Sikorsky 113 Mykhailo Verbytsky 114 Marusia Bohuslavka 116 Roxolana Lisovsky 12 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=93 Bytes=23 25) 1 0 SORT (UNIQUE) (Cost=5 Card=93 Bytes=2325) 2 1 MERGE JOIN (Cost=4 Card=93 Bytes=2325) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (C ost=2 Card=12 Bytes=228) 4 3 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=12) 5 2 SORT (JOIN) (Cost=2 Card=93 Bytes=558) 6 5 INDEX (RANGE SCAN) OF 'PROJECT_HOURS_PK' (INDEX (UNI QUE)) (Cost=1 Card=93 Bytes=558) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 782 bytes sent via SQL*Net to client 511 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 12 rows processed

The query in Example 12-8 can be restated using an EXISTS predicate instead of joining the employee and project_hours tables together. Example 12-9 shows that version.

Example 12-9. The EXISTS approach to listing employees who have charged time to projects 1001 or 1002

SET AUTOTRACE ON SELECT employee_id, employee_name FROM employee WHERE EXISTS (SELECT * FROM project_hours WHERE project_hours.project_id = 1001 AND project_hours.employee_id = employee.employee_id); EMPLOYEE_ID EMPLOYEE_NAME ----------- ---------------------------------------- 101 Marusia Churai 102 Mykhailo Hrushevsky 104 Pavlo Virsky 105 Mykola Leontovych 107 Lesia Ukrainka 108 Pavlo Chubynsky 110 Ivan Mazepa 111 Taras Shevchenko 112 Igor Sikorsky 113 Mykhailo Verbytsky 114 Marusia Bohuslavka 116 Roxolana Lisovsky 12 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=12 Bytes=30 0) 1 0 MERGE JOIN (SEMI) (Cost=4 Card=12 Bytes=300) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cos t=2 Card=12 Bytes=228) 3 2 INDEX (FULL SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (C ost=1 Card=12) 4 1 SORT (UNIQUE) (Cost=2 Card=93 Bytes=558) 5 4 INDEX (RANGE SCAN) OF 'PROJECT_HOURS_PK' (INDEX (UNIQU E)) (Cost=1 Card=93 Bytes=558) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 782 bytes sent via SQL*Net to client 511 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 12 rows processed

The performance results from Examples Example 12-8 and Example 12-9 are a bit inconclusive. When you compare estimated costs, you'll see a cost of 5 for Example 12-8 and a cost of 4 for Example 12-9. Look further at the execution plans, and you'll see that Example 12-9 avoids the sort triggered in Example 12-8 by the use of DISTINCT. Interestingly, the number of logical reads (consistent gets) is lower in the plan with the higher estimated cost, potentially leaving you in a quandary as to which choice to make here. My own feeling is to go with Example 12-9, which avoids the sort because the number of rows to be sorted will only grow as the project_hours table grows.

12.4.4 Using Hints

Rather than allowing Oracle to have total control over how a query is executed, you can provide specific directions to the optimizer through the use of hints. A hint , in Oracle, is an optimizer directive embedded in a SQL statement in the form of a comment. Here is a query with an optimizer hint telling Oracle to do a full table scan:

SELECT /*+ FULL(employee) */ employee_id, employee_name, employee_billing_rate FROM employee WHERE employee_name = 'Igor Sikorsky';

The hint in this case is FULL(employee) , which tells Oracle to do a full table scan of the employee table. Oracle will honor this hint and perform a full table scan even if there happens to be an index on the employee_name field.

12.4.4.1 Syntax for a hint

A hint applies to a single SQL statement, and hints may be specified only for SELECT, INSERT, UPDATE, DELETE, and MERGE statements. A hint takes the form of a specially formatted comment and must appear immediately following the keyword that begins the statement:

keyword /*+ [ hint [ hint ...]] */

in which:

keyword

Is the keyword that begins the statement. This will be one of the keywords SELECT, INSERT, UPDATE, DELETE, or MERGE.

hint

Is the hint, sometimes with one or more arguments enclosed in parentheses. Tables Table 12-3 through Table 12-10 provide a list of the hints available in Oracle Database 10 g , Release 1. Hints aren't case-sensitive. A single comment may contain more than one hint, as long as the hints are separated by at least one space.

Here are some examples of how hints may be specified:

SELECT /*+ FULL(employee) */ employee_id, employee_name FROM employee WHERE employee_billing_rate > 100; SELECT /*+ FULL(e) do a full table scan on the employee table, because most employees do have billing rates > 100. */ employee_id, employee_name FROM employee e WHERE employee_billing_rate > 100;

In the second example, the table name is employee , but an alias of e has been given. The hint for the table uses the same alias and is specified as FULL(e) . Whenever an alias is used, you must use the alias name in any hints for the table. Be careful about this because hints are specially formatted comments, and you won't get any error messages as the result of a malformed hint.

If you want to supply multiple hints for a statement, they must all appear in the same comment:

SELECT /*+ FULL(employee) first_rows */ employee_id, employee_name FROM employee WHERE employee_billing_rate > 100;

When subqueries are used, they are allowed to have their own hints. The hint for a subquery follows immediately after the keyword that starts the subquery:

SELECT /*+ FIRST_ROWS */ employee_id, employee_name FROM employee WHERE exists (SELECT /*+ FULL(project_hours) */* FROM project_hours WHERE project_hours.project_id = 1001 AND project_hours.employee_id = employee.employee_id);

When using hints, be very careful to get the syntax right. Because hints are embedded in statements as comments, Oracle can't do any syntax checking. Oracle treats any incorrectly specified hint as a comment. In addition, you should do an EXPLAIN PLAN after you code your hints to be sure that the optimizer is really doing what you think you told it to do.

12.4.4.2 Specifying table and index names

Many hints take one or more table names as arguments. The FULL hint, for example, takes one table name as an argument.

/*+ FULL(employee) */

Some access method hints are index- related and allow you to specify one or more indexes to be used. In many cases, as with the INDEX hint, you have the choice of specifying an index name or not. The following hint, for example, tells Oracle that you want to do an index scan on the employee table, but it's up to Oracle to pick the index:

/*+ INDEX(employee) */

This is useful if you think Oracle will make the correct choice, or if you don't want to hardcode an index name into the hint. You have the option, however, of specifying the exact index to use. Here's an example:

/*+ INDEX(employee employee_by_name) */

You may specify a list of indexes, and Oracle will choose from the indexes in that list. If, for example, you had seven indexes on the employee table but you believed that only two would be useful for the query in question, you could specify a hint like this:

/*+ INDEX(employee employee_by_name, employee_by_billing_rate) */

This tells Oracle that you want to use an index scan to access the employee table, and that you want to use either the name index or the billing rate index.

12.4.4.3 Hint conflicts and applicability

Whenever two hints are in conflict, Oracle will ignore at least one of them. Oracle ignores hints that can't be followed. For example, take a look at the following query:

SELECT /*+ USE_CONCAT */ employee_id, employee_name FROM employee WHERE employee_name = 'Jeff Gennick';

The USE_CONCAT hint makes no sense here because the query doesn't contain an OR condition. You can't break this into two queries and then UNION the results together, so Oracle will ignore the hint.

A bad hint will be honored, however, whenever it is implementable. The following query contains a hint to do an index scan on the primary key index for the employee table:

SELECT /*+ INDEX(employee employee_pk) */ employee_name FROM employee WHERE employee_name = 'Jeff Gennick';

The primary key for employee is the employee_id field. An index on employee_name does exist. The query seeks one record based on the employee name. Even though it makes perfect sense to look up the name in the name index, Oracle will honor the request to use the primary key index. Here is the execution plan for this statement:

0 SELECT STATEMENT Cost = 2 1 TABLE ACCESS BY INDEX ROWID EMPLOYEE 2 INDEX FULL SCAN EMPLOYEE_PK

Oracle is going to read every entry in the primary key index, retrieve the associated row from the employee table, and check the name to see if it has a match. This is worse than a full table scan. Oracle does this because the hint requested it and because it physically can be done; so be careful what you ask for, and check the results.

12.4.4.4 Hint query blocks

Oracle Database 10 g introduces a new, query block syntax to hints. When you have a statement, such as a SELECT, that consists of a main statement and one or more subqueries, each statement and subquery is a query block. The new query block syntax lets you refer to those query blocks by name from a hint.

You can name the query blocks in a statement using the QB_NAME hint. The statement in Example 12-10 contains two query blocks that are given the names main and sub respectively. The two FULL hints use query block names to fully qualify their respective table references, with the end result that both instances of employee are accessed via a full table scan.

Example 12-10. A statement with named query blocks

SELECT /*+ QB_NAME(main) FULL(@main e1) FULL(@sub e2) */ e1.employee_name, employee_hire_Date FROM employee e1 WHERE EXISTS (SELECT /*+ QB_NAME(sub) */ e2.employee_id FROM employee e2 WHERE employee_hire_date >= TO_DATE('1-Jan-2000','dd-mon-yyyy') AND e1.employee_id = e2.employee_id);

If you don't name the query blocks in a statement, Oracle will generate query block names for you. To see those names automatically, run an EXPLAIN PLAN on your statement followed by a query using the ALL option of DBMS_XPLAN.DISPLAY:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

12.4.5 Oracle's Hint Syntax

The following subsections categorize the various hints available in Oracle Database 10 g and provide a quick summary of the hints.

12.4.5.1 Optimizer goal hints

Optimization goal hints allow you to influence the optimizer's overall goal when formulating an execution plan. You may, for example, specify that you want the plan optimized to return the first record as quickly as possible. Table 12-3 gives a list of these hints.

Table 12-3. Optimizer goal hints

You should avoid the now obsolete RULE hint if at all possible. That hint causes the rule-based optimizer to be used. The rule-based optimizer uses a fixed set of rules when determining the execution plan for a statement and doesn't attempt to factor in the ultimate cost of executing that plan. The cost-based optimizer, on the other hand, bases its decision on the estimated I/O and CPU overhead required by various alternative plans. Although Oracle still supports the rule-based optimizer, it hasn't been enhanced in years , won't be enhanced in the future, and will be de-supported at some point. Oracle is putting its development effort into the cost-based optimizer.

12.4.5.2 Access method hints

Access method hints allow you to control the way data are accessed. For example, you can tell Oracle to do a full table scan or to use an index when accessing a table. You can name the specific index to be used. Table 12-4 provides a list of these hints.

Table 12-4. Access method hints

12.4.5.3 Query transformation hints

Sometimes Oracle's query optimizer will rewrite a query in a different form, from which it is possible to generate a more efficient (you hope) execution plan. The query transformation hints in Table 12-5 provide some control over whether and how your queries get rewritten.

Table 12-5. Query transformation hints

12.4.5.4 Join order hints

Join order hints allow you to exercise some control over the order in which Oracle joins tables. Only three of them exist, and they are listed in Table 12-6.

Table 12-6. Join order hints

12.4.5.5 Join operation hints

Join operation hints allow you to control the manner in which two tables are joined. Oracle uses three basic methods whenever two tables are joined: the merge join, the nested loops join, and the hash join.

Merge join

This type of join is done by sorting the rows from each table by the join columns. Once the two rowsets have been sorted, Oracle reads through both and joins any matching rows together. A merge may use fewer resources than the other options, but you have to wait for all the records to be sorted before you get the first one back. You have to have enough memory and temporary disk space to handle the sort.

Nested loops join

The method used for a nested loops join corresponds to the mental image most people have in mind when they think of joining tables. Oracle picks one table as the driving table and reads through that table row by row. For each row read from the driving table, Oracle looks up the corresponding rows in the secondary table and joins them together. Because no sort is involved, a nested loops join will get you the first record back more quickly than a merge join. For the same reason, a nested loops join doesn't require large amounts of disk space and memory. However, a nested loops join may result in a considerably greater number of disk reads than a merge join.

Hash join

A hash join is similar to a merge join, but a sort is not required. A hash table is built in memory to allow quick access to the rows from one of the tables to be joined. Then rows are read from the other table. As each row is read from the second table, the hash function is applied to the join columns, and the result is used to find the corresponding rows from the first table.

Table 12-7 lists the join operation hints.

Table 12-7. Join operation hints

12.4.5.6 Parallel execution hints

The hints shown in Table 12-8 allow you to influence the way Oracle executes a query in a parallel processing environment. In an environment with a single CPU, parallel processing is not possible, and these hints are ignored.

Table 12-8. Parallel execution hints

Table 12-9. Outer and inner distribution values for PQ hints

12.4.5.7 Other hints

A few hints don't fit neatly into one of the other categories. These are listed in Table 12-10.

Table 12-10. Other hints