FAQ
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?

Search Discussions

  • Vlad Sadilovskiy at Nov 1, 2007 at 1:25 pm
    Do you have other tables assigned to KEEP buffer pool? Other tables might
    overflow into KEEP buffer pool if you just configured it. Do you have
    relevant AWR report to look at? I'd compare AWR reports befor and after the
    change and look for relevant clues.

    Vlad Sadilovskiy
    Oracle Database Tools
    Web site: http://www.fourthelephant.com
    Blog: http://vsadilovskiy.wordpress.com
    On 10/31/07, qihua wu wrote:

    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
  • Qihua wu at Nov 4, 2007 at 1:05 pm
    Hi,Mark,

    there are only two tables in the KEEP buffer.
    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
    *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
  • Jonathan Lewis at Nov 4, 2007 at 7:19 pm
    I think Mark's comments are probably in the right area.
    If you update the table in one session, then other sessions
    are going to start creating read-consistent clones. Do you
    have a recycle pool ? If not, then the clones will end up in
    the Keep pool.

    It's possible that if you generate too many clones of blocks
    1 to 100 (say) then the "originals" of some other blocks have
    to be kicked out of memory to make way for them - and then
    get read back later.

    You could check with a query like:

    select file#, block#, count(*)
    from v$bh
    where objd = {data object id of table}
    group by

    file#, block#
    having

    count(*) > 1
    /

    to see how many copies there are of each block.

    Or possibly:
    select
    ct, count(*)
    from
    (
    select file#, block#, count(*) ct
    from v$bh
    where objd = {data object id of table}
    group by
    file#, block#
    )
    group by ct
    /

    To get a complete distribution pattern of how heavily
    cloned the blocks are.

    The queries might be a little brutal on your buffer cache
    for several seconds, though.

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message ----- -
    a. block cleanouts could cause some blocks to be updated (I think
    that would be a worst case of doubling.)
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Nov 11, 2007 at 5:57 pm
    Sorry about the delay in replying, I've been a bit busy.
    And that's the
    point, if KEEP buffer is super large to contain all the blocks in the
    tables, the number of physical read should be equal to the blocks in
    the tables
    Not necessarily; that's the way you WANT it to be, not the way is has to be.

    It may be "obvious" that that's the way it ought to be - so the next thing
    to do is to find out why it doesn't seem to work that way.

    You've added the fact that you can see 'free' buffers in this pool.
    Did you check whether they were free buffers that had never been
    used, or were they free buffers that had once held blocks from the
    two tables ?

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com

    Author: Cost Based Oracle: Fundamentals
    http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html

    Original Message -----
    From: "qihua wu"
    To:
    Cc:
    Sent: Tuesday, November 06, 2007 11:55 PM
    Subject: Re: table with keep as buffer pool see much more physical reads than
    the number of blocks in the table
    Hi,

    From v$buffer_pool_statistics, I can see that the number of physical
    read is much more than the number of total blocks in the two
    tables(with Keep as default buffer, only two tables use keep), and
    from v$bh I can see that there are many buffer block with "free"
    status which means many buffers in KEEP are very used. And that's the
    point, if KEEP buffer is super large to contain all the blocks in the
    tables, the number of physical read should be equal to the blocks in
    the tables
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 1, '07 at 3:25a
activeNov 11, '07 at 5:57p
posts5
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase