This discussion is archived
7 Replies Latest reply: Dec 18, 2012 11:02 AM by MaJo RSS

Buffer busy wait, 1st level bmp

MaJo Newbie
Currently Being Moderated
Hi All !

OS: Linux redhat 5
DB: 11gr2
Block size: 8K

In an application we use I can see high buffer busy waits over a various periods.

I collect some info during this event.

SQL_HASH_VALUE FILE# BLOCK# REASON
-------------- ---------- ---------- ----------
769132182 6 17512 8
3983195767 6 17512 8
769132182 6 17512 8
3240261994 6 17512 8
3240261994 6 17512 8
3240261994 6 17512 8
769132182 6 17512 8
... I have total 35 sessions

File6 / block 17512 =

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
------------------------------ ------------------ ------------------------------ ----------------------------
GBSLOB LOBSEGMENT GBSASP SYS_LOB0000017961C00006$$


The sql are both inserts and updates to the same large table, blobs are involved (insert/update)
blobs using securefile

AWR reports this for a short period

Buffer busy waits is the top wait event

Buffer Wait Statistics DB/Inst: GGBISP01/ggbisp01 Snaps: 20925-20926
-> ordered by wait time desc, waits desc

Class Waits Total Wait Time (s) Avg Time (ms)
------------------ ----------- ------------------- --------------
1st level bmb 574,636 17,118 30
free list 20,538 70 3
undo header 41,150 7 0
data block 263 1 3
undo block 18 0 0
-------------------------------------------------------------

I'm trying to find more details about this wait event, I believe it is related to usage of ASSM.
Can anyone explain more when 1st level bmp is seen ?



Thank you !

Best regards
Magnus Johansson
  • 1. Re: Buffer busy wait, 1st level bmp
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    MaJo wrote:
    SQL_HASH_VALUE      FILE#     BLOCK#     REASON
    -------------- ---------- ---------- ----------
    769132182          6      17512          8
    3983195767          6      17512          8
    769132182          6      17512          8
    3240261994          6      17512          8
    3240261994          6      17512          8
    3240261994          6      17512          8
    769132182          6      17512          8
    ... I have total 35 sessions
    
    File6 / block 17512 =
    
    TABLESPACE_NAME                SEGMENT_TYPE       OWNER                          SEGMENT_NAME
    ------------------------------ ------------------ ------------------------------ ----------------------------
    GBSLOB                         LOBSEGMENT         GBSASP                         SYS_LOB0000017961C00006$$
    The sql are both inserts and updates to the same large table, blobs are involved (insert/update)
    blobs using securefile

    AWR reports this for a short period

    Buffer busy waits is the top wait event
    Buffer Wait Statistics          DB/Inst: GGBISP01/ggbisp01  Snaps: 20925-20926
    -> ordered by wait time desc, waits desc
    
    Class                    Waits Total Wait Time (s)  Avg Time (ms)
    ------------------ ----------- ------------------- --------------
    1st level bmb          574,636              17,118             30
    free list               20,538                  70              3
    undo header             41,150                   7              0
    data block                 263                   1              3
    undo block                  18                   0              0
    -------------------------------------------------------------
    I'm trying to find more details about this wait event, I believe it is related to usage of ASSM.
    Can anyone explain more when 1st level bmp is seen ?
    Your AWR shows an interesting mix of ASSM and freelist group blocks - are you running ASSM ?

    1st level bitmap blocks (bmb) are the blocks in a segment (usually the first one or two of each extent) that show the availability of free space in the other data blocks. Each bitmap block can identify up to 256 other blocks (the last time I checked), although you have to have a fairly large data segment before you reach this level of mapping.

    If you have a high rate of concurrent inserts and updates on a LOB column then you may be running into code that frequently updates bitmap blocks to show that data blocks have changed from empty to full. It's also possible that you've run into one of the many bugs that appeared when you mixed ASSM with LOB segments - you haven't given the exact version of 11.2, but you might want to check the latest versions and any bug reports for patches to your version.

    Regards
    Jonathan Lewis
  • 2. Re: Buffer busy wait, 1st level bmp
    MaJo Newbie
    Currently Being Moderated
    Thanks a lot for your answer Jonathan,

    It is a ASSM managed tablespace, and it is configured with uniform extent of 4M. we run version 11.2.0.2. I have checked the 11.2.0.3 bug fix list but can not see any
    obvious bug listed there for assm and lobs. Not meaning they wont exist. We are planning to upgrade the QA database to 11.2.0.3 during Christmas.

    This is an edi application that seems to store the various states of the job process in a blob, that's the information I got so far from the vendor.
    I have asked the vendor to explain in detail how this is suppose to work and what information is stored in the blob.
    To me it seems a bit like a design issue, but I hope the explanation from the vendor will help me understand better what is going on.

    Best regards
    Magnus Johansson
  • 3. Re: Buffer busy wait, 1st level bmp
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    MaJo wrote:

    It is a ASSM managed tablespace, and it is configured with uniform extent of 4M. we run version 11.2.0.2. I have checked the 11.2.0.3 bug fix list but can not see any
    obvious bug listed there for assm and lobs. Not meaning they wont exist. We are planning to upgrade the QA database to 11.2.0.3 during Christmas.

    This is an edi application that seems to store the various states of the job process in a blob, that's the information I got so far from the vendor.
    I have asked the vendor to explain in detail how this is suppose to work and what information is stored in the blob.
    To me it seems a bit like a design issue, but I hope the explanation from the vendor will help me understand better what is going on.
    It sounds like the sort of thing where I'd like to chat with the vendor for half an hour to see what's going on before committing myself to further comment - but if you would like a bit of guesswork, I'd say that your comment about design was probably correct. Two things spring to mind - how many concurrent tasks are there running job processes, and how many steps are there to a job process. Imagine a process is:
    action 1
    create lob
    action 2
    update lob
    action 3
    update lob
    ...
    action 99
    update lob
    The way that you specify read consistency for LOBs means that you would probably leave behind a trail of 99 copies of "the same" lob in the lob segment, and constantly be allocating new blocks in the segment for the new copies. In theory I would expect this to stabilise eventually as you created enough "old" data in the LOB segment, though. You would also have an interesting impact on the space holding the LOBINDEX, because each new lob would result in a delete from a very recent index block, and inserts into the "high value" points of the index (and, eventually, deletes from the low-end of the "old version" part of the index. The index activity might cause some BBW on 1st level BMBs as the index initiallly grew, but I would expect it to cause more BBWs on blocks of type data reflecting something similar to the the normal type of index contention you get on sequence-based FIFO indexes.

    (The number of concurrent processes would, of course, increase the rate of block allocation and the level of contention).

    Regards
    Jonathan Lewis
  • 4. Re: Buffer busy wait, 1st level bmp
    MaJo Newbie
    Currently Being Moderated
    I'm still waiting for details from the vendor, but I'm afraid you are right about the process steps.

    I'm also a bit concerned about the blob storage, the lob segment is huge, listed as 382G.

    I also noticed what you wrote about the copies of the blob that comes from an update.
    "The way that you specify read consistency for LOBs means that you would probably leave behind a trail of 99 copies of "the same" lob in the lob segment, and constantly be allocating new blocks in the segment for the new copies"

    I did some researched on that when using 10g and I hoped that this behavior had changed in 11g.
    But from your comment I understand it isn't the case and that is making the scenario with this application even worse.

    Appreciate your comments !

    Regards
    Magnus Johansson
  • 5. Re: Buffer busy wait, 1st level bmp
    Another_user Explorer
    Currently Being Moderated
    That is a pretty solid analysis from JL with the information provided.

    Just curious, with all those updates, do you cache the lob segment itself?
  • 6. Re: Buffer busy wait, 1st level bmp
    MaJo Newbie
    Currently Being Moderated
    Hi !
    Yes the feedback from Jonathan is great and very useful.

    I have discussed with the the vendor and he confirms the scenario where the blob is updated through the process
    Each process has various amounts of steps it goes through, but for each step the blob is updated with new data.
    Also each step involves a commit.

    I have read another thread in this forum related to similar problems were Jonathan also answered (search for buffer busy waits securefiles)
    There Jonathan says that "blobs are not oltp objects but the application is handling lobs as oltp objects"

    I think we can say the same about this application.

    The blobs are not cached. The extents are also uniform 4Mb.

    I'm not sure how to address this, I'll need to do some more homework about lobs. :)
    This application has just started and I expect the load to increase a lot, but now I have my doubts how this application would cope with increased load.

    Thanks
    Magnus Johansson
  • 7. Re: Buffer busy wait, 1st level bmp
    MaJo Newbie
    Currently Being Moderated
    Hi !
    We have a case with Oracle Support and we got the information that this is related to
    Bug 13775960 "enqueue hash chains" latch contention for delete/insert Securefile workload (Hidden)

    We are planning to upgrade to 11.2.0.3.4 + one-off patch 13775960

    Thanks
    Magnus Johansson

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points