Direct read in oracle 10G & 11G

Oracle Direct Reads: 10g vs. 11g

In Oracle Database, when a server process needs data blocks that are not found in the database buffer cache (SGA), it reads them from disk into memory. How these blocks are read into memory can significantly impact performance and buffer cache utilization.

Blocks can be read into:

• Buffer Cache (Buffered Read):

  • These blocks are placed in the Shared Global Area (SGA) buffer cache.

  • They can be accessed and shared by other server processes, promoting data reuse and reducing physical I/O.

  • However, if the buffer cache is full, some older blocks may need to be aged out to make space for new blocks, potentially causing useful data to be evicted.

• PGA of the Server Process (Direct Read):

  • These blocks are read directly into the Program Global Area (PGA) of the individual server process, bypassing the SGA buffer cache.

  • The blocks are private to that server process and cannot be accessed by other server processes from memory.

  • No blocks in the SGA buffer cache need to be aged out to make space for these new blocks.

Direct reads are typically used when:

• The data blocks being read are unlikely to be needed by any other process (e.g., large scans).

• There's a desire to avoid aging out a large number of potentially useful blocks from the shared buffer cache.

Direct Read Behavior in Oracle 10g

In Oracle Database 10g, the behavior of direct reads was largely determined by the type of query:

• Parallel Queries: All parallel queries, regardless of the amount of data being read, resulted in direct reads. This was based on the assumption that parallel queries are typically used in data warehousing environments where the amount of data read is large, and the data is unlikely to be reused by other sessions immediately.

• Serial Queries: All serial queries were generally executed using buffered reads, assuming that the amount of data read would not be excessively large.

Limitations in 10g:

1. Inefficient Parallel Queries: Parallel queries fetching even a small amount of data would bypass the buffer cache. This meant that if any other server process subsequently needed to access the same data, it would have to perform another physical I/O from disk, even if the data was just read by a parallel process.

2. Buffer Cache Flooding by Serial Queries: A serial query fetching a massive amount of data (e.g., a full table scan on a very large table) could flood the buffer cache, aging out a lot of valuable data that might be frequently accessed by other sessions.

Direct Read Behavior in Oracle 11g

Oracle 11g introduced more flexibility and control over direct read behavior, primarily through undocumented parameters, to address the limitations observed in 10g.

1. Controlling Parallel Query Behavior (_small_table_threshold):

   • You can set a threshold on the number of blocks read (_small_table_threshold).

   • If a parallel query fetches data less than this threshold, it will perform buffered I/O instead of direct I/O. This allows the fetched data to reside in the buffer cache, making it accessible to other server processes and promoting reuse.

2. Enabling Direct I/O for Serial Queries (_serial_direct_read):

   • For serial queries, you can enable direct I/O for all full table scans, irrespective of the amount of data read.

   • This is controlled by the _serial_direct_read parameter. Setting this to TRUE (or a non-default value) will cause Full Table Scans (FTSs) to read data directly into the PGA.

   • This prevents large serial scans from aging out a significant amount of data from the SGA buffer cache, preserving valuable cached blocks.

Important Note: Parameters prefixed with an underscore (_) are undocumented and should only be used under the explicit guidance of Oracle Support. Their behavior can change in future releases or patches, and improper use can lead to unexpected performance issues or instability.