DBMS_XPLAN

The DBMS_XPLAN package is used to format the output of an explain plan. It is intended as a replacement for the utlxpls.sql script.

If it is not already present create the SCOTT schema:

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlsampl.sql

Create a PLAN_TABLE if it does not already exist:

conn sys/password as sysdba
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
GRANT ALL ON sys.plan_table TO public;

Next we explain an SQL statement:

conn scott/tiger
EXPLAIN PLAN FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

Finally we use the DBMS_XPLAN.DISPLAY function to display the execution plan:

SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    57 |     3 |
|   1 |  NESTED LOOPS                |             |     1 |    57 |     3 |
|*  2 |   TABLE ACCESS FULL          | EMP         |     1 |    37 |     2 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |     1 |    20 |     1 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT     |     1 |       |       |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("E"."ENAME"='SMITH')
   4 - access("E"."DEPTNO"="D"."DEPTNO")

Note: cpu costing is off

18 rows selected.

The DBMS_XPLAN.DISPLAY function can accept 3 parameters:

table_name - Name of plan table, default value 'PLAN_TABLE'.
statement_id - Statement id of the plan to be displayed, default value NULL.
format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'.

EXPLAIN PLAN SET STATEMENT_ID='TSH' FOR
SELECT *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    e.ename  = 'SMITH';

SET LINESIZE 130
SET PAGESIZE 0
SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','TSH','BASIC'));

----------------------------------------------------
| Id  | Operation                    |  Name       |
----------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  NESTED LOOPS                |             |
|   2 |   TABLE ACCESS FULL          | EMP         |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT        |
|   4 |    INDEX UNIQUE SCAN         | PK_DEPT     |
----------------------------------------------------

10 rows selected.

See: Explain Plan Usage

Hope this helps. Regards Tim...

Back to the Top.