SQL*Plus can be employed as a tool to help tune SQL statements. You can use SQL’s EXPLAIN PLAN facility to get the execution plan for a statement into a table. Then you can query that table by using SQL*Plus to display that plan. If you don’t like the plan that Oracle is using, you can add optimizer hints to your SQL statement that specify how you want the statement to be executed.
Before you can use the EXPLAIN PLAN statement, you need to create a plan table to hold the results. Oracle provides a script named utlxplan.sql to create the plan table, and you’ll find it in the $ORACLE_HOME/rdbms/admin directory. Execute it as follows:
SQL> @e:oracleora81 dbmsadminutlxplan Table created.
The resulting table, PLAN_TABLE, looks like this:
Name Null? Type ------------------------ -------- ---- STATEMENT_ID VARCHAR2(30) TIMESTAMP DATE REMARKS VARCHAR2(80) OPERATION VARCHAR2(30) OPTIONS VARCHAR2(30) OBJECT_NODE VARCHAR2(128) OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_INSTANCE NUMBER(38) OBJECT_TYPE VARCHAR2(30) OPTIMIZER VARCHAR2(255) SEARCH_COLUMNS NUMBER ID NUMBER(38) PARENT_ID NUMBER(38) POSITION NUMBER(38) COST NUMBER(38) CARDINALITY NUMBER(38) BYTES NUMBER(38) OTHER_TAG VARCHAR2(255) PARTITION_START VARCHAR2(255) PARTITION_STOP VARCHAR2(255) PARTITION_ID NUMBER(38) OTHER LONG DISTRIBUTION VARCHAR2(30)
The columns in the plan table often vary from one release of Oracle to the next. This version of the plan table is from Oracle8i release 8.1.5.
Use the EXPLAIN PLAN statement to get the execution plan for a SQL statement. Oracle will place the execution plan into the plan table that you’ve created.
The syntax for EXPLAIN PLAN looks like this:
EXPLAIN PLAN [SET STATEMENT_ID = 'statement_id'] [INTO table_name] FOR statement;
The parameters follow:
Identifies the query you are explaining and is stored in the STATEMENT_ID field of the plan table records. This defaults to null.
Is the name of the plan table and defaults to PLAN_TABLE.
Is the SELECT, INSERT, UPDATE, or DELETE statement to be explained.
First, delete any existing plan table records with the statement ID you are about to use. For example:
DELETE FROM plan_table WHERE statement_id = 'HOURS_BY_PROJECT';
Insert the EXPLAIN PLAN statement onto the front of the SQL statement that you are interested in explaining, then execute the resulting, longer statement. For example:
EXPLAIN PLAN SET STATEMENT_ID = 'HOURS_BY_PROJECT' FOR SELECT employee_name, project_name, SUM(hours_logged) FROM employee, project, project_hours WHERE employee.employee_id = project_hours.employee_id AND project.project_id = project_hours.project_id GROUP BY employee_name, project_name;
Your next step is to query the plan table for the results.
The typical way to look at an execution plan is to display it using a hierarchical query. Oracle breaks query execution down into a series of nested steps, each of which feeds data up to a parent step. The ultimate parent is the query itself; the output of which is returned to the application. The typical query used to display plan output looks like this:
SELECT LPAD(' ', 2*(level-1)) || operation || ' ' || options || ' ' || object_name || ' ' || DECODE(id, 0, 'Cost = ' || position) "Query Plan" FROM plan_table START WITH id = 0 AND statement_id = 'HOURS_BY_PROJECT' CONNECT BY prior id = parent_id AND statement_id = 'HOURS_BY_PROJECT';
The result of this query will be a report showing the steps in the execution plan, with each child step indented underneath its parent, as shown in this example:
Query Plan ---------------------------------------- SELECT STATEMENT Cost = 7 SORT GROUP BY HASH JOIN HASH JOIN TABLE ACCESS FULL EMPLOYEE TABLE ACCESS FULL PROJECT_HOURS TABLE ACCESS FULL PROJECT
The cost for one execution plan can only be interpreted relative to another. A statement with a cost of 14 would require twice the I/O and CPU resources of a query with a cost of 7.
Statistics are required to compute a cost. Out-of-date statistics will result in an inaccurate cost.
Table 1.3 describes the various plan operations and their options.
Table 1-3. EXPLAIN PLAN Operations
Operation and Options |
Description |
---|---|
AND-EQUAL |
Has two or more child steps, each of which returns a set of ROWIDs. The AND-EQUAL operation selects only those ROWIDs that are returned by all the child operations. |
BITMAP CONVERSION TO ROWIDS |
Converts a bitmap from a bitmap index to a set of ROWIDs that can be used to retrieve the actual data. |
BITMAP CONVERSION FROM ROWIDS |
Converts a set of ROWIDs into a bitmapped representation. |
BITMAP CONVERSION COUNT |
Counts the number of rows represented by a bitmap. |
BITMAP INDEX SINGLE VALUE |
Retrieves the bitmap for a single key value. |
BITMAP INDEX RANGE SCAN |
Returns the bitmaps for a range of key values. |
BITMAP INDEX FULL |
Scans an entire bitmapped index. |
BITMAP MERGE |
Merges two bitmaps together, using an OR operation, and returns one bitmap as the result. |
BITMAP MINUS |
Subtracts one bitmap index from another. Rows represented by the second bitmap are excluded from the result. |
BITMAP OR |
Merges two bitmaps together using an OR operation. |
CONNECT BY |
Retrieves rows hierarchically and is the result of a query written with a CONNECT BY clause. |
CONCATENATION |
Combines multiple sets of rows into one set; essentially a UNION ALL. |
COUNT |
Counts the number of rows that have been selected from a table. |
COUNT STOPKEY |
Counts the number of rows up to the number specified for ROWNUM in the query’s WHERE clause. |
FILTER |
Filters a set of rows based on a condition from a query’s WHERE clause. |
FIRST ROW |
Retrieves only the first row of a query’s result set. |
FOR UPDATE |
Locks rows that are retrieved. This would be the result of specifying FOR UPDATE in the original query. |
HASH JOIN |
Joins two tables using a hash join method. |
INDEX UNIQUE |
Looks up a value using a unique index. |
INDEX RANGE SCAN |
Scans an index for a range of values. |
INDEX RANGE SCAN DESCENDING |
Performs the same function as INDEX RANGE SCAN, but the index is scanned in descending order. |
INLIST ITERATOR |
Performs one or more operations once for each value in an IN predicate. |
INTERSECTION |
Takes two rowsets as input and returns only rows that appear in both. |
MERGE JOIN |
Joins two rowsets based on some common value. Both rowsets will first have been sorted by this value. This is an inner join. |
MERGE JOIN OUTER |
Performs a function similar to a MERGE JOIN, but an outer join is performed. |
MERGE JOIN ANTI |
Indicates that an anti-join is being performed. |
MERGE JOIN SEMI |
Indicates that a semi-join is being performed. |
MINUS |
Takes two rowsets as inputs and returns rows from the first set that do not appear in the second. |
NESTED LOOPS |
Uses a nested loop to perform an operation (usually a join) for all combinations of rows from two rowsets. |
NESTED LOOPS OUTER |
Performs the same function as NESTED LOOPS but indicates an outer join. |
PARTITION |
Executes an operation for one or more partitions. The PARTITION_START and PARTITION_STOP columns give the range of partitions over which the operation is performed. |
PARTITION SINGLE |
Executes an operation on a single partition. |
PARTITION ITERATOR |
Executes an operation on several partitions. |
PARTITION ALL |
Executes an operation on all partitions. |
PARTITION INLIST |
Performs the same function as PARTITION ITERATOR, but the list of partitions is being driven by an IN predicate. |
PROJECTION |
Takes multiple queries as input and returns a single set of records. This is used with INTERSECTION, MINUS, and UNION operations. |
REMOTE |
Indicates that a rowset is being returned from a remote database. |
SEQUENCE |
Indicates that an Oracle sequence is being accessed. |
SORT AGGREGATE |
Applies a group function, such as COUNT, to a rowset and returns only one row as the result. |
SORT UNIQUE |
Sorts a rowset and eliminates duplicates. |
SORT GROUP BY |
Sorts a rowset into groups. This is the result of a GROUP BY clause. |
SORT JOIN |
Sorts a rowset in preparation for a join. See MERGE JOIN. |
SORT ORDER BY |
Sorts a rowset in accordance with the ORDER BY clause specified in the query. |
TABLE ACCESS FULL |
Reads all rows in the specified table. |
TABLE ACCESS CLUSTER |
Reads all rows in a table that match a specified index cluster key. |
TABLE ACCESS HASH |
Reads all rows in a table that match a specified hash cluster key. |
TABLE ACCESS BY ROWID |
Retrieves a row from a table based on its ROWID. |
UNION |
Takes two rowsets, eliminates duplicates, and returns the result as one set. |
VIEW |
Executes the query behind a view and returns the resulting rowset. |
Rather than allow 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 that is embedded in a SQL statement in the form of a comment. For example, 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 = 'Jenny Gennick';
The hint in this case is FULL(employee) telling 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.
Hints must be in a comment of the form /*+...*/
,
and that comment must follow the SQL keyword that begins the
statement.
Optimizer hints can be divided into the following five categories: optimizer goal hints, access method hints, join order hints, join operation hints, and parallel execution hints.
Optimizer goal hints allow you to influence the optimizer’s overall goal when formulating an execution plan:
Produce an execution plan that minimizes resource consumption.
Produce an execution plan that gets to the first row as fast as possible.
Use the cost-based optimizer if statistics are present for at least one of the tables referenced in a query.
Use the rules-based optimizer.
Access method hints allow you to control the manner in which Oracle accesses data:
Do a full table scan of the specified table.
Scan the specified table based on ROWIDs.
Do a cluster scan of the specified table.
Do a hash scan of the specified table.
Do a hash anti-join of the specified table.
Access the specified table via an index scan. Optionally, you may specify a list of indexes from which to choose.
Perform the same function as INDEX, but scan in ascending order.
Use some combination of two indexes. You may optionally specify a list of indexes to use.
Perform the same function as INDEX, but scan in descending order.
Do a fast full index scan.
Turn a NOT IN subquery into a merge anti-join.
Scan two or more indexes and merge the results. You must specify at least two index names.
Turn a query with OR conditions into two or more queries unioned together with a UNION ALL.
Join order hints allow you to exercise some control over the order in which tables are joined:
Join tables left to right in the same order in which they are listed in the FROM clause.
Use a star query execution plan, if at all possible. This can work only if there are at least three tables being joined and if the largest table has a concatenated index on columns that reference the two smaller tables. The two smaller tables are joined first, and then a nested-loop join is used to retrieve the required rows from the largest table.
Transform the query into a star query, if possible, and then use the best plan for that query.
Join operation hints allow you to control the manner in which two tables are joined:
Use a nested loop when joining this table. The table specified by this hint will be the one accessed by the innermost loop. The other table will be the driving table.
Use the sort merge method when joining this table.
Use a hash join for the specified table.
Prevent Oracle from merging the query from a view into the main query.
Drive a distributed join from the site where the named table resides.
Parallel execution hints influence the way in which Oracle uses the resources of a parallel processing environment to execute a query:
Access data from the indicated table in a parallel processing mode. You can optionally specify both the degree of parallelism to use and the number of instances that will be involved. The DEFAULT keyword may be used for both arguments, in which case Oracle decides the values based on parameters in the INIT.ORA file.
Do not access the specified table in parallel.
Do not attempt to reuse any free space that may be available in any extents currently allocated to the table. Applies only to INSERT statements.
Use any free space in extents currently allocated to the table. Applies only to INSERT statements.
Access data from the indicated table by scanning the specified index in a parallel processing mode. The index must be a partitioned index. You can optionally specify both the degree of parallelism to use and the number of instances that will be involved. The DEFAULT keyword may be used for both arguments, in which case Oracle decides the values based on parameters in the INIT.ORA file.
Place blocks for the table at the most recently used end of the buffer cache, so that they will remain in memory as long as possible.
Place blocks at the least recently used end of the buffer cache, where they will be cleared out as soon as possible.
Evaluate nonmerged subqueries as soon as possible during query execution. If you expect the subquery to eliminate a large number of rows, this can result in a performance improvement.