Advanced Oracle Explain Plan Techniques: Part 1

This is the first in an article series that will illustrate some advanced SQL explain plan techniques that were introduced with Oracle 10g.  Oracle 10g was a big change for the Oracle optimizer, the code that tries to determine the best path to retrieve the requested data.  Changes include how Oracle uses indexes and just a better overall improvement in the SQL plans.  This article series might also include a bit on the SQL Plan Management (SPM) that Oracle introduced with this same database release.

Oracle now uses DBMS_XPLAN.Display table function as the Autotrace feature in SQL*Plus.  This function–turned on with ‘set autotrace on explain’, or ‘set autotrace on explain only’–doesn’t execute the SQL, it just produces the explain plan.  The function is similar to this syntax:

select * from table(dbms_xplan.display);

You can also include the syntax ‘explain plan for’ in front of your SQL statement and run it in SQL*Plus.  SQL*Plus will reply with ‘Explained’ and the PLAN_TABLE (also discussed in an earlier LogicalRead article) will be populated.  The ‘select * from table(dbms_xplan.display)’ will format and display the current contents of this PLAN_TABLE.

DBMS_XPLAN can be used to display explain plan information from other locations as well.  DBMS_XPLAN.Display_Cursor takes into account the SQL_ID, an identifier for each executed SQL (also first appeared in Oracle 10) and will produce an explain plan out of the V$SQL dictionary view and is useful for SQL that has been recently executed.  This explain plan is one that the SQL actually executed with.  The explain plans produced with the prior discussed SQL*Plus examples produces an explain plan because you asked for one.  There could be differences in these explain plans.

DBMS_XPLAN.Display_awr also takes the SQL_ID identifier but looks in the Automated Workload Repository (AWR) and reports back explain plans that the SQL had also executed with but over the prior seven days.  AWR is a repository first established with Oracle10 and stores quite a bit of information, including SQL and explain plans.

You need to have the proper Oracle licensing to look at any AWR information.  Seriously.  Oracle collects it but you can’t look at it without paying more.

Take a moment to notice the various DBMS_XPLAN functions shown in the list above.  The ones that return dbms_xplan_type_table  display explain plan information.  These are also programmed as table functions and you call them from the ‘from’ syntax line, for example:

‘select * from table(dbmx_xplan.display)’;

There are many other features of this Oracle package, which also has functions that can show the differences between explain plans as well.

CREATE OR REPLACE TYPE SYS.dbms_xplan_type_table

  as table of dbms_xplan_type;

/


CREATE OR REPLACE TYPE SYS.dbms_xplan_type

  as object (plan_table_output varchar2(300));

/

Table functions utilize these special types.  You can see the DBMS_XPLAN_TYPE_TABLE being used in the first 5 DBMS_XPLAN functions and this type is simply a PL/SQL collection of a varchar2 string 300 in length.

Look for future articles in this series to focus on various DBMS_XPLAN syntax to illustrate various examples and uses and how it has evolved through Oracle 12. Read Parts 2, 3 and 4 now.