@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display unstable execution plan from AWR
*  Version    : 
*  Parameters : 1 - From Timestamp (YYYY-MM-DD HH24:MI:SS)
*               2 - To Timestamp   (YYYY-MM-DD HH24:MI:SS)
*               3 - Top record Count
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  12-Jun-12  Vishal Gupta  Intial version
*
*
*/



/************************************
*  INPUT PARAMETERS
************************************/
UNDEFINE FROM_TIMESTAMP                   
UNDEFINE TO_TIMESTAMP
UNDEFINE TOP_RECORDCOUNT

DEFINE FROM_TIMESTAMP="&&1"
DEFINE TO_TIMESTAMP="&2"
DEFINE TOP_RECORDCOUNT="&3"


COLUMN  _FROM_TIMESTAMP      NEW_VALUE FROM_TIMESTAMP     NOPRINT
COLUMN  _TO_TIMESTAMP        NEW_VALUE TO_TIMESTAMP       NOPRINT
COLUMN  _TOP_RECORDCOUNT     NEW_VALUE TOP_RECORDCOUNT    NOPRINT


set term off
SELECT DECODE('&&FROM_TIMESTAMP','',to_char(sysdate - 7 - (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&FROM_TIMESTAMP')  "_FROM_TIMESTAMP"
     , DECODE('&&TO_TIMESTAMP','',to_char(sysdate + (1/1440),'YYYY-MM-DD HH24:MI:SS'),'&&TO_TIMESTAMP')  "_TO_TIMESTAMP"
     , DECODE('&&TOP_RECORDCOUNT','','20','&&TOP_RECORDCOUNT')  "_TOP_RECORDCOUNT"
FROM DUAL
;

set term on


PROMPT 
PROMPT ***********************************************************************
PROMPT *   Top &&TOP_RECORDCOUNT SQLIds with unstable execution plan
PROMPT ***********************************************************************


COLUMN force_matching_signature        HEADING "Force Matching Signature"        FORMAT 999999999999999999999
COLUMN elapsed_time_perexec_max        HEADING "Max|Elapsed|Time|(sec)"          FORMAT 999,999,999.999
COLUMN elapsed_time_perexec_min        HEADING "Min|Elapsed|Time|(sec)"          FORMAT 999,999,999.999
COLUMN elapsed_time_perexec_avg        HEADING "Avg|Elapsed|Time|(sec)"          FORMAT 999,999,999.999
COLUMN elapsed_time_perexec_deviation  HEADING "Deviation|Elapsed|Time|(sec)"    FORMAT 999,999,999.999

SELECT * 
FROM
(
SELECT ss.force_matching_signature
     , max(ss.sql_id) sql_id
     , max(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_max
     , min(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_min
     , avg(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_avg
     , (max(ss.elapsed_time_total/1000000/ss.executions_total) 
           - avg(ss.elapsed_time_total/1000000/ss.executions_total) 
          )
          / avg(ss.elapsed_time_total/1000000/ss.executions_total) elapsed_time_perexec_deviation
  FROM v$database d
     , dba_hist_snapshot s
     , dba_hist_sqlstat ss
   --  , dba_hist_sql_plan p
 WHERE d.dbid             = s.dbid
   AND ss.dbid            = s.dbid 
   AND ss.instance_number = s.instance_number 
   AND ss.snap_id         = s.snap_id 
 --  AND p.dbid             = ss.dbid (+)
 --  AND p.sql_id           = ss.sql_id (+)
 --  AND p.plan_hash_value  = ss.plan_hash_value (+)
 --  AND p.id               = 0 -- Top row which has cost as well
   AND s.begin_interval_time BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS') 
                                 AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS')
   AND s.end_interval_time   BETWEEN TO_TIMESTAMP('&&from_timestamp','YYYY-MM-DD HH24:MI:SS') 
                                 AND TO_TIMESTAMP('&&to_timestamp','YYYY-MM-DD HH24:MI:SS')
   AND ss.force_matching_signature <> 0                                 
   AND ss.executions_delta > 0  
   AND ss.executions_total > 0   
   AND ss.elapsed_time_total > 0
GROUP BY ss.force_matching_signature
/*
HAVING (max(ss.elapsed_time_total/1000000/ss.executions_total) 
           - avg(ss.elapsed_time_total/1000000/ss.executions_total) 
          )
          / avg(ss.elapsed_time_total/1000000/ss.executions_total)  > 0.1
*/          
ORDER BY elapsed_time_perexec_deviation desc
)
WHERE ROWNUM <= &&TOP_RECORDCOUNT
/

UNDEFINE FROM_TIMESTAMP
UNDEFINE TO_TIMESTAMP
UNDEFINE TOP_RECORDCOUNT

@@footer