find query with performance degradation running more than 5 sec

find query with performance degradation running more than 5 sec :

select * from (
select sql_id, execs, before_avg_etime, after_avg_etime, norm_stddev,
       case when to_number(before_avg_etime) < to_number(after_avg_etime) then 'Slower' else 'Faster' end result
from (
select sql_id, sum(execs) execs, sum(before_execs) before_execs, sum(after_execs) after_execs,
       sum(before_avg_etime) before_avg_etime, sum(after_avg_etime) after_avg_etime,
       min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev,
       case when sum(before_avg_etime) > sum(after_avg_etime) then 'Slower' else 'Faster' end better_or_worse
from (
select sql_id,
       period_flag,
       execs,
       avg_etime,
       stddev_etime,
       case when period_flag = 'Before' then execs else 0 end before_execs,
       case when period_flag = 'Before' then avg_etime else 0 end before_avg_etime,
       case when period_flag = 'After' then execs else 0 end after_execs,
       case when period_flag = 'After' then avg_etime else 0 end after_avg_etime
from (
select sql_id, period_flag, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, period_flag, sum(execs) execs, sum(etime)/sum(decode(execs,0,1,execs)) avg_etime from (
select s.sql_id, 'Before' period_flag,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta)/1000000 etime
-- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
and ss.begin_interval_time <= sysdate-1
union
select s.sql_id, 'After' period_flag,
nvl(s.executions_delta,0) execs,
(s.elapsed_time_delta)/1000000 etime
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS ,dba_hist_sqltext st
where ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.executions_delta > 0
and s.elapsed_time_delta > 0
   and st.sql_id=s.sql_id
   and st.sql_text not like '/* SQL Analyze%'
and ss.begin_interval_time > sysdate-1
)
group by sql_id, period_flag
)
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('1'),2)
and max_etime > nvl(to_number('5'),.1)
)
where result = 'Slower'
order by norm_stddev;

original by Kerry Osborne

2 комментария к “find query with performance degradation running more than 5 sec”

Slow running queries can be a result of missing indexes, poor execution plans, bad application and schema design, etc.

Ответить

with stats as (
select /*+ materialize */
 * from (
select  t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  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 > 5
group by  sql_id, plan_hash_value
 )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 1=1
  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)%';

Ответить

2 комментария к “find query with performance degradation running more than 5 sec”

Оставьте комментарий Отменить ответ