Format EXPLAIN PLAN results easily with Oracle's DBMS_XPLAN

by Bob Watkins | May 16, 2006 7:00:00 AM

Tags: Programming languages, Databases, Bob Watkins, Oracle Corp., execution plan, SQL, Explain Plan, SQL Statement, PLAN_TABLE, DBMS_XPLAN.DISPLAY, DBMS_XPLAN, Oracle Tips Newsletter

Takeaway: When you need to troubleshoot inefficient SQL, one of the most important diagnostics tools at your disposal is the output of the EXPLAIN PLAN command. Find out how Oracle's DBMS_XPLAN functions can help you tune your SQL statements.

One of the most important diagnostic tools in troubleshooting inefficient SQL is the output of the EXPLAIN PLAN command. This command loads a work table (called PLAN_TABLE by default) with the execution plan steps that the Oracle Optimizer has calculated for a SQL statement. For example:

EXPLAIN PLAN FOR

   SELECT empno, ename

   FROM emp

   WHERE ename LIKE 'S%';

Because the rows in the PLAN_TABLE form a hierarchy, queries against it require the complicated START WITH and CONNECT BY clauses of the SELECT statement. DBMS_XPLAN, introduced in Oracle 9i and expanded in 10g, makes it much easier to format and display execution plans.

DBMS_XPLAN.DISPLAY is a table valued function that displays the contents of a PLAN_TABLE. Unlike regular scalar functions like SUBSTR or aggregate functions like SUM, table functions return a complete rowset. They are used in the FROM clause of the SELECT statement, and must be preceded by the word TABLE. For example:

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY);

Listing A shows the results of the above EXPLAIN PLAN statement, as formatted by DBMS_XPLAN.DISPLAY.

What's even more remarkable is that DBMS_XPLAN can also display "live" execution plans from cursors stored within the System Global Area (SGA). Listing B shows the same SQL statement executed by user SCOTT. By querying the V$SESSION view, you can see the last SQL ID executed in SCOTT's session. This, in turn, can be fed into DBMS_XPLAN.DISPLAY_CURSOR to get the execution plan used by that cursor.

Finally, the DBMS_XPLAN.DISPLAY_AWR function can be used to look up an historical SQL statement captured in Oracle 10g's Automatic Workload Repository (AWR), and display its execution plan. This gives you a seven-day rolling window of history that you can access.

All three of these functions are powerful tools for tuning your SQL statements.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator.