Plan Change for SQL ID

Script 1:

set lines 150 pages 50
column begin_interval_time format a25
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

select begin_interval_time,plan_hash_value,cpu_time_delta,elapsed_time_delta,iowait_delta,disk_reads_delta
from dba_hist_sqlstat A,dba_hist_snapshot B
where A.snap_id = B.snap_id
and   A.sql_id = '&sql_id'
order by begin_interval_time;

Script 2:

accept sql_id -

       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'

set lines 155
col execs for 999,999,999
col etime for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
break on plan_hash_value on startup_time skip 1
select sql_id, plan_hash_value, sum(execs) execs, sum(etime) etime, sum(etime)/sum(execs) avg_etime, sum(lio)/sum(execs) avg_lio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5
/

SELECT DISTINCT sql_id, plan_hash_value, TO_CHAR(TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') Plan_creation_ts
FROM DBA_HIST_sql_plan
WHERE sql_id = '&sql_id'
/

undef sql_id

Script 3:

/* This query should be used to see the number of times a plan has been used

and see if a different plan has been used for a given piece of stored sql ( sql_id).

In particular observe the average lio (logical io ) ie blocks retrieved from the buffer cache

and the average execution time ( avg_etime)

 */

select trunc(begin_interval_time) as dt,

    sql_id, plan_hash_value, END_OF_FETCH_COUNT_TOTAL + (1 - END_OF_FETCH_COUNT_DELTA) As Exections,

    sum(executions_delta) execs ,

    min(begin_interval_time), max(end_interval_time),

    sum((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,Decode(executions_delta,0,1,executions_delta)))/1000000) avg_etime,

    sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,Decode(executions_delta,0,1,executions_delta)))) avg_lio,

    sum((disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,Decode(executions_delta,0,1,executions_delta)))) disk_reads,

    sum((rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,Decode(executions_delta,0,1,executions_delta)))) avg_rows,

    sum((CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,Decode(executions_delta,0,1,executions_delta)))) as CPU_TIME,

    sum((IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,1))) as IOWAIT,

    sum((CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,1))) as CLWAIT,

    sum((APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,1))) as APWAIT,

    sum((CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,1))) as CCWAIT,

    max(PX_SERVERS_EXECS_DELTA) as Threads

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where  sql_id ='&sql_id' 

    and ss.snap_id = S.snap_id

    and ss.instance_number = S.instance_number

--    and begin_interval_time > TO_DATE ('01/12/2010', 'dd/mm/yyyy')

GROUP BY trunc(begin_interval_time), sql_id, plan_hash_value, END_OF_FETCH_COUNT_TOTAL + (1 - END_OF_FETCH_COUNT_DELTA)

ORDER BY 1 desc;

prompt enter start and end times in format DD-MON-YYYY [HH24:MI]

column sample_end format a21

select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end

, q.sql_id

, q.plan_hash_value

, sum(q.EXECUTIONS_DELTA) executions

, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec

, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec

, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.SQL_ID=trim('&sql_id')

and s.snap_id = q.snap_id

and s.dbid = q.dbid

and s.instance_number = q.instance_number

group by s.snap_id

, q.sql_id

, q.plan_hash_value

order by s.snap_id, q.sql_id, q.plan_hash_value

;