Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter