6 Generating and Displaying Execution Plans

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?

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, a good practice is to 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.

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.

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$

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 "Guidelines for Creating PLAN_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.

  Note:

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

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:

You can specify the INTO clause to specify a different table, as in the following statement:

EXPLAIN PLAN
  INTO my_plan_table FOR
  SELECT last_name FROM employees;

You can specify a statement ID when using the INTO clause, as in the following statement:

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

Example 6-3 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.

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.