Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Tips

Analyzing real time I/O waits

The majority of activity in a database involves reading data.  Therefore the ability to analyze and correct Oracle Database physical read wait events is critical in any tuning project.  This type of tuning can have a huge, positive impact on performance.

 

Since it can show those wait events that are the primary bottleneck for the system, system wait tuning has become very popular.  Certain expert techniques like the 10046 wait event (level 8 and higher) analysis and Oracle MetaLink now have an analysis tool called trcanlzr.sql written by Carlos Sierra to interpret bottlenecks via 10046 trace dumps. Details are available in MetaLink note 224270.1.

 

In theory, any Oracle database will run faster if access to hardware resources associated with waits is increased.  It is critical to remember all Oracle databases experience wait events, and the presence of waits does not always indicate a problem.  In reality, every well tuned database experiences some bottleneck.  For example, a computationally intensive database may be CPU-bound and a data warehouse may be bound by disk-read waits.

 

While this section explores a small subset of wait analysis, it also illustrates the critical concept of Oracle tuning that every task waits on specific events.  It is the DBA’s job to find out whether the Oracle database is I/O bound, CPU bound, memory bound, or bound waiting on latches or locks.  Once the source of the bottleneck has been identified, ASH data can be used to determine the causes of these events and attempt to remove them.

 

The Oracle Database provides numerous views such as v$system_event and v$session_wait to give insight into the wait events and to aid in their identification.  The v$system_event dictionary views provides information regarding the total number of I/O-related waits within the Oracle database, but it does not identify the specific object involved.  In Oracle Release 2, the v$segment_statistics view gives this information.  The v$session_wait view offers detailed file and block data, from which the object can be extracted from the block number.

 

Oracle event waits occur quite swiftly, and it is difficult to get data unless the query is run at the exact moment the database is experiencing the wait.  For this reason, a method for using the v$session_wait view must be created so a sample of the transient physical I/O waits can be captured.

 

If the v$system_event view is used, there are over 300 specific wait events.  There are two critical I/O read waits within any Oracle database:

§       db file scattered read waits:  Scattered read waits happen whenever multiblock (full scan) I/O is invoked by an SQL statement.  When the Oracle Database performs a full-table scan or sort operation, multiblock block read is automatically invoked.

§       db file sequential read waits: A db file sequential read wait occurs within an Oracle database when a single block is requested, usually via index access. A single read is most commonly an index probe by rowid into an individual table or the access of an index block.  Sequential reads are single block reads, as opposed to multiblock (scattered) reads.

First, those objects that experience physical read waits and when they do so must be identified in order to tune these wait events.  The issue would then be addressed with tuning techniques.  The following section starts by studying the solutions, and then looks at ways to identify wait conditions.

 

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

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

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

Oracle® is the registered trademark of Oracle Corporation.