Miladin Modrakovic’s Blog: Oraclue

The event db_open_begin delay (opening database with delay)  is undocumented event ( read unsupported ) and it will do exactly what is says e.g. open database with delay .Although alter database open command is executed it will wait for specified interval before opening database.

So here is scenario:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2973003776 bytes
Fixed Size                  2217064 bytes
Variable Size            2080377752 bytes
Database Buffers          872415232 bytes
Redo Buffers               17993728 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event db_open_begin delay=10
Statement processed.
SQL> set timing on
SQL> alter database open;

Database altered.

Elapsed: 00:00:11.51

Database was opened after approx 12 sec ( 10 sec delay and 1.51sec to run all oracle code related to opening database  starting with  bootstrap$  object. )

alter system command will work too:

SQL> alter system set events ‘db_open_begin delay =50′;

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