Friday, October 7, 2011

Finding Problematic SQL and Top waits from past using AWR tables


Let us assume that we found that a SQL scrip executed some time in past  between lets say 6 and 9 AM resulted in  performance issues. So we can find out what caused it by using the process below.


In this case we are comparing performance of a particular database on the Oct 6th. 


First get the Snaps for a particular time. Since snaps are collect hourly by default.



select snap_id,to_char(BEGIN_INTERVAL_TIME,'DD-MON-YY HH24:MI:SS') "Runtime"
from dba_hist_snapshot
where to_char(BEGIN_INTERVAL_TIME,'DD-MON-YY')='06-OCT-11' order by snap_id;




Now to see the Top 10 waits experience during that time, use the script below and pass on the 'begin' and 'end' Snap IDs when asked by the query as a bind variable




select * from
(select event, waits “Waits”, time “Wait Time (s)”, pct*100 “Percent of Total”, waitclass “Wait Class”
from (select e.event_name event, e.total_waits – nvl(b.total_waits,0) waits,
(e.time_waited_micro – nvl(b.time_waited_micro,0))/1000000 time
, (e.time_waited_micro – nvl(b.time_waited_micro,0))/(select sum(e1.time_waited_micro – nvl(b1.time_waited_micro,0))
from dba_hist_system_event b1 , dba_hist_system_event e1
where b1.snap_id(+) = b.snap_id and e1.snap_id = e.snap_id and b1.dbid(+) = b.dbid
and e1.dbid = e.dbid and b1.instance_number(+) = b.instance_number
and e1.instance_number = e.instance_number
and b1.event_id(+) = e1.event_id
and e1.total_waits > nvl(b1.total_waits,0)
and e1.wait_class <> ‘Idle’
) pct
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> ‘Idle’
order by waits desc
)
where rownum < 11);


we are filtering the rows to display only the Top 10 Wait Events



We then obtain the Top 5 SQL statements executing during that period by using the script below. This script can be sorted. like disk reads, buffer gets, I/O waits, Cpu time etc. We will sort it by disk reads to find the top 5 SQL statements with the most disk reads.
Provide the Snap ID to the query when asked.


select * from
(select sql.sql_id c1, sql.buffer_gets_delta c2, sql.disk_reads_delta c3, sql.iowait_delta c4
from dba_hist_sqlstat sql, dba_hist_snapshot s
where s.snap_id = sql.snap_id  and s.snap_id=&SnapID
order by c3 desc)
where rownum < 6
                                 






Now that we have the SQL ID, we will obtain the SQL Query for that SQL ID

select sql_text from dba_hist_sqltext
where sql_id='&SQL_ID';

2 comments:

  1. I found it Helpful.

    ReplyDelete
  2. getting error

    SQL> select * from
    (select event, waits "Waits", time "Wait Time (s)", pct*100 "Percent of Total", waitclass "Wait Class"
    2 3 from (select e.event_name event, e.total_waits - nvl(b.total_waits,0) waits,
    4 (e.time_waited_micro – nvl(b.time_waited_micro,0))/1000000 time
    , (e.time_waited_micro – nvl(b.time_waited_micro,0))/(select sum(e1.time_waited_micro – nvl(b1.time_waited_micro,0))
    5 6 from dba_hist_system_event b1 , dba_hist_system_event e1
    where b1.snap_id(+) = b.snap_id and e1.snap_id = e.snap_id and b1.dbid(+) = b.dbid
    7 8 and e1.dbid = e.dbid and b1.instance_number(+) = b.instance_number
    and e1.instance_number = e.instance_number
    9 10 and b1.event_id(+) = e1.event_id
    and e1.total_waits > nvl(b1.total_waits,0)
    11 12 and e1.wait_class <> 'Idle'
    ) pct
    13 14 , e.wait_class waitclass
    from
    15 16 dba_hist_system_event b ,
    dba_hist_system_event e
    17 18 where b.snap_id(+) = 924
    and e.snap_id = 934
    19 20 and b.event_id(+) = e.event_id
    and e.total_waits > nvl(b.total_waits,0)
    21 22 and e.wait_class <> 'Idle'
    order by waits desc
    23 24 )
    25 where rownum < 11);
    (e.time_waited_micro – nvl(b.time_waited_micro,0))/1000000 time
    *
    ERROR at line 4:
    ORA-00907: missing right parenthesis

    please help me.

    ReplyDelete