dbms_xplan

In version 9, Oracle finally provides a utility that formats the contents of the plan table. The plan table is one that is used to hold the results of an “Explain Plan” for a particular SQL statement. Explain Plan is used to generate and show the optimizer execution plan for a particular SQL statement.

The output from the explain plan shows the anticipated optimizer execution path, along with the estimated cost of the statement without actually executing the statement against the database.

The DBA or developer first needs to create the plan table. The DDL for this table is in the $ORACLE_HOME/rdbms/admin/utllxplan.sql file. The create table statement in the file is:

create table PLAN_TABLE (

statement_id varchar2(30),

timestamp date,

remarks varchar2(80),

operation varchar2(30),

options varchar2(255),

object_node varchar2(128),

object_owner varchar2(30),

object_name varchar2(30),

object_instance numeric,

object_type varchar2(30),

optimizer varchar2(255),

search_columns number,

id numeric,

parent_id numeric,

position numeric,

cost numeric,

cardinality numeric,

bytes numeric,

other_tag varchar2(255),

partition_start varchar2(255),

partition_stop varchar2(255),

partition_id numeric,

other long,

distribution varchar2(30),

cpu_cost numeric,

io_cost numeric,

temp_space numeric,

access_predicates varchar2(4000),

filter_predicates varchar2(4000));

and this DDL can be submitted in any users schema. The next step in using dbms_xplan is running Explain Plan for a statement.

explain plan for

select * from employee where emp_id = 64523;

The command above will populate the plan table with the data returned from the optimizer. Next, the dbms_xplan utility can be used to view the output.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 24 | 2 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 24 | 2 |

|* 2 | INDEX RANGE SCAN | EMP_IDX1 | 1 | | 1 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------

2 - access("EMPLOYEE"."EMP_ID"=64523)

Note: cpu costing is off

15 rows selected.

The output above shows the query execution plan complete with formatting. This is the starting point for all expert SQL tuners.

dbms_xplan Options

dbms_xplan has only one procedure – display.

dbms_xplan.display (

table_name in varchar2 default ‘PLAN_TABLE’,

statement_id in varchar2 default null,

format in varchar2 default ‘TYPICAL’);

· table_name – The name of the plan table. If the plan table was named something other than the default (plan_table), then it needs to be specified here.

· statement_id – Displays only the plan table data for a particular statement id. When executing an Explain Plan, the user has the option of setting a statement_id. Use this option when the data for many statements is stored in the plan table.

· format – Four options apply to the format parameter.

§ BASIC – Minimum information from the execution plan is displayed – object name and operation.

§ TYPICAL – The most useful information from the plan table is displayed – ID, operation, object name, rows, bytes, and cost.

§ ALL – Maximum level of output. Includes everything in the TYPICAL plus SQL statements generated for parallel execution (if applicable).

§ SERIAL – Like TYPICAL but without parallel information regardless of whether or not the plan executes in parallel.

For more information on interpreting execution plan output see "Oracle High-Performance SQL Tuning" by Oracle Press.

dbms_xplan provides a useful feature to DBAs and developers. Although most DBAs and developers have explain plan scripts that they’ve used in prior versions of Oracle, the ease of this package makes it much more efficient. In addition, the formatting of execution plans for partitions and parallel queries is a valuable feature. DBAs should grant public access (execute) to this package and encourage developers to use it. By putting more tools into the hands of those who write the SQL, the better the database will perform.