Miladin Modrakovic's Blog: Oraclue

There is small addition to oradebug unit_test command in 11g R2 .

oradebug unit_test per_session_find_one_waiter

This command has four mandatory arguments and one optional.

First parameter is find_waiters_for which can have two different values : current_sess or all_local_sess .Self descriptive.
Second parameter is wait_event.This is name of the wait event which search is done.Third and fourth parameters are related to time and rusults.

For my simple test I will test this command on event enq TX -row lock contention for my own session.

Session 1:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;

Session 2:

update test set a=11 where a=3;
update test set a=12 where a=4;

SQL> /

PID SPID                            SID USERNAME                          SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
22 1030                            190 SYS                                  1115     3889092034

Session 1 again:

SQL> /

PID SPID                    SID USERNAME                          SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
21 31360                   156 SYS                                 59123     3889092034

update test set a=25 where a=8;
update test set a=13 where a=3;

Now I got  event to test oradebug command:

oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1

Here is output:

WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 74 secs
WAITERS_FOUND_END

if I run same command again

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1

WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 221 secs
WAITERS_FOUND_END

wait time will increase.

Let’s increase waiter_min_secs_blkd=300 and run command:

SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1
WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 301 secs
WAITERS_FOUND_END

and I got output after aprox 301 secs..