Thursday, 25 May 2017

How to find the SQL script which executed more than 5 hrs in database

set wrap off
col elapsed_time_delta format 9999999999
col plan_hash_value    format 9999999999
col seconds            format 99999
col executions_total   format 99999999

select
   stat.sql_id,
   plan_hash_value,
   rpad(parsing_schema_name,10) "schema",elapsed_time_total/1000000 "seconds",  
   elapsed_time_delta,disk_reads_delta,
   stat.executions_total,
   to_char(ss.end_interval_time,'dd-mm-yy hh24:mi:ss') "endtime",
   rpad(sql_text,40) text,ss.snap_id
from
   dba_hist_sqlstat  stat,
   dba_hist_sqltext  txt,
   dba_hist_snapshot ss
where
   stat.sql_id = txt.sql_id
and
   stat.dbid = txt.dbid
and
   ss.dbid = stat.dbid
and
   ss.instance_number = stat.instance_number
and
   stat.snap_id = ss.snap_id
and
   parsing_schema_name not like 'sys%'
and
   ss.begin_interval_time >= sysdate-40 ----you can change it accordingly
and
   stat.elapsed_time_total/1000000 > 18000 ----you can change it accordingly
order by
elapsed_time_total desc;



No comments:

Post a Comment