Oracle Technology Sharing!!!: Buffer Busy Wait scenario and how to find out object part of buffer busy wait

(Source:www.oracledba.in: Author: Ritesh Singh)

Hi

I have been asked many time about buffer busy wait event, how to identify the bottleneck and objects being involved into it.

Well let me explain you with a simulated example.

In Oracle, when a particular block is being used by multiple process at the same time, the block is treated as Hot, the ITL Entry

for the block has to maintain the list of transaction in the block header. Contention for the blocks leads to Enqueues / ITL Waits

latches and Buffer Busy waits.

If Session 1 tries to access a block and till the time read is not completed in the buffer, the block would have a latch acquired, at the

same time if there would be another session trying to access the same blocks, one have to wait for the latch availability and then the

read to the buffer be completed by other session.

There is no fixup resolution to avoid buffer busy wait, rather than to reduce the contention in the blocks and allow quick read to the blocks

by session.

If you are seeing lots of buffer busy waits, its better to tune queries by reducing the number of blocks it accesses. A block may be visited

unnecessarily many times. Spread data across blocks if its a contention in the indexes by using Reverse key indexes.

Lets see a demonstration of how to simulate a Buffer Busy Wait scenario and how to find out object part of buffer busy wait.

SQL> create user ritesh identified by pass;

User created.

SQL> grant dba to ritesh;

Grant succeeded.

SQL> grant select on v_$session to ritesh;

Grant succeeded.

Now Connect to Ritesh User and create a table with 10000 records and check the number of blocks in the table.

SQL> conn ritesh/pass
Connected.
SQL>
SQL> create table t nologging as select rownum t1,rownum+1 t2 from dual connect by level<=10000;

Table created.

SQL> select blocks from user_segments where segment_name='T';

    BLOCKS
----------
        24

The Next thing would be to create a package which will allow me to access the same set of blocks from multiple session at the

same time.

CREATE OR REPLACE PACKAGE GEN_BUF_BUSY 
authid current_user
AS
TYPE t_RefCur IS REF CURSOR;
FUNCTION RetDSQL(p_sql IN VARCHAR2) RETURN t_RefCur;
PROCEDURE RUNSELECT;
PROCEDURE RUNUPDATE1;
PROCEDURE RUNUPDATE2;
procedure kill_session(p_username varchar2);
END GEN_BUF_BUSY;
/

Package would have one select procedure and two update procedure updaing different columns of the table at the same row.

CREATE OR REPLACE PACKAGE BODY GEN_BUF_BUSY AS
FUNCTION RetDSQL(p_sql IN VARCHAR2) RETURN t_RefCur
as
v_RetCur t_RefCur;
BEGIN
OPEN v_RetCur FOR p_sql;
RETURN v_RetCur;
end RETDSQL;
PROCEDURE RUNSELECT
as
cursor dummy is select * from t;
c1 t_refcur;
rec dummy%rowtype;
begin


for i in 1..1000000 
loop
c1:=retDSQL('select * from t');
loop
fetch c1 into rec;
exit when c1%notfound;
end loop;
close c1;
end loop;
end RUNSELECT;
PROCEDURE RUNUPDATE1
as
BEGIN

for i in 1..1000000
loop
update t set t1=rownum;
commit;
end loop;

END RUNUPDATE1;
PROCEDURE RUNUPDATE2
as
BEGIN

for i in 1..1000000
loop
update t set t2=rownum;
commit;
end loop;

END RUNUPDATE2;

procedure kill_session(p_username varchar2)
as
cursor c1 is select sid,serial# from sys.v_$session where username=p_username;
rec c1%rowtype;
begin
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
execute immediate 'alter system kill session '''||rec.sid||','||rec.serial#||'''';
end loop;
end;
end GEN_BUF_BUSY;
/

Now open Six sessions

Session 1 : SYS User

Session 2 : RITESH

Session 3 : RITESH

Session 4 : RITESH

Session 5 : RITESH

Session 6 : RITESH

and run the following procedures

exec GEN_BUF_BUSY.runUPDATE1; -- Run on SESSION 2
exec GEN_BUF_BUSY.runUPDATE2; -- Run on SESSION 3
exec GEN_BUF_BUSY.runSELECT;  -- Run on SESSION 4
exec GEN_BUF_BUSY.runSELECT;  -- Run on SESSION 5
exec GEN_BUF_BUSY.runSELECT;  -- Run on SESSION 6

From session one monitor by following queries

SQL> select event,p1,p2,p3 from v$session_wait where sid in
  2  (select sid from v$session where username='RITESH');

EVENT                                                                    P1         P2         P3
---------------------------------------------------------------- ---------- ---------- ----------
buffer busy waits                                                         4      16234          1
buffer busy waits                                                         4      16227          1
log buffer space                                                          0          0          0
latch: cache buffers chains                                      8.7934E+12        150          0
enq: TX - row lock contention                                    1415053318     524312       2368

P1 stands for File id and P2 stands for Block id when you work with Buffer Busy Waits, Remeber each wait has differente meaning for

P1 and P2.

Now run the following command to identify the object the wait belongs to

SQL> alter system dump datafile 4 block 16227;

System altered.

This will generate a trace file at user_dump_dest, please find your spid to identify the trace file

SQL> select spid from v$process
  2  where addr=
  3   (select paddr from v$session
  4    where sid=(select distinct sid from v$mystat))
  5
SQL> /

SPID
------------------------
3968

Go to User_Dump_dest and see the trace file, open it in notepad and identify the content with seg/obj#

Block header dump:  0x01003f63
 Object id on Block? Y
 seg/obj: 0x10fc6  csc: 0x00.7456c0  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1003f60 ver: 0x01 opc: 0
     inc: 0  exflg: 0

use the seg/obj# from the trace file and run the following command to identify the object.

  CREATE OR REPLACE FUNCTION HEX2DEC (hexnum IN CHAR) RETURN NUMBER IS
  i                 NUMBER;
  digits            NUMBER;
  result            NUMBER := 0;
  current_digit     CHAR(1);
  current_digit_dec NUMBER;
BEGIN
  digits := LENGTH(hexnum);
  FOR i IN 1..digits LOOP
     current_digit := SUBSTR(hexnum, i, 1);
     IF current_digit IN ('A','B','C','D','E','F') THEN
        current_digit_dec := ASCII(current_digit) - ASCII('A') + 10;
     ELSE
        current_digit_dec := TO_NUMBER(current_digit);
     END IF;
     result := (result * 16) + current_digit_dec;
  END LOOP;
  RETURN result;
END hex2dec;

You can also use calculator to convert hex to decimal

then use this

SQL>  select owner,object_name from dba_objects where data_object_id=hex2dec(upper('10fc6'));

OWNER                          OBJECT_NAME
------------------------------ ---------------------------------------------------------------------
RITESH                         T

This is one of the way to identify the block contending for Buffer busy waits, In the next articles i would demonstrate on how to troubleshoot

or reduce the buffer busy waits on a database.

Please check the video for practical demonstration of the written article.