Skip to content

Oracle, find query witch runs more than &1 sec

Oracle, find query witch runs more than &1 sec published on Комментариев к записи Oracle, find query witch runs more than &1 sec нет
with stats as (
select /*+ materialize */
 * from (
select  parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
 round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,'iw')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by  sql_id, plan_hash_value,parsing_schema_name
 )t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
 rownum <= 50 )
 select * from stats s 
 where parsing_schema_name not like 'SYS%'
  and s.sql_text not like '%/* DS_SVC */%'
  and s.sql_text not like '%_job_proc%'
  and s.sql_text not like '%SQL Analyze%'
  and lower(s.sql_text) not like '%dbms_feature%'
  and s.sql_text not like '%SYS_DBA_SEGS%'
  and lower(s.sql_text) not like '%v$sys%'
  and lower(s.sql_text) not like '%dba_audit_trail%'
  and lower(s.sql_text) not like '%no_index(mytab)%';

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика