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  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