The dbms_xplan PL/SQL package arrived with Oracle 9i. It had just one function, display(). Provided you have a plan table (created with $OH/rdbms/admin/utlxplan.sql) in your schema, you can use it like this in a sqlplus session:

EXPLAIN PLAN FOR
SELECT e.*, d.dept_name
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.ename='benoit';

Rem
Rem Display the plan using the DBMS_XPLAN.DISPLAY() table function
Rem
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Why use it? It provides explain plans formatted much better than the older pre 9i methods of displaying query explain plans. DBMS_XPLAN also provides more information needed for performance tuning. I thought I was pretty slick having this at my finger tips since 9i came out.

What I didn’t realize is that DBMS_XPLAN has been enhanced in 10G and further in 11G.

The comments for this Jonathan Lewis blog post tipped me off to the new functions. They led to Rob van Wijk’s detailed post on the new display_cursor function, as well as another Jonathan Lewis post on the function.

Needless to say, these enhancements have been around for 5 plus years and I’m just getting to them. Humbling!

By the way, the plan table supports keeping data for multiple statements at the same time. Handy if multiple developers need to run explain simultaneously. Here’s how: provide arguments to the dbms_xplan.display function.


-- Begin spooling to file.
spool my_file_name.txt

-- Capture database name and current user.
select * from global_name;
select user from dual;

-- Run query.
explain plan 
SET STATEMENT_ID = 'developer_01_sql_01' 
INTO plan_table for
SELECT e.*, d.dept_name
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.ename='benoit';

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

spool off
exit

About these ads