Static Analysis of Execution Plans

I started working on Static Analysis of Explain Plans when presented with a 1600 line query (680 Explain Plan steps) which was running 11 hours then bombing out.

Remember that Explain Plan is a "Best Guess". I usually resort to it when presented with a query that runs very long or doesn't finish because of temp space issues. If the query can finish in a reasonable amount of time I prefer to use SQL Monitoring or GATHER_PLAN_STATISTICS with DBMS_XPLAN to find exactly how long each step took.

The purpose of this exercise is to hopefully try to find expensive operations that we can tackle first. Once we free up resources then we can tack the next espensive operation and so on.

So how do we find possible tuning opportunities?
o Generate the Explain Plan
o Query the generated Explain Plan it for Full Table Scans, costly steps, steps that will use a lot of TEMP space, etc.

1) Generate an Explain Plan using standard methods

delete from plan_table where statement_id='MYSTMT';
explain plan set statement_id='MYSTMT' for 
select * from dual;

2) Analyze the results

-- Top 20 Full Table Scans sorted by Bytes Visited
WITH MY_PLAN AS (SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID='MYSTMT'),
     OutputRows AS (
SELECT ID, PARENT_ID, OPTIONS, OPERATION, OBJECT_OWNER, OBJECT_NAME,COST, 
       CARDINALITY, ROUND(BYTES/1024/1024,2) as mb, FILTER_PREDICATES  
  FROM MY_PLAN
 WHERE OPTIONS LIKE '%FULL%'
   AND OBJECT_TYPE IN ('MAT_VIEW','TABLE')
ORDER BY BYTES DESC NULLS LAST)
SELECT * FROM OutputRows where rownum <= 20;

-- Top 20 Expensive joins, see ACCESS_PREDICATES for join criteria
WITH MY_PLAN AS (SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID='MYSTMT'),
OutputRows AS (
SELECT ID, PARENT_ID, OPTIONS, OPERATION, COST, CARDINALITY, ROUND(BYTES/1024/1024,2) as MB, 
        ROUND(TEMP_SPACE/1024/1024) as TEMP_SPACE_MB, ACCESS_PREDICATES
  FROM MY_PLAN
 WHERE OPERATION LIKE '%JOIN%'
 ORDER BY COST DESC NULLS LAST)
SELECT * FROM OutputRows where rownum <= 20;

-- Top 10 Expensive Temp Operations 
WITH OutputRows AS (select * From plan_table where statement_id='MYSTMT'
 ORDER BY TEMP_SPACE DESC NULLS LAST)
SELECT ID, PARENT_ID,
       OPERATION, OPTIONS, 
       ROUND(BYTES/1024/1024/1024,2) as VISITED_GB, 
       ROUND(TEMP_SPACE/1024/1024/1024,2) as TEMP_SPACE_GB 
  FROM OutputRows 
 WHERE rownum <= 10;


-- Survey of Table/MatView/Index size for objects involved in query
-- Note: objects may be referenced multiple times by multiple steps
WITH Objects
        AS (SELECT ID,
                   PARENT_ID,
                   OPTIONS,
                   OPERATION,
                   OBJECT_OWNER,
                   OBJECT_NAME
              FROM plan_table
             WHERE statement_id = 'DMANN'
                   AND OBJECT_TYPE IN ('MAT_VIEW', 'TABLE','INDEX'))
  SELECT Objects.*, ROUND (BYTES / 1024 / 1024, 2) AS OBJECT_SIZE_MB
    FROM USER_SEGMENTS, OBJECTS
   WHERE Objects.OBJECT_NAME = USER_SEGMENTS.SEGMENT_NAME
         AND BYTES / 1024 / 1024 > 50
ORDER BY BYTES DESC;

-- Cardinality Survey (Estimated)
WITH OUTPUTROWS AS (
SELECT ID, PARENT_ID, OPTIONS, OPERATION, OBJECT_OWNER, OBJECT_NAME,
       CARDINALITY, COST, ROUND(BYTES/1024/1024,2) as VISIT_MB, 
       ROUND(TEMP_SPACE/1024/1024,2) AS TEMP_MB, 
       ACCESS_PREDICATES||FILTER_PREDICATES AS PREDICATES
  FROM plan_table where statement_id='MYSTMT'
   AND CARDINALITY IS NOT NULL
 ORDER BY CARDINALITY DESC, COST DESC)
SELECT * FROM OUTPUTROWS WHERE rownum <= 20;

-- Partitioned tables with no pruning
To Do