Re: db buffer cache advisory clarification

  • From: "jayaraj rengarajan" <jayaraj.rengarajan@xxxxxxxxx>
  • To: st.anderson@xxxxxxxxx
  • Date: Tue, 28 Mar 2006 14:02:50 -0500

Steve:

I am seeing it  other way around. PIO is getting reduced with increased
estimate size.  Below detail from a production statspack report.  We recycle
the DB during weekend..

Jay

Buffer Pool Advisory for DB: WDSP  Instance: WDSP  End Snap: 4040
-> 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             32    .1            3,970         33.20      1,601,419,883
D             64    .2            7,940         23.86      1,150,897,477
D             96    .3           11,910         15.87        765,448,716
D            128    .4           15,880          1.61         77,887,469
D            160    .5           19,850          1.33         63,917,781
D            192    .5           23,820          1.20         58,065,691
D            224    .6           27,790          1.13         54,488,453
D            256    .7           31,760          1.08         52,177,905
D            288    .8           35,730          1.05         50,519,436
D            320    .9           39,700          1.02         49,257,170
D            352   1.0           43,670          1.00         48,240,061
D            384   1.1           47,640          0.98         47,376,752
D            416   1.2           51,610          0.97         46,571,856
D            448   1.3           55,580          0.95         45,897,062
D            480   1.4           59,550          0.94         45,287,072
D            512   1.5           63,520          0.93         44,733,134
D            544   1.5           67,490          0.92         44,206,534
D            576   1.6           71,460          0.91         43,725,760
D            608   1.7           75,430          0.90         43,284,321
D            640   1.8           79,400          0.89         42,853,995
          -------------------------------------------------------------


On 3/28/06, Stephen Anderson <st.anderson@xxxxxxxxx> 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? I have looked around the web
> and have never seen an explanation for this.  I also looked on metalink to
> see if it was a know bug.
>
> This was from a lvl 5 statspack 15 minute snap on 9.2.0.3 on Sun Solaris.
> The instance has been up for over 8 months.  The results are the same no
> matter when I snap.
>
> Buffer Pool Advisory for DB: MERLIN  Instance: MERLIN  End Snap: 15
> -> 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         54,232,173
> D             32    .5            3,970          0.03         28,255,991
> D             48    .8            5,955          0.01          7,094,145
> D             64   1.0            7,940          1.00        937,436,311
> D             80   1.3            9,925          0.99        924,356,448
> D             96   1.5           11,910          0.97        912,150,561
> ...
> D            272   4.3           33,745          0.85        792,237,723
> D            288   4.5           35,730          0.84        783,014,854
> D            304   4.8           37,715          0.82        769,676,998
> D            320   5.0           39,700          0.79        738,539,663
>           -------------------------------------------------------------
>
> Regards,
> Steve Anderson
>

Other related posts: