Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Buffer busy wait in Statspack report Oracle

Buffer busy wait in Statspack report Oracle

ERROR
On fetching the statspack report from Oracle Database, We are getting the Buffer busy wait on top:

                                                                  Avg
                                                      Total Wait  wait  Waits
Event                               Waits   Timeouts   Time (cs)  (ms)   /txn
---------------------------- ------------ ---------- ----------- ----- ------
buffer busy waits                 225,559    211,961  24,377,029  1081    4.0 
enqueue                            25,731     21,756   6,786,722  2638    0.5
Parallel Query Idle Wait - S        9,980      7,929   1,762,606  1766    0.2
SQL*Net message from dblink       435,845          0   1,288,965    30    7.7
db file parallel write              4,252          0   1,287,023  3027    0.1
write complete waits                5,872      5,658     581,066   990    0.1
db file sequential read         1,249,981          0     510,793     4   22.0 

On Checking the buffer busy waits, we find data block are on top events:

                                                                              
                                 Tot Wait    Avg                              
Class                    Waits  Time (cs) Time (cs)                           
------------------ ----------- ---------- ---------                           
data block             216,577 ##########       108  <== 
undo header              5,072    609,734       120                           
undo block               3,770    333,876        89                           
free list                   70     17,426       249                           
segment header               8         34         4         

IDENTIFIED THE CAUSE

Find the blocks id which object causing the problems:

SELECT count(*) NUM_WAITERS, p1 FILE#, p2 BLK#, p3 CLASS
FROM v$session_wait
WHERE event = 'buffer busy waits'
GROUP BY p1, p2, p3

NUM_WAITERS    FILE#   BLK#     CLASS
------------   -----  -------  ------
92             2      13487      1016
73             2      27762      1016
32             1      29697      1016

Find the Segment name and type which causing problem:

SELECT owner,segment_name,segment_type
FROM dba_extents
WHERE file_id=&file
AND &blockid BETWEEN block_id AND block_id + blocks

NUM_WAITERS   OWNER.SEGMENT_NAME       TYPE     FILE#    BLK#      CLASS
------------  -------------------    ---------  ----   -------    ------
92             SYSTEM.DEF$_TRANORDER   INDEX     2      13487      1016
73             SYSTEM.DEF$_AQCALL      TABLE     2      27762      1016
32             SYSTEM.DEF$_TRANORDER   INDEX     1      29697      1016

Solution:
Following are the possible solution in buffer cache wait:

1. You should rebuild the index with increase PCTFREE/FREELIST to reduce the contention.
2. You can put this object in keep buffer if its more frequently used and size is small.
3. Check the SQL Queries associated with object which causing contention. Tune the SQL Queries.
4. Increase the Buffer pool size if you can by checking the buffer advisory.

Advertisements