Re: table with keep as buffer pool see much more physical reads than the number of blocks in the table

Hi,Mark,

1. there are only two tables in the KEEP buffer.
2. Each if there are block cleanouts(or update for the table), there is no read from the disk(only write the data from buffer to disk), that means number of "physical read" should not increaes for update or blcok cleanouts

For KEEP BUFFER, should the block be read three times before keep inside the KEEP buffer without aging out? Isn't it kept in the buffer ONCE it is read? Thanks,
Qihua

On 11/1/07, Mark W. Farnham < mwf_at_rsiz.com> wrote:
>
> 1) What else is in the KEEP buffer?
>
> 2) Do you only read this table?
>
> a. block cleanouts could cause some blocks to be updated (I think
> that would be a worst case of doubling.)
>
> b. if you're updating this table (or other tables sharing its
> cluster if clustered) then you are making the blocks candidates to be
> written out, and/or creating cached block clones serving read consistency,
> so at some point you might be reading those again, though sourced from
> rollback (UNDO)).
>
>
>
> If you're not updating this table at all, then table scanning it three
> times before your batch job might work some magic. (It is not really magic.
> It covers the delayed block cleanout and relatively heats your blocks for
> this table, so it will fare marginally better getting cleared out for other
> KEEPed (KEPT?) tables from pushing it out competitively.)
>
>
>
>
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> *On Behalf Of *qihua wu
> *Sent:* Wednesday, October 31, 2007 11:26 PM
> *To:* oracle-l
> *Subject:* table with keep as buffer pool see much more physical reads
> than the number of blocks in the table
>
>
>
> Hi, everyone, the oracle I use is 10.2.0.2 on HPUX IA64
>
> Our job ran very slow and from AWR report I can see that a table named ATTRIBUTE_VALUE
> consumes most of the physical IO (Physical read was more than 2.5M), as I
> know the blocks the table has is only 0.3M(block size is 8K), so I created
> a keep buffer and set the buffer pool of ATTRIBUTE_VALUE to "keep buffer".
> I was doing this to hope that once ATTRIBUTE_VALUE is read into the keep
> buffer, it will never be paged out so there the physical read will not
> exceed 0.3M (the blocks the table has). I already set the size of the keep
> buffer large enough (4G) to hold all the blocks.
>
> But after that change, ATTRIBUTE_VALUE was still read physically much more
> times than 0.3M (about 1.6M). Shouldn't it be only 0.3M if the table was
> "kept" in the buffer?
>

--
http://www.freelists.org/webpage/oracle-l