Re: db buffer cache advisory clarification

On 23 Mar 2006 09:41:22 -0800, st.anderson_at_gmail.com wrote:

>Can anyone let me know why the advisory is saying i can so drastically
>reduce my PIO's by reducing my db_cache_size?
>
>This was from a statspack 15 minute snap on 9.2.0.3
>
>Buffer Pool Advisory for DB: MERLIN Instance: MERLIN End Snap: 24
>-> Only rows with estimated physical reads >0 are displayed
>-> ordered by Block Size, Buffers For Estimate
>
> Size for Size Buffers for Est Physical Estimated
>P Estimate (M) Factr Estimate Read Factor Physical Reads
>--- ------------ ----- ------------ ------------- ------------------
>D 16 .3 1,985 0.06 76,733,864
>D 32 .5 3,970 0.03 42,372,246
>D 48 .8 5,955 0.01 14,388,875
>D 64 1.0 7,940 1.00 1,235,406,218
>D 80 1.3 9,925 0.99 1,218,140,869
>D 96 1.5 11,910 0.97 1,202,045,787
>D 112 1.8 13,895 0.96 1,186,037,878
>D 128 2.0 15,880 0.95 1,170,539,124
>D 144 2.3 17,865 0.93 1,153,082,843
>D 160 2.5 19,850 0.92 1,136,840,124
>D 176 2.8 21,835 0.91 1,120,406,916
>D 192 3.0 23,820 0.89 1,105,315,871
>D 208 3.3 25,805 0.88 1,091,342,049
>D 224 3.5 27,790 0.87 1,078,433,638
>D 240 3.8 29,775 0.86 1,066,281,869
>D 256 4.0 31,760 0.85 1,055,056,082
>D 272 4.3 33,745 0.85 1,044,234,271
>D 288 4.5 35,730 0.84 1,032,099,896
>D 304 4.8 37,715 0.82 1,014,564,963
>D 320 5.0 39,700 0.79 973,654,538
> ---------------------------------------------------------

Very strange indeed. Time to visit Metalink to discover this is a bug in 9.2.0.3 and is resolved in 9.2.0.6. In 9.2.0.6 this doesn't happen.

--
Sybrand Bakker, Senior Oracle DBA