Oracle : buffer busy wait

 If CLASS=

1. data block
   • IF OTYPE =
   • INDEX , then the insert index leaf block is probably hot, solutions are
     • Hash partition the index
     • Use reverse key index
   • TABLE, then insert block is hot,solutions
     • Use free lists
     • Put Object in ASSM tablespace
2. Segment header – If “segment header” occurs at the same time as CLASS= “data block” on the same object and the object is of OTYPE= “TABLE”  then this is just a confirmation that the TABLE needs to use free lists or  ASSM.
3. File Header Block – Most likely extent allocation problems, look at extent size on tablespace and increase the extent size to there are few extent allocations and less contention on the File Header Block.
4. free lists – Add free list groups to the object
5. undo header – Not enough UNDO segments, if using old RBS then switch to AUM
6. undo block – Hot spot in UNDO, application issue

set linesize 120

col block_type for a20

col objn for a25

col otype for a15

col filen for 9999

col blockn for 9999999

col obj for a20

col tbs for a10

select

       bbw.cnt,

       bbw.obj,

       bbw.otype,

       bbw.sql_id,

       bbw.block_type,

       nvl(tbs.name,to_char(bbw.p1)) TBS,

       tbs_defs.assm ASSM

from (

    select

       count(*) cnt,

       nvl(object_name,CURRENT_OBJ#) obj,

       o.object_type otype,

       ash.SQL_ID sql_id,

       nvl(w.class,'usn '||to_char(ceil((ash.p3-18)/2))||' '||

                    decode(mod(ash.p3,2),

                         1,'header',

                         0,'block')) block_type,

       --nvl(w.class,to_char(ash.p3)) block_type,

       ash.p1 p1

    from v$active_session_history ash,

        ( select rownum class#, class from v$waitstat ) w,

        all_objects o

    where event='buffer busy waits'

      and w.class#(+)=ash.p3

      and o.object_id (+)= ash.CURRENT_OBJ#

      and ash.session_state='WAITING'

      and ash.sample_time > sysdate - &minutes/(60*24)

      --and w.class# > 18

   group by o.object_name, ash.current_obj#, o.object_type,

         ash.sql_id, w.class, ash.p3, ash.p1

  ) bbw,

    (select   file_id, 

       tablespace_name name

  from dba_data_files

   ) tbs,

    (select

 tablespace_name    NAME,

        extent_management  LOCAL,

        allocation_type    EXTENTS,

        segment_space_management ASSM,

        initial_extent

     from dba_tablespaces 

   ) tbs_defs

  where tbs.file_id(+) = bbw.p1

    and tbs.name=tbs_defs.name

Order by bbw.cnt

/

  CNT OBJ     OTYPE   SQL_ID        BLOCK_TYPE       TBS        ASSM

----- ------- ------- ------------- ---------------- ---------- ------

    3 TOTO1   TABLE   8gz51m9hg5yuf segment header   NO_ASSM    MANUAL

   59 TOTO1   TABLE   8gz51m9hg5yuf data block       NO_ASSM    MANUAL

On  7.0  – 8.1.5 see http://sites.google.com/site/embtdbo/oracle-buffer-busy-wait/oracle-buffer-busy-wait-7-8-1-5

On version 8 and 9, the p3 value has a different meaning. Instead  of meaning the block type (which is the best thing to know) it means the kind of buffer busy wait. There are only two values that matter to us, values in

100 range = read waits (basically just an IO wait)

Reader blocking Reader, ie one reader is reading a block in and another person wants to read this block and waits on a buffer busy wait p3=130.

200 range = write contetion (same as in 10g)

And see what kind of buffer busy waits there are and what the objects are:

No ASH ?

---

If you don’t have ASH data you will have to do some guess work.

The first step in finding out the source of buffer busy waits is looking at

     V$waitstats

This will tell us what kind of datablocks we have contention on.

Above is a diagram shows some of the essential parts of Oracle in regards to performance tuning.

In the machine memory are

•     Oracle’s SGA, or System Global Area, a memory that is shared between Oracle users
•     LGWR – log writer process
•     DBWR – database writer process
•     User1,2,3 … – user processes, in this case “shadow processes”

On the machine file system are

• Redo log files
• Data files

The SGA is composed of (among other things)

• Log Buffer
• Library Cache
• Buffer Cache

In order to access a block, a user (shadow process) has to get a latch (cache buffer chains latch) which protects buckets or linked lists of buffer headers. Once the header desired if found the latch is released. The buffer headers point to the actual data block in memory. Before modifying a block in memory a user has to lock the buffer header. The buffer header is locked any time a modification is made whether it is reading a block into memory or modifying a block that is already in memory. Usually the header is locked only for a brief amount of time but when there is a lot of concurrent access the buffer header can become a bottleneck.

by adding free lists or moving the table to an ASSM tablespace we will alleviate the bottleneck.

Multiple free lists:

The inserts would look something like this (somewhat exaggerated drawing)

BBW on index (because of insert)

Reverse Key Index

OBJN        OTYPE  FILEN  BLOCKN SQL_ID        BLOCK_TYPE

----------- ------ ------ ------ ------------- ------------

54962 TOTO1 TABLE     16   45012 8gz51m9hg5yuf data block 

54962 TOTO1 TABLE     16     161 8gz51m9hg5yuf segment header

0                     14       9 8gz51m9hg5yuf  87

0                     14       9 8gz51m9hg5yuf  87

IF the block is of class > 18, the there will be no object name, so we have to look it up ourselves to be sure: