However, increasing the cache size to 334MB may potentially decrease reads by a factor of. Hence, it is not advisable to decrease the cache size to 212MB.
In this example, the output shows that if the cache was 212 MB-instead of the current size of 304 MB-the estimated number of physical reads would increase by a factor of 1.74, or 74%. The output of this query might look like the following: Estd Phys Estd PhysĬache Size (MB) Buffers Read Factor Readsģ0 3,802 18.70 192,317,943 10% of Current SizeĦ08 76,040. 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.
0 kommentar(er)
