 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
|
|
 |
Donald K. Burleson Oracle Tips |
Time series I/O Wait Analysis
Every database will have "signatures," which are typically caused by regularly scheduled processing. When these signatures are identified, STATSPACK must be used to extract the SQL and ensure that it is properly optimized. When the detailed event waits data has been acquired, it is a trivial task to roll up the data and create trend reports.
If the read waits persist, the next step is to manipulate the schedule to execute the colliding SQL at different times. Some of the workload can be moved to a different time window if there is not sufficient I/O bandwidth to run the full workload all at once. To display a trend by day, a similar query may be run that will average the number of sequential read waits by day of the week.
More importantly, there is the detailed wait information in the dba_hist_waitstat table, so the exact table or index that is experiencing the real time wait can be investigated. By doing that in conjunction with AWR, the SQL may also be collected in the AWR table such that it is clear what SQL is precipitating the disk wait events.
Figure 13.9: Plotting real-time waits averages by hour of the day
Figure 13.9 shows a high number of real time db file sequential read waits between 2:00AM and 3:00AM with another spike between 9:00PM and midnight. This information can go to STATSPACK for use in extracting the SQL that was running during this period.
The DBA can also average the read waits by day of the week as shown in Figure 13.10. The figure shows that there is an obvious increase in scattered read waits every Tuesday and Thursday and the SQL can be extracted during these periods.
Figure 13.10: Plotting real-time waits averages by day of the week
Ordinarily, this insight would be ineffective because the source of the waits would not be obvious. Of course, with Oracle release 2 and beyond, the v$segment_statistics view can be used to see some of this information if the statistics_level parameter is set to a value of seven or higher.
Now, the DBA can drill in and see those specific table and indexes that were experiencing the sequential read waits.
Block
Segment Segment Wait
Date Hr. Name Type Count
-------------------- ------------------------------ ---------- ----
23-jan-2003 21 SYSPRD.S_COMM_REQ_SRC_U1 INDEX 23
23-jan-2003 21 SYSPRD.S_EVT_ACT TABLE 44
23-jan-2003 21 SYSPRD.S_EVT_ACT_F51 INDEX 16
23-jan-2003 22 SYSPRD.S_EVT_ACT TABLE 32
The specific object that experiences the physical read wait must be identified since the goal may be to distribute the object over additional disk spindles.
The details about the objects that experience physical read waits can be easily captured using a real time wait sampling method. Once they are recognized, STATSPACK can be used to find the problematic SQL and begin the tuning. The tuning of physical read waits involves SQL tuning, object striping across multiple disks, employing the KEEP pool for small objects, rescheduling the SQL to relieve the contention, or increasing the data buffer cache size.
A simple query can be used to plot the user I/O wait time statistic for each AWR snapshot. From phys_reads.sql, the physical read counts can be extracted from the AWR.
< phys_reads.sql
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
select
begin_interval_time,
filename,
phyrds
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
;
The output below shows a running total of Oracle physical reads. The snapshots are collected every half-hour. Starting from this script, a where clause criteria could easily be added to create a unique time series exception report.
SQL> @phys_reads
BEGIN_INTERVAL_TIME FILENAME PHYRDS
------------------------- --------------------------------------- ------------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 164,700
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 26,082
E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 472,008
E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF 1,794
E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA 2,123
The next step is to take a look at how these simple scripts can be enhanced to produce powerful exception reports.
In the simple report called hot_write_files.sql, the dba_hist_filestatxs is queried to identify hot write datafiles where the file consumed more than 25% of the total physical writes for the instance. The query compares the physical writes in the phywrts column of dba_hist_filestatxs with the instance -wide physical writes om statistic# = 55 from dba_hist_sysstat.
This simple yet powerful script allows the Oracle professional to track hot-write datafiles over time, thereby gaining important insights into the status of the I/O sub-system over time.
< hot_write_files.sql
prompt
prompt This will identify any single file who's write I/O
prompt is more than 25% of the total write I/O of the database.
prompt
set pages 999
break on snap_time skip 2
col filename format a40
col phywrts format 999,999,999
col snap_time format a20
select
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,
filename,
phywrts
from
dba_hist_filestatxs
natural join
dba_hist_snapshot
where
phywrts > 0
and
phywrts * 4 >
(
select
avg(value) all_phys_writes
from
dba_hist_sysstat
natural join
dba_hist_snapshot
where
stat_name = 'physical writes'
and
value > 0
)
order by
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi'),
phywrts desc
;
The following is sample output. This is a very useful report because the high write datafiles as well as those times when they are hot are revealed.
SQL> @hot_write_files
This will identify any single file who's write I/O
is more than 25% of the total write I/O of the database.
SNAP_TIME FILENAME PHYWRTS
-------------------- ---------------------------------------- ----------
2004-02-20 23:30 E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 85,540
2004-02-21 01:00 E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 88,843
2004-02-21 08:31 E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 89,463
2004-02-22 02:00 E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 90,168
2004-02-22 16:30 E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF 143,974
E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF 88,973
Time series exception reporting is extremely useful for detecting those times when an Oracle10g database is experiencing stress. Many Oracle professionals will schedule these types of exception reports for automatic e-mailing every day.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
|
Copyright © 1996 - 2011 by
Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.