Advanced Oracle Explain Plan Techniques: Part 2

This is the second of an article series on advanced Oracle explain plan techniques (read Part 1 here). In this article, we continue down the DBMS_XPLAN path and dig a little deeper into DBMS_XPLAN to show useful SQL tuning information.

The above illustration shows the DBMS_XPLAN package and the three main functions I use to display useful explain plan information:

• Display – produces a nice explain plan from the last executed SQL
  • Is also the SQL*Plus autotrace setting
• Display_Cursor – produces about the same explain plan but has some other options available to it, allowing it to pull actual explain plans from V$SQL
• Display_Awr – is similar to Display_Cursor except that it pulls its information and explain plans from the AWR repository
  • Remember, you need additional Oracle licensing to review AWR data!

The above illustration shows the ABC sample SQL being executed with the AUTOTRACE feature on and set to EXPLAIN (see first line).  This is the same output as running the SQL statement then running ‘select * from table(DBMS_XPLAN.Display_Cursor).  The DBMS_XPLAN.Display will read the contents of the PLAN_TABLE and format this information similarly.  Use the syntax ‘EXPLAIN PLAN FOR’ in front of any SQL first.

This is a nice explain plan that also shows the WHERE clause items (Predicate Information).

DBMS_XPLAN can display any notes at the end, such as if dynamic sampling took place, and alos.

Let’s review some additional formatting features of DBMS_XPLAN.  The format options OUTLINE, ALL STATS LAST, and ADVANCED give additional useful information.  The outline produces a list of hints along with the explain plan and predicate information.  This list of hints, when placed in the SQL as hints, would produce the identical explain plan.  I find this useful when working with stubborn SQL that won’t stay tuned.

Another place it might be used is if there are ANY of the old rule-based optimizer SQL still in applications.  This tip works for those as well, and they can easily be converted to the cost-based optimizer using this list of hints.

Notice that these hints shown above are enclosed in the hint syntax /*+  followed by */.  This allows for the entire group to be easily copy and pasted into the SQL text.

Also notice the list of hints here matches up nicely with the explain plan also shown.  There are several hints of interest.  I use this list as a bit of a learning curve sometimes, to see what new hints Oracle might have introduced.  The DB_VERSION and OPTIMIZER_FEATURES_ENABLE allow for specific database versions to be set.  Perhaps this SQL worked better in a prior release.  The typical response for the DBA staff is to configure this setting as a global parameter (in the init.ora file) and the optimizer features are rolled back for ALL SQL.  This is the easy route, of course, but should be avoided if only a handful of SQL don’t like the newer database.

The remainder of this article series on DBMS_XPLAN will illustrate two more DBMS_XPLAN format options. Read Parts 3 and 4 to learn more.