Re: db buffer cache advisory clarification
- From: "Stephen Anderson" <st.anderson@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 29 Mar 2006 11:21:24 +0100
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:
