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.
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
———————–