Whenever a server process is looking for some data blocks and does not find them in buffer cache, it will read them from disk into memory. Blocks can be read into :
- Buffer cache (buffered read) in which case
. those blocks can be accessed by other server processes also
. Some earlier blocks in buffer cache may need to be aged out to make space for new blocks
- PGA of the server process (direct read) in which case
- those blocks can not be accessed by other server processes
- no blocks in buffer cache may need to be aged out to make space for new block Hence, direct reads may be used when
- the data blocks being read will not be needed by any other process
- we don’t want a large no. of blocks in buffer cache to be aged out
10g
– all the parallel queries resulted in direct reads irrespective of the size of the data as parallel queries are normally used in data warehouse where amount of data read is large.
– all the serial queries were executed using buffered reads assuming that amount of data read is not large.
1. Parallel queries fetching even small amount of data bypassed the buffer cache so that if any other server process needed to access the same data, he had to access the disk.
2. A serial query fetching massive amount of data could age out a lot of data from buffer cache .
11g we can modify this behavior using some undocumented parameters.
1. we can set a threshold on the no. of blocks read (_small_table_threshold) so that even parallel queries fetching data less than the threshold will perform bufered I/O so that the fetched data in buffer cache can be accessed by other server processes also.
2. For serial queries we can enable direct I/O (_serial_direct_read) for all full table scans irrespective of the amount of the data read so that Full Table Scans (FTS’s) read the data into PGA and data in buffer cache is not aged out.
