@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Execution plan from AWR
*  Parameters : 1 - SQL_ID          (Use % as wildcard) 
*               2 - PLAN_HASH_VALUE (Use % as wildcard)
*               3 - Number of Days  (Default 7 days) 
*               4 - Whereclause
*               
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  02-Jul-13  Vishal Gupta  Show other SQLIds with same force_matching_signature
*  18-Mar-12  Vishal Gupta  Intial version
*
*
*/

/************************************
*  INPUT PARAMETERS
************************************/

UNDEFINE sql_id
UNDEFINE plan_hash_value
UNDEFINE days
UNDEFINE whereclause
UNDEFINE force_matching_signature

DEFINE sql_id="&&1"
DEFINE plan_hash_value="&&2"
DEFINE days="&&3"
DEFINE whereclause="&&4"
DEFINE force_matching_signature=""


COLUMN  _plan_hash_value                NEW_VALUE  plan_hash_value                NOPRINT
COLUMN  _days                           NEW_VALUE  days                           NOPRINT
COLUMN  _force_matching_signature       NEW_VALUE  force_matching_signature       NOPRINT

set term off
SELECT DECODE('&&plan_hash_value','','%','&&plan_hash_value')   "_plan_hash_value"
     , DECODE('&&days','','7','&&days')                         "_days"
     , DECODE('&&sql_id','%','%','')                            "_force_matching_signature"
FROM DUAL;
set term on


/************************************/



/************************************
*  CONFIGURATION PARAMETERS
************************************/

DEFINE COUNT_SMALL_FORMAT=9,999
DEFINE COUNT_SMALL_DIVIDER="1"
DEFINE COUNT_SMALL_HEADING="#"
--DEFINE COUNT_SMALL_DIVIDER="1000"
--DEFINE COUNT_SMALL_HEADING="#1000"


DEFINE COUNT_FORMAT=99,999,999
DEFINE COUNT_DIVIDER="1"
DEFINE COUNT_HEADING="#"
--DEFINE COUNT_DIVIDER="100"
--DEFINE COUNT_HEADING="#100"
--DEFINE COUNT_DIVIDER="1000"
--DEFINE COUNT_HEADING="#1000"

DEFINE COUNT_LARGE_FORMAT=9,999
--DEFINE COUNT_LARGE_DIVIDER="1"
--DEFINE COUNT_LARGE_HEADING="#"
--DEFINE COUNT_LARGE_DIVIDER="1000"
--DEFINE COUNT_LARGE_HEADING="#1000"
DEFINE COUNT_LARGE_DIVIDER="1000000"
DEFINE COUNT_LARGE_HEADING="#mil"



DEFINE BYTES_FORMAT="999,999"

--DEFINE BYTES_DIVIDER="1024"
--DEFINE BYTES_HEADING="KB"
--DEFINE BYTES_DIVIDER="1024/1024"
--DEFINE BYTES_HEADING="MB"
DEFINE BYTES_DIVIDER="1024/1024/1024"
DEFINE BYTES_HEADING="GB"

DEFINE TIME_FORMAT=999,999

--DEFINE TIME_DIVIDER="0.001"
--DEFINE TIME_HEADING="msec"
DEFINE TIME_DIVIDER="1"
DEFINE TIME_HEADING="sec"
--DEFINE TIME_DIVIDER="60"
--DEFINE TIME_HEADING="min"
--DEFINE TIME_DIVIDER="3600"
--DEFINE TIME_HEADING="hr"


/************************************/


COLUMN  _force_matching_signature          NEW_VALUE  force_matching_signature            NOPRINT

set term off
select  to_char(ss2.force_matching_signature) "_force_matching_signature" 
  from  v$database d
      , dba_hist_sqlstat ss2 
  where d.dbid = ss2.dbid
    AND ss2.sql_id = '&&sql_id' and rownum = 1;

select CASE WHEN '&&force_matching_signature' IS NULL THEN  '0'  
            WHEN '&&force_matching_signature' = '' THEN  '0'   
            ELSE '&&force_matching_signature' 
       END "_force_matching_signature" 
  from dual
;

set term on

/************************************/

