Tuning SQL

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.

Creating the Plan Table

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.

Explaining a Query

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.

EXPLAIN PLAN syntax

The syntax for EXPLAIN PLAN looks like this:

EXPLAIN PLAN
        [SET STATEMENT_ID = 'statement_id']
        [INTO table_name]
        FOR statement;

The parameters follow:

statement_id

Identifies the query you are explaining and is stored in the STATEMENT_ID field of the plan table records. This defaults to null.

table_name

Is the name of the plan table and defaults to PLAN_TABLE.

statement

Is the SELECT, INSERT, UPDATE, or DELETE statement to be explained.

EXPLAIN PLAN example

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.

Querying the Plan Table

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.

Warning

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.

Using Optimizer Hints

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

Optimizer goal hints allow you to influence the optimizer’s overall goal when formulating an execution plan:

ALL_ROWS

Produce an execution plan that minimizes resource consumption.

FIRST_ROWS

Produce an execution plan that gets to the first row as fast as possible.

CHOOSE

Use the cost-based optimizer if statistics are present for at least one of the tables referenced in a query.

RULE

Use the rules-based optimizer.

Access method hints

Access method hints allow you to control the manner in which Oracle accesses data:

FULL(table_name)

Do a full table scan of the specified table.

ROWID(table_name)

Scan the specified table based on ROWIDs.

CLUSTER(table_name)

Do a cluster scan of the specified table.

HASH(table_name)

Do a hash scan of the specified table.

HASH_AJ(table_name)

Do a hash anti-join of the specified table.

INDEX(table_name [index_name ... ])

Access the specified table via an index scan. Optionally, you may specify a list of indexes from which to choose.

INDEX_ASC(table_name [index_name ... ])

Perform the same function as INDEX, but scan in ascending order.

INDEX_COMBINE(table_name [index_name ... ])

Use some combination of two indexes. You may optionally specify a list of indexes to use.

INDEX_DESC(table_name [index_name ... ])

Perform the same function as INDEX, but scan in descending order.

INDEX_FFS(table_name [index_name ... ])

Do a fast full index scan.

MERGE_AJ(table_name)

Turn a NOT IN subquery into a merge anti-join.

AND_EQUAL(table_name index_name index_name ... )

Scan two or more indexes and merge the results. You must specify at least two index names.

USE_CONCAT

Turn a query with OR conditions into two or more queries unioned together with a UNION ALL.

Join order hints

Join order hints allow you to exercise some control over the order in which tables are joined:

ORDERED

Join tables left to right in the same order in which they are listed in the FROM clause.

STAR

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.

STAR_TRANSFORMATION

Transform the query into a star query, if possible, and then use the best plan for that query.

Join operation hints

Join operation hints allow you to control the manner in which two tables are joined:

USE_NL(table_name)

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_MERGE(table_name)

Use the sort merge method when joining this table.

USE_HASH(table_name)

Use a hash join for the specified table.

NO_MERGE

Prevent Oracle from merging the query from a view into the main query.

DRIVING_SITE(table_name)

Drive a distributed join from the site where the named table resides.

Parallel execution hints

Parallel execution hints influence the way in which Oracle uses the resources of a parallel processing environment to execute a query:

PARALLEL(table_name [, degree [, num_instances]])

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.

NO_PARALLEL(table_name)

Do not access the specified table in parallel.

APPEND

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.

NOAPPEND

Use any free space in extents currently allocated to the table. Applies only to INSERT statements.

PARALLEL_INDEX(table_name, index_name [,degree [,num_instances]])

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.

CACHE(table_name)

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.

NOCACHE(table_name)

Place blocks at the least recently used end of the buffer cache, where they will be cleared out as soon as possible.

PUSH_SUBQ

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset