Display execution plan in SQLPLUS – plan_table script

Posted By Sagar Patil

I was trying to execute following SQL through client side tools and found network delays were contributing to the SQl execution times. This SQL normally returns in less than 50 millisec and now taking more than 700 Msec. I am addicted to TOAD -TORA GUI created Execution plans and was looking for a an easy way to get execution times as well as plans on sqlplus. Here is how you do it .

SQLPLUS> set autotrace on; – Will display formatted explain plan

SQLPLUS> set timing on;
- Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently.
- The format is HH:MM:SS.hundredths of a second for example 00:00:00.81 is .81 seconds

If you get error like “PLAN_TABLE not found” use $ORACLE_HOME/utlxplan to create PLAN_TABLE and pass on access to relevant user.

SQLPLUS> set autotrace on;
SQLPLUS> set timing on;
SQL> select count(*) from voucher;


Elapsed: 00:01:55.05 format is HH:MM:SS.hundredths of a second

Execution Plan
0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1)
2    1     INDEX (FAST FULL SCAN) OF ‘VOUCHER_P0001001′ (UNIQUE) (Cost=3 Card=126665996)

0  recursive calls
0  db block gets
622158  consistent gets
621830  physical reads
0  redo size
522  bytes sent via SQL*Net to client
651  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

PLAN_TABLE is the default table for results of the EXPLAIN PLAN statement. It is created by $ORACLE_HOME/rdbms/admin/utlxplan.sql, and it contains one row for each step in the execution plan.

For 10G:

create table PLAN_TABLE (
statement_id varchar2(30),
plan_id number,
timestamp date,
remarks varchar2(4000),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_alias varchar2(65),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
depth 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),
projection varchar2(4000),
time numeric,
qblock_name varchar2(30)

Column Datatype Description
STATEMENT_ID VARCHAR2(30) Optional statement identifier specified in the EXPLAIN PLAN statement
TIMESTAMP DATE Date and time when the EXPLAIN PLAN statement was issued
REMARKS VARCHAR2(80) Place for comments that can be added to the steps of the execution plan
OPERATION VARCHAR2(30) Name of the operation performed at this step
OPTIONS VARCHAR2(255) Options used for the operation performed at this step; the string returned by the index cost function
OBJECT_NODE VARCHAR2(128) Name of the database link used to reference the object
OBJECT_OWNER VARCHAR2(30) Owner of the object
OBJECT_NAME VARCHAR2(30) Name of the object
OBJECT_INSTANCE NUMBER(38) Numbered position of the object name in the original SQL statement
OBJECT_TYPE VARCHAR2(30) Descriptive modifier that further describes the type of object
OPTIMIZER VARCHAR2(255) Current mode of the optimizer
SEARCH_COLUMNS NUMBER Number of index columns with start and stop keys (that is, the number of columns with matching predicates)
ID NUMBER(38) Identification number for this step in the execution plan
PARENT_ID NUMBER(38) ID of the next step that operates on the results of this step
POSITION NUMBER(38) Order of processing for steps with the same parent ID. For cost-based optimization, the value in the first row of the plan is the statement’s execution cost. For rule-based optimization, the value is null in the first row.
COST NUMBER(38) Cost of the current operation estimated by the cost-based optimizer (CBO)
CARDINALITY NUMBER(38) Number of rows returned by the current operation (estimated by the CBO)
BYTES NUMBER(38) Number of bytes returned by the current operation
OTHER_TAG VARCHAR2(255) Describes the function of the SQL text in the OTHER column. Values for OTHER_TAG are: 

· SERIAL – SQL is the text of a locally-executed, serial query plan. Currently, SQL is not loaded in OTHER for this case.

· SERIAL_FROM_REMOTE – SQL text shown in the OTHER column will be executed at a remote site

· PARALLEL_COMBINED_WITH_PARENT – Parent of this operation is a DFO that performs both operations in the parallel execution plan

· PARALLEL_COMBINED_WITH_CHILD – Child of this operation is a DFO that performs both operations in the parallel execution plan.

· PARALLEL_TO_SERIAL – SQL text shown in the OTHER column is the top-level of the parallel plan.

· PARALLEL_TO_PARALLEL – SQL text shown in the OTHER column is executed and output in parallel

· PARALLEL_FROM_SERIAL – Operation consumes data from a serial operation and outputs it in parallel

PARTITION_START VARCHAR2(255) Start partition of a range of accessed partitions
PARTITION_STOP VARCHAR2(255) Stop partition of a range of accessed partitions
PARTITION_ID NUMBER(38) Step that has computed the pair of values of the PARTITION_START and PARTITION_STOP columns
OTHER LONG Information about parallel execution slaves and parallel queries
DISTRIBUTION VARCHAR2(30) Distribution method
CPU_COST NUMBER(38) User-defined CPU cost
IO_COST NUMBER(38) User-defined I/O cost
TEMP_SPACE NUMBER(38) Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null.
ACCESS_PREDICATES VARCHAR2(4000) Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.
FILTER_PREDICATES VARCHAR2(4000) Predicates used to filter rows before producing them
PROJECTION VARCHAR2(4000) Expressions produced by the operation
TIME NUMBER(38) Elapsed time (in seconds) of the operation as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null.

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu