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: Wed, 29 Mar 2006 11:21:24 +0100
Message-ID: <a9a8b6c0603290221q3def680dh40b16928f003f922@mail.gmail.com>


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_at_gmail.com> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 29 2006 - 04:21:24 CST

Original text of this message

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