Sometimes user or application team comes to us and question what was running hostrocally at particular moment which slowdown their application , we have to use historical view to check these details, below script can be used to find out historical details like SQL_ID, SQL_PROFILE, LIO ,PIO etc.
You can give your time lines in below script.
select trunc(b.end_interval_time) SNAP_DATE ,a.sql_id ,a.module
,a.plan_hash_value ,a.sql_profile --,sum(a.PX_SERVERS_EXECS_DELTA)
PX_PER_EXECUTIONS ,sum(a.executions_delta) EXECUTIONS
,sum(a.disk_reads_delta) DISK_READS_PIO ,sum(a.buffer_gets_delta)
BUFFER_GETS_LIO ,sum(a.direct_writes_delta) direct_writes
,sum(a.rows_processed_delta) rows_processed
,trunc(sum(a.elapsed_time_delta)/1000000/60/60,2) elap_hrs
,trunc(sum(a.cpu_time_delta)/1000000/60/60,2) cpu_hrs
,trunc(sum(a.clwait_delta)/1000000/60/60,2) clw_hrs
,trunc(sum(a.apwait_delta)/1000000/60/60,2) apw_hrs
,trunc((sum(a.buffer_gets_delta)-sum(a.disk_reads_delta))/(sum(decode(a.buffer_gets_delta,0,1,a.buffer_gets_delta))/100),2)
chr
,trunc((sum(a.elapsed_time_delta)-sum(a.apwait_delta))/sum(decode(a.buffer_gets_delta,0,1,a.buffer_gets_delta)))
elap_lio
,trunc(sum(a.elapsed_time_delta)/1000000/60/60,2)-trunc(sum(a.apwait_delta)/1000000/60/60,2)
act_hrs select trunc(b.end_interval_time) SNAP_DATE ,a.sql_id ,a.module
,a.plan_hash_valu ,a.sql_profile --,sum(a.PX_SERVERS_EXECS_DELTA)
PX_PER_EXECUTIONS ,sum(a.executions_delta) EXECUTIONS
,sum(a.disk_reads_delta) DISK_READS_PIO ,sum(a.buffer_gets_delta)
BUFFER_GETS_LIO ,sum(a.direct_writes_delta) direct_writes
,sum(a.rows_processed_delta) rows_processed
,trunc(sum(a.elapsed_time_delta)/1000000/60/60,2) elap_hrs
,trunc(sum(a.cpu_time_delta)/1000000/60/60,2) cpu_hrs
,trunc(sum(a.clwait_delta)/1000000/60/60,2) clw_hrs
,trunc(sum(a.apwait_delta)/1000000/60/60,2) apw_hrs
,trunc((sum(a.buffer_gets_delta)-sum(a.disk_reads_delta))/(sum(decode(a.buffer_gets_delta,0,1,a.buffer_gets_delta))/100),2)
chr
,trunc((sum(a.elapsed_time_delta)-sum(a.apwait_delta))/sum(decode(a.buffer_gets_delta,0,1,a.buffer_gets_delta)))
elap_lio
,trunc(sum(a.elapsed_time_delta)/1000000/60/60,2)-trunc(sum(a.apwait_delta)/1000000/60/60,2)
act_hrs ,trunc(sum(a.IOWAIT_DELTA)/1000000/60/60,2) iow_hrs from
dba_hist_sqlstat a ,dba_hist_snapshot b ,dba_hist_sqltext c where 1=1 and
a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number =
b.instance_number and a.sql_id = c.sql_id and a.dbid = c.dbid and
c.command_type != 47 --and a.snap_id between 149529 and 149533 and
b.end_interval_time between to_date('27-JUL-2023 08:00:00','dd-mon-yyyy
hh24:mi:ss') and to_date('04-AUG-2023 09:00:00','dd-mon-yyyy hh24:mi:ss')
,trunc(sum(a.IOWAIT_DELTA)/1000000/60/60,2) iow_hrs from dba_hist_sqlstat a
,dba_hist_snapshot b ,dba_hist_sqltext c where 1=1 and a.snap_id = b.snap_id
and a.dbid = b.dbid and a.instance_number = b.instance_number and a.sql_id =
c.sql_id and a.dbid = c.dbid and c.command_type != 47 --and a.snap_id
between 149529 and 149533 and b.end_interval_time between
to_date('27-JUL-2023 08:00:00','dd-mon-yyyy hh24:mi:ss') and
to_date('04-AUG-2023 09:00:00','dd-mon-yyyy hh24:mi:ss') and a.sql_id in
('34d70ygdz5csd') --and a.plan_hash_value in ('273738736') group by
trunc(b.end_interval_time) ,a.sql_id ,a.module ,a.plan_hash_value
,a.sql_profile order by 1 desc;
## Replace SQL_ID and dates of execution
No comments:
Post a Comment