Question: What is the adaptive
path direct read feature of Oracle? Can you give an
example of adaptive direct path read operations?
18.104.22.168 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.
controlled by the hidden parameters:
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
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
793845.1 titled "High 'direct path read' waits in
alter system set events '10949 trace name
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
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
Burleson is the American Team
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
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
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
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
is the registered trademark of Oracle Corporation.