Table scan handling in Oracle 11g

posted May 8, 2012, 6:01 AM by Sachchida Ojha   [ updated May 8, 2012, 10:12 PM ]
Oracle treats blocks introduced from large table scans ( A large table scan is one that involves more blocks than the value of the hidden parameter "_small_table_threshold") differently from blocks introduces by index lookups.
   a.ksppinm  "Parameter",
   a.ksppdesc "Description",
   b.ksppstvl "Session Value",
   c.ksppstvl "Instance Value"
   x$ksppi a,
   x$ksppcv b,
   x$ksppsv c
   a.indx = b.indx
   a.indx = c.indx
   a.ksppinm LIKE '/_small%' escape '/'
The chance that blocks from large table scan will be accessed again are low, and there is a risk that by putting all the blocks from that table scan into cache that they will flush out blocks that are likely to be accessed again soon. Oracle tries to avoid filling the buffer cache with blocks from a large table scan.

Blocks from large table scans will generally not be included in the buffer cache at all (11g) or will be quickly flushed out of the buffer cache(10g).

To do this Oracle puts the block at the LRU end of the list so that they will be flushed almost immediately.

Using direct path  I/O which bypass the buffer cache. Blocks read in this way will not enter the buffer cache at all.

From the output of the left side of the query
Parameter     Description     Session Value   Instance Value
_small_table_threshold lower threshold level of table size for direct reads 117 117

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
(a.ksppity, 1,
'boolean', 2,
'string', 3,
'number', 4,
'file', a.ksppity) type,
a.ksppdesc description
from sys.x$ksppi a,
sys.x$ksppcv b
a.indx = b.indx
a.ksppinm like '\_%' escape '\'
order by