SIMULATE BUFFER BUSY WAIT AND IDENTIFY HOT OBJECT

In my earlier post on Buffer Cache Wait Events, I had talked about buffer busy waits. 
 
Lets see a demonstration of how to simulate a Buffer Busy Wait scenario and how to find out object part of buffer busy wait.
Login to your database as sys user
SYS> create user test identified by test;
     grant dba to test;
     grant select on v_$session to test;
Now Connect to Test User and create a table with 10000 records .
SYS> conn test/test
TEST> create table t nologging
      as
      select rownum t1,rownum+1 t2
      from dual
      connect by level<=10000;
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.
TEST>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.
TEST>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 :  TEST
Session 3 :  TEST
Session 4 :  TEST
Session 5 :  TEST
Session 6 :  TEST
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 (SYS) monitor by following queries 
SYS> col event for a30
     select event,p1 file#,p2 block#
     from v$session_wait
     where sid in
         (select sid from v$session
          where username=’TEST’);
EVENT                               FILE#     BLOCK#
—————————— ———- ———-
log buffer space                        0          0
SQL*Net message from client    1650815232          1
buffer busy waits                      4     190828
buffer busy waits                       4     190828
enq: TX – row lock contention  1415053318     131096
SQL*Net message from client    1650815232          1
P1 stands for File id and P2 stands for Block id when you work with Buffer Busy Waits.
Now we have two methods to identify the object the wait belongs to :
1.Using the dump of the file/block
2. Using DBA_EXTENTS
Let’s use both of them :
1. Take a dump of the specified file/block
    Identify the seg/obj  in trace file . It is given in hexadecimal format in  trace file.
   Convert the hexadecimal no. to decimal no.
   Identify the object from dba_objects
- Take the dump of the specified file/block
SYS>alter system dump datafile 4 block 190828 ;
- Identify the tracefile of the session
SYS> col value for a70
          select  value from v$diag_info
          where name like ‘%Trace File%';
VALUE
———————————————————————-
/u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_ora_9088.trc
-    Identify the seg/obj  in trace file .
SYS>ho vi u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_ora_9088.trc
Object id on Block? Y
 seg/obj: 0x128cf csc: 0x00.fdd256  itc: 3  flg: E  typ: 1 – DATA
     brn: 0  bdba: 0x102e968 ver: 0x01 opc: 0
     inc: 0  exflg: 0
-  The object-id in hexadecimal format is 128cf
-   Convert the hexadecimal no. to decimal no. – It can be done using simple maths or by creating a function to do it.
     . Using mathematics
sql>select 15 + 12*16 + 8*16*16 + 2*16*16*16 + 1*16*16*16*16
    from dual;
15+12*16+8*16*16+2*16*16*16+1*16*16*16*16
—————————————–
                                    75983
SQL>  col owner for a30
      col object_name for a25
  
      select owner,object_name
      from dba_objects
      where data_object_id = 75983;
OWNER                          OBJECT_NAME
—————————— ——————–
TEST                           T
OR
     . Create a function to convert hexadecimal to decimal and then use the function to find 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;
/
SYS>  select owner,object_name
      from dba_objects
      where data_object_id=hex2dec(upper(‘128cf‘));
OWNER                          OBJECT_NAME
—————————— ——————–
TEST                           T
2. Use DBA_EXTENTS : Identify the object to which the specified  block belongs
SQL>col owner for a10
    col segment_name for a15
    select owner, segment_name
    from dba_extents
    where file_id = 4
      and 190828 between block_id and block_id+blocks-1;
OWNER      SEGMENT_NAME
———- —————
TEST       T

References:

http://www.oracledba.in/Articles/display_article.aspx?article_id=784

————————————————————————————————————

Related links: 

Home

Database Index

Tuning Index 
Buffer Cache Wait Events
Clustering Factor Demystified
Direct Read Enhancements in 11g

Oracle Checkpoints

 

 

———————–

Your comments and suggestions are welcome!