Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: db buffer cache advisory clarification

Re: db buffer cache advisory clarification

From: Stephen Anderson <st.anderson_at_gmail.com>
Date: Tue, 28 Mar 2006 20:48:57 +0100
Message-ID: <a9a8b6c0603281148j467d452bu50e98adb4590d066@mail.gmail.com>


Hi Jay,

This is 'normal expected' behaviour. I would always assume that as the pool grows larger we can store more blocks in the buffer, thereby negating many PIO's. My problem is in understanding how a reduction in the buffer pool would provide an opportunity to *reduce* PIO's by over 99%.

On 3/28/06, jayaraj rengarajan <jayaraj.rengarajan_at_gmail.com> wrote:
>
> 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_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? 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
> >
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 28 2006 - 13:48:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US