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

Solutions to Physical Read Waits

Once the objects that experience the physical read waits have been identified, STATSPACK can be used to extract the SQL associated with the waits.  The following actions can then be taken to correct the problem.  These corrective actions are presented in the order in which they are most likely to be effective.  Some may not apply to a particular environment: 

Tune the SQL statement: Tuning the SQL is the single most important factor in reducing disk I/O contention.  If an SQL statement can be tuned to reduce disk I/O by using an index to remove an unnecessary large-table full-table scan, the amount of disk I/O and associated waits are dramatically reduced. Other SQL tuning might include:

Change table join order: For sequential read waits, the SQL may be tuned to change the order that the tables are joined, often using the ORDERED hint.

Change indexes: The SQL can be tuned by adding function-based indexes or using an INDEX hint to make the SQL less I/O-intensive by using a more selective index.

Change table join methods: Often, nested loop joins have fewer I/O waits than hash joins, especially for sequential reads. Table join methods can be changed with SQL hints (use_nl, for example). Prior to Oracle with pga_aggregate_target, the propensity for hash join must be changed by adjusting the hash_area_size parameter.

The database can also be tuned at the instance level with these techniques:

Get better CBO statistics: Stale or non-representative statistics can cause suboptimal SQL execution plans, resulting in unnecessary disk waits. The solution is to use the dbms_stats package to analyze the schema. Also, it should be noted if column data values are skewed, the addition of histograms may also be necessary.

Distribute disk I/O across more spindles: Disk channel contention is often responsible for physical read waits, and they will show up in the ASH data.  If the system experiences disk waits as a result of hardware contention and RAID is not in use, the DBA may consider segregating the table of index into a separate tablespace with many data files and striping the offending data file across multiple disk spindles by reorganizing the object and using the minextents and next parameters.

Use the KEEP pool:  Many experts recommend implementing the KEEP pool for reducing scattered reads. In the Oracle Magazine article Advanced Tuning with STATSPACK (Jan/Feb. 2003), the author notes that small table full-table scans should be placed in the KEEP pool to reduce scattered read waits.

Increase the db_cache_size: - The more data blocks in the RAM buffer, the smaller the probability of physical read wait events.

The dba_hist_sqltext table keeps a record of historical SQL source statements, and it is easy to extract the SQL that was executing at the time of the read waits.  From there, the execution plans for the SQL statements can be gathered.  The DBA can then verify they are using an optimal execution plan.

 

The next section will explore how the data needed to fix the causes of the physical read waits can be acquired now that the solutions have been identified.

 

 

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.