How the Oracle Explain Plan Can Uncover Slow SQL Queries

The prior article finished the discussion on hard parsing.  This article will take a look at some of the explain plan content then wrap up with what to look for when you have poorly performing SQL.

Up to now, we have been discussing how Oracle processes SQL, the hard and soft parse processes, and how the execution plan is arrived at.  I’ve been using some illustrations of SQL and explain plans to help deliver the message. Some information found in the explain plan include:

• Access Predicate – this is rows being filtered from a table or physical structure access
• Filter Predicate – is filtering rows from the internal rowset
• Table Access – accessing a table structure
• View of – accessing a sub query/view/inline view
• Internal Function – New to Oracle11…indicates when a function was added internally to fix a data type mismatch in the where clause
• Concatenation – SQL has a union clause
• INDEX (Unique) – index accessing a single row.
  • WHEN ever you see UNIQUE in an explain plan…it means 1 row
• INDEX (Range Scan) – index accessing one or more leaf blocks.  The COST number on this explain plan row gives an indication how wide the scan is
  • There are several types of Index scans…perhaps covered better in a future article
• INLIST ITERATOR – SQL has an in clause or a series of OR statements that Query Transformation rewrote into an IN clause
• Bitmap Conversion – an index merge operation…a good thing usually
  • Only for Enterprise Edition of Oracle
• Bitmap Index – using an bitmap index
• Partitioning – Explain plans do a nice job on partitioned objects.  Perhaps covered in another article
• SORT – used for order by/group by/aggregate functions.  IF the CBO is using an index to maintain the order, you might not see the SORT clause in the explain plan.
• Stop Key – has a ROWNUM listed in the where clause
• These join conditions have options like INNER, OUTER, CARTEASAN, etc
  • The output of one join condition into another…the output becomes the first table to the next join
  • Nested Loops – a join condition between 2 objects
    • Larger number of rows being compared to a small number of rows
      • First table should be the larger set, second table the smaller set
    • The smaller the small number of rows, the better
    • Indexes both help the CBO determine to use this and then access the rows
  • Hash Join – a join condition between 2 objects
    • Smaller number of rows being compared to a larger set
    • Works only on equal conditions
  • Merge Join – a join condition between 2 objects
    • Generally for larger sets of rows from 2 objects
    • Doesn’t matter which one is first generally

Let’s wrap up the series with a look at these join conditions that might be in the wrong order, using the example explain plan below.

 

Figure 1. Example Oracle Explain Plan.

Notice the syntax here.  The SQL statement has a hint in it called ‘GATHER_PLAN_STATISTICS’.  When this hint is included and is teamed up with the DBMS_XPLAN.Display_Cursor syntax listed above, you get the CBO row counts that it thought were going to be there and the actual row counts from the actual execution.  It is very important to get both for your problem SQL.  This syntax was introduced in Oracle10.

IF a nested loop is going smaller to larger, or, a hash join is going larger to smaller…the performance will be poor, even though the math works out the same.  The CBO might show things in the correct order but you really need to see what it ACTUALLY executed against, and this syntax shows that.

 

Notice in this example that the E-Rows is what the CBO thought was going to be there at hard parse time.  The hard parse determines the order of the processing.  Notice the A-Rows, or actual rows…if you see ID line 4…is significantly higher but is still going smaller to larger on a hash join.  Notice though that ID line 3 at 94K is doing a hash join with ID line 6 at 61400…larger to smaller…not good.

I wrote this free tool quite a while ago to use in my Oracle classes.  If you put the hint in as the first hint, my program recognizes the event and runs the correct DBMS_XPLAN syntax instead of the autotrace!  Makes life easy.  There are some other features in here that I cover in class to make your life easy when it comes to SQL statement tuning.  This tool is free at www.DanHotka.com.