Using the AWR History Tables to compare performance - Part 2

In the Part 1 of this post, we saw how we can use the AWR history tables to compare the top wait events between two different time periods.

We will now use the history tables to track and identify problem SQL statements.

Suppose we have been informed that there was a I/O performance issue early in the morning around 4.30 AM …

1) We obtain the SNAP_ID for the period in question

SQL> select snap_id,begin_interval_time,end_interval_time
2 from dba_hist_snapshot where to_char(begin_interval_time,’DD-MON-YYYY HH24:MI’)=’28-JUL-2009 04:00′;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
END_INTERVAL_TIME
---------------------------------------------------------------------------
     10951 28-JUL-09 04.00.08.054 AM
28-JUL-09 05.00.58.732 AM

2) We then obtain the Top 5 SQL statements executing during that period – we can sort on a number of variables 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.

SQL> select * from
2 (
select
3 4 sql.sql_id c1,
5 sql.buffer_gets_delta c2,
6 sql.disk_reads_delta c3,
7 sql.iowait_delta c4
8 from
9 dba_hist_sqlstat sql,
10 dba_hist_snapshot s
11 where
12 s.snap_id = sql.snap_id
13 and
14 s.snap_id=10951
15 order by
16 c3 desc)
17 where rownum < 6
18 ;

C1                    C2         C3         C4
------------- ---------- ---------- ----------
gyvak7ftvukcy    6131099     216469  613703400
7vk0vmj5hubqt   16027977     157384  484194526
0ay748ut6y71y     293922     142957  852668499
0utjstq2kntvx   13269295     100043  299328212
ahx483x819uu1   23584192      81361  797258856

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

SQL> select sql_text from dba_hist_sqltext
2 where sql_id=’gyvak7ftvukcy’;

SQL_TEXT
——————————————————————————–
select /*NORULE */ ‘DATAPOINT extents_left ‘ || ‘ ‘ ||
nvl(min(a.MAXEXTS – a.EXTENTS), 111) || CHR(10) ||
‘DATAPOINT extents_left_pct’ || ‘ ‘ ||
round(nvl(min(round(a.MAXEXTS – a.EXTENTS) * 100 / a.MAXEXTS), 100), 0) bpb
from (select ds.header_file file#,ds.header_block block#,
ds.extents,ds.max_extents maxexts,st.ts#,su.user#
from dba_segments ds,sys.ts$ st,sys.user$ su where
st.name=ds.tablespace_name and
su.name=ds.owner
and segment_type not in (‘SPACE HEADER’,’CACHE’ ) ) a,
……………..
…………………

4) We can then examine a single SQL statement and see if there are any performance trends. It shows that during the night between the hours of 1.00 AM and 3.00 AM, the I/O waits are the highest for this particular query and now we can then isolate the problem further by investigating into what other concurrent activities are happening during the same time interval.

1 select
2 s.snap_id,
3 to_char(s.begin_interval_time,’HH24:MI’) c1,
4 sql.executions_delta c2,
5 sql.buffer_gets_delta c3,
6 sql.disk_reads_delta c4,
7 sql.iowait_delta c5,
8 sql.apwait_delta c6,
9 sql.ccwait_delta c7,
10 sql.cpu_time_delta c8,
11 sql.elapsed_time_delta c9
12 from
13 dba_hist_sqlstat sql,
14 dba_hist_snapshot s
15 where
16 s.snap_id = sql.snap_id
17 and to_char(s.begin_interval_time,’DD-MON-YYYY’)=’28-JUL-2009′
18 and
19 sql.sql_id=’gyvak7ftvukcy’
20* order by 1
SQL> /

   SNAP_ID C1            C2         C3         C4         C5         C6         C7         C8         C9
---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     10947 00:00          2     294862     100954  580304536          0       7601   21400306  597529767
     10948 01:00          1     148163      50573 1284907667          0         39   10410248 1295096968
     10949 02:00          1     146961      71551 1313801418          0        177   11410211 1324273555
     10950 03:00          1     146961      71485 1249378673          0         74   11980186 1259394083
     10951 04:00          2     293922     142957  852668499          0          0   19720409  872476253
     10952 05:00          2     259310     115831  576725343          0         52   14970399  590388211
     10953 06:00          1     181574      77533  482981819          0          0   16200248  495323735
     10954 07:00          2     293922     142096  608989402          0         30   20550363  625696011
     10955 08:00          2     237469      94186  522814616          0          0   10070410  533824217
     10956 09:00          1     203949     127931 1022499631          0          0   15140178 1039516623
     10957 10:00          2     159078      87453  570435749          0          0    6830445  580726055
     10958 11:00          1     282066     134766 1071336554          0          0   15960187 1089728917
     10959 12:00          2     293922     142264  494751879          0         66   20480362  511719534
     10960 13:00          1     148228      69950  293789050          0          0    9280086  302702194
     10961 14:00          2     293922     141880  719430817          0         40   18620390  737121746

15 rows selected.