Prompt **************************************************************
Prompt * AWR SQL Executions Plans 
Prompt *  (Force Matching Signature = '&&force_matching_signature')
PROMPT * 
PROMPT * Input Parameters 
PROMPT *    - SQL Id           = '&&sql_id'
PROMPT *    - Plan Hash Value  = '&&plan_hash_value'
PROMPT *    - Days             = '&&days'
PROMPT *    - WhereClause      = '&&whereclause'
Prompt **************************************************************


PROMPT 
PROMPT #################################################################
PROMPT #       S Q L     P L A N   H A S H   V A L U E 
PROMPT #################################################################

COLUMN force_matching_signature    HEADING "Force Matching Signature"    FORMAT 99999999999999999999999
COLUMN sql_id                      HEADING "SQL_ID"                      FORMAT a13
COLUMN plan_hash_value             HEADING "Plan|Hash|Value"             FORMAT 9999999999999
COLUMN cost                        HEADING "Cost"                        FORMAT 9999999999
COLUMN last_used                   HEADING "LastUsed"                    FORMAT a20
COLUMN first_used                  HEADING "FirstUsed"                   FORMAT a20
COLUMN first_parsed                HEADING "FirstParsed"                 FORMAT a20

COLUMN iowait_delta                    HEADING "IO|Wait|(&&TIME_HEADING)|Per|Exec"                 FORMAT &&TIME_FORMAT     
COLUMN apwait_delta                    HEADING "Appl|Wait|(&&TIME_HEADING)|Per|Exec"               FORMAT &&TIME_FORMAT     
COLUMN ccwait_delta                    HEADING "Conc|Wait|(&&TIME_HEADING)|Per|Exec"               FORMAT &&TIME_FORMAT     
COLUMN clwait_delta                    HEADING "Cluster|Wait|(&&TIME_HEADING)|Per|Exec"            FORMAT &&TIME_FORMAT           
COLUMN plsexec_time_delta              HEADING "PLsexec|Time|(&&TIME_HEADING)|Per|Exec"            FORMAT &&TIME_FORMAT     
COLUMN cpu_time_delta                  HEADING "CPU|Time|(&&TIME_HEADING)|Per|Exec"                FORMAT &&TIME_FORMAT     
COLUMN elapsed_time_delta              HEADING "Elapsed|Time|(&&TIME_HEADING)|Per|Exec"            FORMAT &&TIME_FORMAT     
COLUMN executions_delta                HEADING "Delta|Exec|(&&COUNT_HEADING)"                      FORMAT &&COUNT_FORMAT
COLUMN rows_processed_delta            HEADING "Rows|(&&COUNT_HEADING)|Per|Exec"                   FORMAT &&COUNT_FORMAT 
COLUMN sorts_delta                     HEADING "Sorts|(&&COUNT_SMALL_HEADING)|Per|Exec"            FORMAT &&COUNT_SMALL_FORMAT 
COLUMN buffer_gets_delta               HEADING "Buffer|Gets|(&&COUNT_LARGE_HEADING)|Per|Exec"      FORMAT &&COUNT_LARGE_FORMAT 
COLUMN direct_writes_delta             HEADING "Direct|WriteReq|(&&COUNT_HEADING)|Per|Exec"        FORMAT &&COUNT_FORMAT
COLUMN io_interconnect_bytes_delta     HEADING "IO|Inter|Connect|(&&BYTES_HEADING)|Per|Exec"       FORMAT &&BYTES_FORMAT 
COLUMN physical_read_bytes_delta       HEADING "Phy|Read|(&&BYTES_HEADING)|Per|Exec"               FORMAT &&BYTES_FORMAT     
COLUMN physical_write_bytes_delta      HEADING "Phy|Write|(&&BYTES_HEADING)|Per|Exec"              FORMAT &&BYTES_FORMAT     
COLUMN optimized_physical_reads_delta  HEADING "Optimized|Phy|ReadReq|(&&COUNT_HEADING)|Per|Exec"  FORMAT &&COUNT_FORMAT
COLUMN io_offload_elig_bytes_delta     HEADING "IO|Offload|Elig|(&&BYTES_HEADING)|Per|Exec"        FORMAT &&BYTES_FORMAT     
COLUMN io_offload_return_bytes_delta   HEADING "IO|Offload|Return|(&&BYTES_HEADING)|Per|Exec"      FORMAT &&BYTES_FORMAT     
COLUMN sql_profile                     HEADING "sql_profile"                                      FORMAT a30



