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.

Weekly Oracle tips in your inbox

TechRepublic’s free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.

Automatically sign up today!

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.