Re: db buffer cache advisory clarification

I have just tested "resetting" the v$db_cache_)advice view by changing the
parameter to ready and then on again. This "flushed" the advice and started
collecting again.  Now when I ran a report I get understandable figures. I
assume the problem must have been that the advice was being collected for so
long that there was some sort of discrepancy at one time in the system.

alter system set db_cache_advice=ready;
alter system set db_cache_advice=on;

Buffer Pool Advisory for DB: MERLIN  Instance: MERLIN  End Snap: 33
-> 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          1.14          1,445,606
D             32    .5            3,970          1.08          1,364,761
D             48    .8            5,955          1.03          1,302,580
D             64   1.0            7,940          1.00          1,268,680
D             80   1.3            9,925          0.98          1,249,249
D             96   1.5           11,910          0.97          1,233,174
....
D            304   4.8           37,715          0.93          1,177,106
D            320   5.0           39,700          0.92          1,171,614
I know this doesn't explain why I saw such bizarre data, but at least I am
seeing reasonable data now :-)
Thanks all for your input.


On 3/29/06, Stephen Anderson <st.anderson@xxxxxxxxx> wrote:
>
>  Hello,
>
> Ran a 7 minute snap this morning - results are getting worse!  Physical
> Reads are at 6278/s and Logical Reads are 70,844/s.
>
> Buffer Pool Advisory for DB: MERLIN  Instance: MERLIN  End Snap: 31
>
> -> 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.07        124,118,645
> D             32    .5            3,970          0.04         70,906,440
> D             48    .8            5,955          0.02         29,259,452
> D             64   1.0            7,940          1.00      1,783,067,416
> D             80   1.3            9,925          0.99      1,757,988,089
> ...
> D            256   4.0           31,760          0.85      1,523,232,446
> D            272   4.3           33,745          0.85      1,507,672,223
>
> Now we can see over 1 billion PIO's, so it cannot be a formatting thing.
> The metalink note that Jared suggested merely chose to format it
> differently.
>
>

Other related posts: