Skip Headers
Oracle® Database SQL Tuning Guide
12c Release 1 (12.1)

E49106-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Generating and Displaying Execution Plans

This chapter contains the following topics:

6.1 Introduction to Execution Plans

The combination of the steps that Oracle Database uses to execute a statement is an execution plan. Each step either retrieves rows of data physically from the database or prepares them for the user issuing the statement. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.

6.2 About Plan Generation and Display

The EXPLAIN PLAN statement displays execution plans that the optimizer chooses for SELECT, UPDATE, INSERT, and DELETE statements. This section contains the following topics:

6.2.1 About the Plan Explanation

A statement execution plan is the sequence of operations that the database performs to run the statement. The row source tree is the core of the execution plan (see "SQL Row Source Generation"). The tree shows the following information:

  • An ordering of the tables referenced by the statement

  • An access method for each table mentioned in the statement

  • A join method for tables affected by join operations in the statement

  • Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

  • Optimization, such as the cost and cardinality of each operation

  • Partitioning, such as the set of accessed partitions

  • Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results enables you to determine whether the optimizer selects a particular execution plan, such as a nested loops join. The results also help you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and enables you to understand the performance of a query.

See Also:

Oracle Database SQL Language Reference to learn about the EXPLAIN PLAN statement

6.2.2 Why Execution Plans Change

Execution plans can and do change as the underlying optimizer inputs change. EXPLAIN PLAN output shows how the database would run the SQL statement when the statement was explained. This plan can differ from the actual execution plan a SQL statement uses because of differences in the execution environment and explain plan environment.

Note:

To avoid possible SQL performance regression that may result from execution plan changes, consider using SQL plan management.

Execution plans can differ because of the following:

6.2.2.1 Different Schemas

Schemas can differ for the following reasons:

  • The execution and explain plan occur on different databases.

  • The user explaining the statement is different from the user running the statement. Two users might be pointing to different objects in the same database, resulting in different execution plans.

  • Schema changes (usually changes in indexes) between the two operations.

6.2.2.2 Different Costs

Even if the schemas are the same, the optimizer can choose different execution plans when the costs are different. Some factors that affect the costs include the following:

  • Data volume and statistics

  • Bind variable types and values

  • Initialization parameters set globally or at session level

6.2.3 Minimizing Throw-Away

Examining an explain plan enables you to look for throw-away in cases such as the following:

  • Full scans

  • Unselective range scans

  • Late predicate filters

  • Wrong join order

  • Late filter operations

In the plan shown in Example 6-1, the last step is a very unselective range scan that is executed 76563 times, accesses 11432983 rows, throws away 99% of them, and retains 76563 rows. Why access 11432983 rows to realize that only 76563 rows are needed?

Example 6-1 Looking for Throw-Away in an Explain Plan

Rows      Execution Plan
--------  ----------------------------------------------------
      12  SORT AGGREGATE
       2   SORT GROUP BY
   76563    NESTED LOOPS
   76575     NESTED LOOPS
      19      TABLE ACCESS FULL CN_PAYRUNS_ALL
   76570      TABLE ACCESS BY INDEX ROWID CN_POSTING_DETAILS_ALL
   76570       INDEX RANGE SCAN (object id 178321)
   76563     TABLE ACCESS BY INDEX ROWID CN_PAYMENT_WORKSHEETS_ALL
11432983      INDEX RANGE SCAN (object id 186024)

6.2.4 Looking Beyond Execution Plans

The execution plan operation alone cannot differentiate between well-tuned statements and those that perform poorly. For example, an EXPLAIN PLAN output that shows that a statement uses an index does not necessarily mean that the statement runs efficiently. Sometimes indexes are extremely inefficient. In this case, you should examine the following:

  • The columns of the index being used

  • Their selectivity (fraction of table being accessed)

It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption.

6.2.4.1 Using V$SQL_PLAN Views

In addition to running the EXPLAIN PLAN command and displaying the plan, you can use the V$SQL_PLAN views to display the execution plan of a SQL statement:

After the statement has executed, you can display the plan by querying the V$SQL_PLAN view. V$SQL_PLAN contains the execution plan for every statement stored in the shared SQL area. Its definition is similar to the PLAN_TABLE. See "PLAN_TABLE Columns".

The advantage of V$SQL_PLAN over EXPLAIN PLAN is that you do not need to know the compilation environment that was used to execute a particular statement. For EXPLAIN PLAN, you would need to set up an identical environment to get the same plan when executing the statement.

The V$SQL_PLAN_STATISTICS view provides the actual execution statistics for every operation in the plan, such as the number of output rows and elapsed time. All statistics, except the number of output rows, are cumulative. For example, the statistics for a join operation also includes the statistics for its two inputs. The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL.

The V$SQL_PLAN_STATISTICS_ALL view enables side by side comparisons of the estimates that the optimizer provides for the number of rows and elapsed time. This view combines both V$SQL_PLAN and V$SQL_PLAN_STATISTICS information for every cursor.

See Also:

6.2.5 EXPLAIN PLAN Restrictions

Oracle Database does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan.

From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message otherwise. You can avoid this limitation by putting appropriate type conversions in the SQL statement.

6.2.6 The PLAN_TABLE Output Table

The PLAN_TABLE is automatically created as a public synonym to a global temporary table. This temporary table holds the output of EXPLAIN PLAN statements for all users. PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. See "PLAN_TABLE Columns" for a description of the columns in the table.

While a PLAN_TABLE table is automatically set up for each user, you can use the SQL script catplan.sql to manually create the global temporary table and the PLAN_TABLE synonym. The name and location of this script depends on your operating system. On UNIX and Linux, the script is located in the $ORACLE_HOME/rdbms/admin directory.

For example, start a SQL*Plus session, connect with SYSDBA privileges, and run the script as follows:

@$ORACLE_HOME/rdbms/admin/catplan.sql

Oracle recommends that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

If you do not want to use the name PLAN_TABLE, create a new synonym after running the catplan.sql script. For example:

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$

6.3 Generating Execution Plans

The EXPLAIN PLAN statement enables you to examine the execution plan that the optimizer chose for a SQL statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Issue the EXPLAIN PLAN statement and then query the output table.

The basics of using the EXPLAIN PLAN statement are as follows:

  • Use the SQL script CATPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. See "The PLAN_TABLE Output Table".

  • Include the EXPLAIN PLAN FOR clause before the SQL statement.

  • After issuing the EXPLAIN PLAN statement, use a script or package provided by Oracle Database to display the most recent plan table output. See "Displaying PLAN_TABLE Output".

  • The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first.

    Notes:

    • The EXPLAIN PLAN output tables in this chapter were displayed with the utlxpls.sql script.

    • The steps in the EXPLAIN PLAN output in this chapter may be different on your system. The optimizer may choose different execution plans, depending on database configurations.

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
  SELECT last_name FROM employees;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. See "Displaying PLAN_TABLE Output".

6.3.1 Identifying Statements for EXPLAIN PLAN

With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using SET STATEMENT ID, remove any existing rows for that statement ID.

In Example 6-2, st1 is specified as the statement identifier:

Example 6-2 Using EXPLAIN PLAN with the STATEMENT ID Clause

EXPLAIN PLAN
  SET STATEMENT_ID = 'st1' FOR
  SELECT last_name FROM employees;

6.3.2 Specifying Different Tables for EXPLAIN PLAN

You can specify the INTO clause to specify a different table.

Example 6-3 Using EXPLAIN PLAN with the INTO Clause

EXPLAIN PLAN
  INTO my_plan_table FOR
  SELECT last_name FROM employees;

You can specify a statement ID when using the INTO clause.

EXPLAIN PLAN
   SET STATEMENT_ID = 'st1'
   INTO my_plan_table FOR
   SELECT last_name FROM employees;

See Also:

Oracle Database SQL Language Reference for a complete description of EXPLAIN PLAN syntax.

6.4 Displaying PLAN_TABLE Output

After you have explained the plan, use the following SQL scripts or PL/SQL package provided by Oracle Database to display the most recent plan table output:

  • UTLXPLS.SQL

    This script displays the plan table output for serial processing. Example 6-5, "EXPLAIN PLAN Output" is an example of the plan table output when using the UTLXPLS.SQL script.

  • UTLXPLP.SQL

    This script displays the plan table output including parallel execution columns.

  • DBMS_XPLAN.DISPLAY table function

    This function accepts options for displaying the plan table output. You can specify:

    • A plan table name if you are using a table different than PLAN_TABLE

    • A statement ID if you have set a statement ID with the EXPLAIN PLAN

    • A format option that determines the level of detail: BASIC, SERIAL, TYPICAL, and ALL

    Examples of using DBMS_XPLAN to display PLAN_TABLE output are:

    SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
    
    SELECT PLAN_TABLE_OUTPUT 
      FROM TABLE(DBMS_XPLAN.DISPLAY('MY_PLAN_TABLE', 'st1','TYPICAL'));
    

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_XPLAN package

6.4.1 Displaying an Execution Plan: Example

Example 6-4 uses EXPLAIN PLAN to examine a SQL statement that selects the employee_id, job_title, salary, and department_name for the employees whose IDs are less than 103.

Example 6-4 Using EXPLAIN PLAN

EXPLAIN PLAN FOR
  SELECT e.employee_id, j.job_title, e.salary, d.department_name
  FROM   employees e, jobs j, departments d
  WHERE  e.employee_id < 103
  AND    e.job_id = j.job_id 
  AND    e.department_id = d.department_id;

The resulting output table in Example 6-5 shows the execution plan chosen by the optimizer to execute the SQL statement in the example:

Example 6-5 EXPLAIN PLAN Output

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."EMPLOYEE_ID"<103)
   5 - access("E"."JOB_ID"="J"."JOB_ID")
   7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"


-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     3 |   189 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                   |               |       |       |            |          |
|   2 |   NESTED LOOPS                  |               |     3 |   189 |     8  (13)| 00:00:01 |
|   3 |    MERGE JOIN                   |               |     3 |   141 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID | JOBS          |    19 |   513 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN            | JOB_ID_PK     |    19 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                   |               |     3 |    60 |     3  (34)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     3 |    60 |     2   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | EMP_EMP_ID_PK |     3 |       |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN            | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
   8 - access("E"."EMPLOYEE_ID"<103)
   9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

6.4.2 Customizing PLAN_TABLE Output

If you have specified a statement identifier, then you can write your own script to query the PLAN_TABLE. For example:

  • Start with ID = 0 and given STATEMENT_ID.

  • Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATEMENT_ID and PARENT_ID = PRIOR ID.

  • Use the pseudo-column LEVEL (associated with CONNECT BY) to indent the children.

    SELECT cardinality "Rows",
       lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
      FROM PLAN_TABLE
    CONNECT BY prior id = parent_id
            AND prior statement_id = statement_id
      START WITH id = 0
            AND statement_id = 'st1'
      ORDER BY id;
    
       Rows Plan
    ------- ----------------------------------------
            SELECT STATEMENT
             TABLE ACCESS FULL EMPLOYEES
    

    The NULL in the Rows column indicates that the optimizer does not have any statistics on the table. Analyzing the table shows the following:

       Rows Plan
    ------- ----------------------------------------
      16957 SELECT STATEMENT
      16957  TABLE ACCESS FULL EMPLOYEES
    

    You can also select the COST. This is useful for comparing execution plans or for understanding why the optimizer chooses one execution plan over another.

    Note:

    These simplified examples are not valid for recursive SQL.