| |
 |
|
Oracle Tips by Burleson |
Locating Slow Disk with ASH
A snapshot of Oracle wait events can be obtained every hour with the
Oracle 10g Active Session History (ASH) tables. This information
can be used to plot changes in wait behavior over time. Thresholds
can also be set so that reporting is done only on wait events that
exceed those pre-defined thresholds. Here is the script that is
commonly used for exception reporting of wait events.
* ash_event_rollup.sql
-- *************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact rtp AT rampant.cc
-- *************************************************
ttitle 'High waits on events|Rollup by hour'
column mydate heading 'Yr. Mo Dy Hr' format a13;
column event format a30;
column total_waits heading 'tot waits' format 999,999;
column time_waited heading 'time wait' format 999,999;
column total_timeouts heading 'timeouts' format 9,999;
break on to_char(snap_time,'yyyy-mm-dd') skip 1;
select
to_char(e.sample_time,'yyyy-mm-dd HH24') mydate,
e.event,
count(e.event) total_waits,
sum(e.time_waited) time_waited
from
v$active_session_history e
where
e.event not like '%timer'
and
e.event not like '%message%'
and
e.event not like '%slave wait%'
having
count(e.event) > 100
group by
to_char(e.sample_time,'yyyy-mm-dd HH24'),
e.event
order by 1
;
The output from this script is show below. A time-series report is
the result, showing those days and hours when the thresholds are
exceeded. Notice where every evening between 10:00 PM and 11:00 PM
high I/O waits on the Oracle redo logs are being experienced.
Wed Aug 21 page 1
High waits on events
Rollup by hour
Yr. Mo Dy Hr EVENT tot waits time wait
------------- ------------------------------ --------- ---------
2002-08-18 22 LGWR wait for redo copy 9,326 1,109
2002-08-18 23 LGWR wait for redo copy 8,506 316
2002-08-18 23 buffer busy waits 214 21,388
2002-08-19 00 LGWR wait for redo copy 498 5
2002-08-19 01 LGWR wait for redo copy 497 15
2002-08-19 22 LGWR wait for redo copy 9,207 1,433
2002-08-19 22 buffer busy waits 529 53,412
2002-08-19 23 LGWR wait for redo copy 9,066 367
2002-08-19 23 buffer busy waits 250 24,479
2002-08-20 00 LGWR wait for redo copy 771 16
2002-08-20 22 LGWR wait for redo copy 8,030 2,013
2002-08-20 22 buffer busy waits 356 35,583
2002-08-20 23 LGWR wait for redo copy 8,021 579
2002-08-20 23 buffer busy waits 441 44,677
2002-08-21 00 LGWR wait for redo copy 1,013 26
2002-08-21 00 rdbms ipc reply 160 30,986
2002-08-21 01 LGWR wait for redo copy 541 17
AWR can also be used to drill-down and see the specific tables and
indexes that are experiencing slow I/O. The following
wait_time_detail.sql script compares the wait event values from
dba_hist_waitstat and dba_hist_active_sess_history. The exact
objects that are experiencing wait events can be identified.
The above book excerpt is from:
Oracle RAC
& Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing
ISBN:
0-9761573-5-7
Mike Ault, Donald K. Burleson
http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm |
