SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor,ĪND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') Sampling is not used if the number of buffers in a buffer pool is small to begin with.Įxample 13-1 Querying the V$DB_CACHE_ADVICE View COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'ĬOLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'ĬOLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'ĬOLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
To reduce both the CPU and memory overhead associated with bookkeeping, Oracle Database uses sampling to gather cache advisory statistics. When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required. There is some overhead associated with using this advisory view. The benefit from increasing buffers decreases as the number of buffers increases. The benefit from increasing buffers from point A to point B is considerably higher than from point B to point C. The decrease in the physical I/O between points A and B and points B and C is not smooth, as indicated by the dotted line in the graph.
Figure 13-1 Physical I/O Ratio and Buffer Cache Sizeĭescription of "Figure 13-1 Physical I/O Ratio and Buffer Cache Size"Įxamining the example illustrated in the above figure leads to the following observations:Īs the number of buffers increases, the physical I/O ratio decreases.