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.
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