How to print explain plan

DELETE PLAN_TABLE;
EXPLAIN PLAN FOR
  SELECT * FROM t WHERE group2=1;
SELECT * FROM TABLE(dbms_xplan.display());

Oracle utilities

The Oracle utility creating PLAN_TABLE:

D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplan.sql

The Oracle utility displays the last explain plan. It displays also parallel query information if the plan happens to run parallel:

D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxplp.sql

The utility that does not displays parallel query information (sequential).

D:\Oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlxpls.sql

Hierarchical query

An example of use of hierarchical queries to print the last explain plan:

SELECT
  LPad(' ', level-1) || operation || ' (' || options || ')' AS operation,
  coalesce(object_name, ' ') object,
  cost,
  cardinality,
  bytes
FROM
  plan_table
START WITH
  id = 0
CONNECT BY PRIOR
  id=parent_id;

Example of the result:

OPERATION OBJECT COST CARDINALITY BYTES
SELECT STATEMENT () 873 1000 20000
  TABLE ACCESS (BY INDEX ROWID) T 873 1000 20000
    INDEX (RANGE SCAN) T_GROUP2 5 1000

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s