The database often throws away rows in the following situations:

• 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 76,563 times, accesses 11,432,983 rows, throws away 99% of them, and retains 76,563 rows. Why access 11,432,983 rows to realize that only 76,563 rows are needed?

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.

This section contains the following topics:

V$SQL_PLAN contains the execution plan for every statement stored in the shared SQL area. Its definition is similar to PLAN_TABLE.

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.

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 VARCHAR, and gives an error message otherwise. You can avoid this limitation by putting appropriate type conversions in the SQL statement.

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.

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.

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

• 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 database. 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;

The preceding plan explains the plan and stores the output in the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE.

Before using SET STATEMENT ID, remove any existing rows for that statement ID. In the following example, st1 is specified as the statement identifier.

The following statement directs output to my_plan_table:

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;

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 STATMENT_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.