direct path read/write(lob)
These occur when database processes perform special types of multiblock I/Os
between the disk and process PGA memory, thus bypassing the Buffer Cache.
Such I/Os may be performed both synchronously and asynchronously.
Examples where they may be used are:
o Sort I/Os when memory Sort areas are exhausted and temporary tablespaces
are used to perform the sort
o Parallel Execution (Query and DML)
o Readahead operations (buffer prefetching)
o Direct Load operations
o I/O to LOB segments (which are not cached in the Buffer Cache)
Due to the way in which time for these waits is recorded (it does not measure
the time taken to perform the I/O), their relative position in listings such
as Statspack’s “Top 5 Wait/Timed Events” cannot be used to evaluate their
true impact.
Guidelines for tuning:
o Usage of Asynchronous I/O is recommended where available.
o In Oracle8i, minimize the number of I/O requests by setting the
DB_FILE_DIRECT_IO_COUNT instance parameter so that
DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system
In Oracle8i the default for this is 64 blocks.
(In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
will be sized down if the max_io_size of the system is smaller.)
Note 47324.1 Init.ora Parameter “DB_FILE_DIRECT_IO_COUNT” Reference Note
o Tune memory Sort areas so that disk I/O for Sorting is minimized:
In 9i and above use Automated SQL Execution Memory Management.
In 8i tune the various Sort areas manually.
Note 147806.1 Oracle9i New Feature: Automated SQL Execution Memory Management
Note 109907.1 How to Determine an Optimal SORT_AREA_SIZE
o For LOB segments, store them on filesystems where an Operating System File
Buffer Cache can provide some memory caching.
o Identify sessions performing direct I/Os by querying V$SESSION_EVENT
for these Wait Events or V$SESSTAT for statistics
‘physical reads direct’, ‘physical reads direct (lob)’,
‘physical writes direct’ & ‘physical writes direct (lob)’
and tune their SQL statements.
o Identify datafiles on bottlenecked disk storage and move elsewhere
using V$FILESTAT or Statspack’s “File IO Statistics” section.
