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

 

 

   
  Oracle Tips by Burleson

Finding the source of bandwidth bottlenecks

The first step in applying SSD to RAC is to locate the root cause of the I/O contention.  The most common cause of I/O bandwidth saturation in Oracle RAC is a poorly performing I/O subsystem. However, more detailed exploration is warranted to see what specific data files contribute to the saturation of the storage.  The source of Oracle I/O saturationmay be due to one of these causes:

  • Non-database processes read from the same device as Oracle data files

  • Another database sharing the same file systems (Oracle RAC)

  • A poorly tuned I/O subsystem (e.g. RAID5 for high-update data files).

There are two approaches to I/O monitoring Oracle RAC for disk bandwidth bottlenecks:

Monitor enqueues at the disk level – Use OS tools, such as iostatof vendor-based disk monitors.

Monitor at the Oracle instance level – Use an AWRor STATSPACK report to monitor buffer busy waitsand high access times for each instance.

As a review, the hallmark feature of RAC is the ability for many Oracle instances to simultaneously read the Oracle files (Figure 2.6).  This complicates the process of locating high concurrent access data files because the file I/O on each of the RAC instances must be interrogated.

Because Oracle file I/O is measured at the instance level, finding bandwidth bottlenecks may mean reading AWRreports on dozens of RAC nodes.  To simplify the process of identification, most Oracle professionals will measure I/O at the disk level using native I/O monitors such as SAR and iostat.  They would then look for disk enqueues, a condition where read/write requests are waiting for access to the disk.

In the example below, a SAR command was issued to locate the disk enqueues:

root > sar –d –f /var/adm/sa/sa16

In the SAR output, review the avque column seeking high device backlogs:

SunOS prod1 5.6 Generic_105181-23 sun4u    05/16/01  

01:00:00 device        %busy   avque   r+w/s  blks/s  avwait  avserv
         sd22            100    72.4    2100    2971     0.0    87.0
         sd23              0     0.0       0       0     0.0     0.0          sd24              0     0.0       0       0     0.0     0.0          sd25            100    72.4    2100    2971     0.0    87.0

Because of the transient nature of disk enqueues, many Oracle professionals also use disk vendor specific time-series tools (e.g. the EMC Symmetrics console) to track disk enqueues over time.

Another approach to locating bandwidth bottlenecks in Oracle RAC is to interrogate each instance for buffer busy waits.  The next section examines this closer.


The above book excerpt is from:

Oracle RAC & Tuning with Solid State Disk

Expert Secrets for High Performance Clustered Grid Computing

ISBN 0-9761573-5-7

Donald K. Burleson & Mike Ault

http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm

  
 

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. 

Hit Counter