buffer busy waits
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: buffer busy waits

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I use spotlight to monitor the database, I see buffer busy waits a lot of times and I want to find out which table it is occurring in. But I am not able to see any buffer busy waits in v$session_wait (to get the hot block). It appears in v$system_event and v$session_event but never in v$session_wait. How do I find out which is the block for which there are large number of waits.

    Thanx
    KN

  2. #2
    Join Date
    Jan 2002
    Posts
    48

    Smile

    Spotlight is not able to give you this information in a simple way, you need StorageXpert or a similar tool for this that will go in deep (very deep) and point out hotspots. I recommend a visit to the site http://www.ixora.com.au/scripts/cache.htm where you can find scripts that might help you, I use alot of these scripts when handling buffer busy waits and such things.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    From Anjo Kolk

    Code:
    buffer busy waits
    This event is caused by:
    •multiple sessions requesting the same block (i.e., one or more sessions are waiting
    for a process to read the requested block into the buffer cache)
    •multiple sessions waiting for a change to complete for the same block (only one
    process at a time can write to the block, so other processes have to wait for that
    buffer to become available)
    If buffer busy waits is high, determine which blocks are being accessed concurrently and if
    the blocks are being read or changed through V$SESSION_WAIT and V$WAITSTAT.
    V$SESSION_WAIT will show the file#, block# and id (where id represents the status of
    the buffer busy wait event).
    •file# - data file number containing the block being read
    •block# - block number being waited on
    •id - buffer busy wait event:
    •1013/1014 - block is being read by another session
    •1012/1016 - block is being modified
    V$WAITSTAT will show the block classes and the number of times waited for each.
    Different actions may be taken for each block class to alleviate contention. Tuning
    priorities should be oriented toward the classes that contribute the highest wait time
    percentage.
    
    
    Identifying block waits by file
    X$KCBFWAIT shows a count of buffer busy waits per file. The indx column represents the
    file id number - 1. So this view can be queried to determine which file has a high number
    of buffer busy waits.
    select indx+1 fileno, count, time
    from x$kcbfwait
    where time != 0 or count > 0
    order by time;
    If the file with highest wait time is known, find the objects that belong to that file:
    select file_id, segment_name, segment_type, freelists,
    freelist_groups, pctfree, pctused
    from dba_extents
    where file_id = ;

  4. #4
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    When I run the query below to find out for what file/tablespace the buffer busy waits is occurring for,

    select
    d.tablespace_name,
    sum(x.count) total_waits,
    sum(x.time) time_waited
    from
    sys.x_$kcbfwait x,
    sys.dba_data_files d
    where
    x.inst_id = userenv('Instance') and
    x.count > 0 and
    d.file_id = x.indx + 1
    group by
    d.tablespace_name
    order by 3 desc
    --
    SYSTEM 52964 219146
    RBS 2644 1346
    DATA 14 18
    INDEXES 1 0


    I see that the system tablespace has the highest waits, what does this mean, I thought buffer busy waits occur on waits for blocks/freelists for inserts??

    Thanx in advance.
    KN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width