SELECT 
                              p.sql_id
                            , p.plan_hash_value
                            , p.cost
                            , to_char(MAX(s.end_interval_time) ,'DD-MON-YY HH24:MI:SS')                                            last_used
                          --, to_char(MIN(s.end_interval_time) ,'DD-MON-YY HH24:MI:SS')                                            first_used
                            , to_char(MIN(p.timestamp) ,'DD-MON-YY HH24:MI:SS')                                                    first_parsed
                            , ROUND(sum(ss.executions_delta)/&&COUNT_DIVIDER)                                                      executions_delta 
                            , ROUND(sum(ss.iowait_delta)/1000000/GREATEST(sum(ss.executions_delta),1)/&&TIME_DIVIDER)              iowait_delta
                            , ROUND(sum(ss.cpu_time_delta)/1000000/GREATEST(sum(ss.executions_delta),1)/&&TIME_DIVIDER)            cpu_time_delta
                            , ROUND(sum(ss.elapsed_time_delta)/1000000/GREATEST(sum(ss.executions_delta),1)/&&TIME_DIVIDER)        elapsed_time_delta
                            , ROUND(sum(ss.buffer_gets_delta)/GREATEST(sum(ss.executions_delta),1)/&&COUNT_LARGE_DIVIDER)          buffer_gets_delta
&&_IF_ORA_11gR2_OR_HIGHER   , ROUND(sum(ss.physical_read_bytes_delta)/GREATEST(sum(ss.executions_delta),1)/&&BYTES_DIVIDER)        physical_read_bytes_delta
&&_IF_ORA_11gR2_OR_HIGHER   , ROUND(sum(ss.physical_write_bytes_delta)/GREATEST(sum(ss.executions_delta),1)/&&BYTES_DIVIDER)       physical_write_bytes_delta
&&_IF_ORA_11gR2_OR_HIGHER   , ROUND(sum(ss.direct_writes_delta)/GREATEST(sum(ss.executions_delta),1)/&&COUNT_DIVIDER)              direct_writes_delta
&&_IF_ORA_11gR2_OR_HIGHER   , ROUND(sum(ss.optimized_physical_reads_delta)/GREATEST(sum(ss.executions_delta),1)/&&COUNT_DIVIDER)   optimized_physical_reads_delta
  FROM v$database d
     , dba_hist_sql_plan p
     , dba_hist_sqlstat ss
     , dba_hist_snapshot s
 WHERE d.dbid              = p.dbid
   AND p.dbid              = ss.dbid 
   AND p.sql_id            = ss.sql_id 
   AND p.plan_hash_value   = ss.plan_hash_value 
   AND ss.dbid             = s.dbid 
   AND ss.instance_number  = s.instance_number 
   AND ss.snap_id          = s.snap_id 
   AND s.end_interval_time >  SYSDATE - &&days 
   AND p.id = 0 -- Top row of SQL Plan only which has cost as well
   AND ss.sql_id LIKE CASE ss.force_matching_signature 
                          WHEN 0 THEN '&&sql_id'
                          ELSE '%'
                      END
   AND ss.force_matching_signature = '&&force_matching_signature'
   AND ss.plan_hash_value          LIKE '&&plan_hash_value'
   &&whereclause
GROUP BY ss.force_matching_signature
       , p.sql_id
       , p.plan_hash_value
       , p.cost    
ORDER BY MAX(s.end_interval_time) desc
/

PROMPT 
PROMPT 
PROMPT #################################################################
PROMPT #  E X E C U T I O N     P L A N S
PROMPT #################################################################
PROMPT 

SET long 4000 
SET longchunksize 4000 
set pages 0
set lines 250

COLUMN plan_table_output             HEADING "SQLText"  FORMAT a200 WRAP


SELECT * 
FROM table(DBMS_XPLAN.display_awr( sql_id          => '&&sql_id'
                                 , plan_hash_value => DECODE('&&plan_hash_value','%',-1,TO_NUMBER('&&plan_hash_value'))
                                 , db_id           => (select dbid from v$database)
                                 , format          => 'ADVANCED ALLSTATS PREDICATE PROJECTION '
                                 )
          )
;


@@footer