Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

 Adaptive path read operation tips

Oracle Database Tips by Donald BurlesonApril 24, 2015

Question:  What is the adaptive path direct read feature of Oracle?  Can you give an example of adaptive direct path read operations?

Answer:  Release 11.2.0.1 adds a new feature "adaptive direct path reads". NOTE: The internet is full of complaints about poor performance of this new feature (high direct path read waits and high physical disk reads), so consider disabling this feature after workload testing before moving 11g release 2 into production.

The adaptive direct path reads" value is based on number of blocks in a table/index and adaptive direct path read operations are enabled by default in 11gr2 and beyond.

If you have recently migrated to 11g release 2 and are seeing excessive I/O, also see my important notes in 11g release 2 upgrade gotchas.

It is controlled by the hidden parameters: _adaptive_direct_read and _small_table_threshold. There is also a _serial_direct_read parameter which forces the full scan on the big table to be a direct read

The adaptive direct path reads acts like the sequential pre-fetch feature of DB2, whereby if Oracle detects a lot of data being scanned from the same cylinders, it automatically, switches from loading the data blocks into the SGA, and instead, loads the data buffer blocks into the PGA, where they are private to the executing task.

The adaptive direct path read is the same as using Oracle parallel query, because the data blocks bypass the SGA and are read into the PGA. 

There are several compelling benefits to using the adaptive direct path read operations:

  • The SGA does not become "clogged with data block that are unlikely to ne needed by other sessions.
  • In general, a direct path read operation is faster than a scattered read (full-table scan read) because the SGA is bypassed.

Disabling adaptive direct path reads

The only major downsides to adaptive direct read operations is that a system which re-scans the same data over-and-over does not gain any benefit from data buffer caching because the PGA is private to each session.  If you are re-reading these full-scan operations, this will manifest itself in a huge spike in physical disk reads.  If you see this you will want to disable adaptive direct path read operations with this command:  See MOSC  note 793845.1 titled "High 'direct path read' waits in 11g".

alter system set events '10949 trace name context forever';

Also, if you are already fully-caching a table with the KEEP pool, then the adaptive direct path read operations will be detrimental to overall system I/O because the data blocks will not reside in the SGA.

Another drawback is with Exadata servers, which are loaded with solid-state disk in the SGA (flash cache technology).  On an Exadata server, the speed of a read into the SGA is close to the logical I/O speed of adaptive direct path read operations.  In these cases, you may wan to alter the hidden parameters to turn off adaptive direct path reads.

   
Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training
 
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster