TechRepublic : A ZDNet Tech Community

Format EXPLAIN PLAN results easily with Oracle's DBMS_XPLAN

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

  • Save
  • Print
  • Recommend
  • 1

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.

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.

  • Save
  • Print
  • Recommend
  • 1

What do you think?

White Papers, Webcasts, and Downloads

Article Categories

Security
Security Solutions, IT Locksmith
Networking and Communications
E-mail Administration NetNote, Cisco Routers and Switches
CIO and IT Management
Project Management, CIO Issues, Strategies that Scale
Desktops, Laptops & OS
Windows 2000 Professional, Microsoft Word, Microsoft Excel, Microsoft Access, Windows XP,
Data Management
Oracle, SQL Server
Servers
Windows NT, Linux NetNote, Windows Server 2003
Career Development
Geek Trivia
Software/Web Development
Web Development Zone, Visual Basic, .NET

SmartPlanet

Click Here