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.
