Question: How
do I use the _index_prefetch_factor hidden
parameter. Will _index_prefetch_factor Tips
increase the number of multi-block reads on index range
scans?
Answer:
The _index_prefetch_factor is an
undocumented parameter that defaults to a value of 100.
When setting _index_prefetch_factor to a value smaller than
100, it increases the propensity that Oracle will invoke
multi-block reads on an index range scan or index full scan.
The v$sysstat and v$sesstat and AWR report also provide
information on prefetch operations. Prefetch
operations in an AWR report include:
- index crx upgrade (prefetch)
-
physical reads cache prefetch
- physical
reads prefetch warmup
- prefetch warmup blocks aged out
- prefetched blocks aged out before
Also, the 10299 trace event measures prefetch operations.
Also see my related notes on
db_file_noncontig_mblock_read_count.
