Identifying Blocking Locks/ Contention & Resolving Database Hanging Issue

One of my 10.2.0.1 database was hanging despite allocating ample opf resources. The CPU, Memory,Disk accesses were normal but under heavy load server would freeze for no valid reason. I had to raise TAR with Oracle.

My Statspack report had following details

—–Enqueue activity DB/Inst:
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
——————————————————————————
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
———— ———— ———– ———– ———— ————–
TX-Transaction (row lock contention)
6,611 6,615 0 6,615 32,521 4,916.24

Above list indicates oracle was waiting about 4 secs to get lock . Enqueue is a SGA memory structure used by Oracle.

Oracle support suggested to locate hot sql and hot block like table/index/cluster to reoslve this issue.

There are some articles on google but this PDF “Resolving_Oracle_Latch_Contention.pdf” from Guy Harrison at Quest is interesting …

Avoiding Hot Blocks
Cache buffers chains latch contention is one of the most intractable types of latch contention. There are a couple of things you can do at the application level to reduce the severity of this type of contention.
Firstly, identify the blocks that are hot.? Metalink note 163424.1, “How to Identify a Hot Block Within The Database”? describes how to do this. Having identified the identity of the hot block, you will most likely find that it is an index root or branch block.

It was suggested by Oracle support to look at Locks and provide them historical information about it. I couldn’t see any blokcing locks i TOAD/ deadlocks at alrtlog so I was surelocks are not the isse but I had to do what Oracle suypport suggested. So here is what I did.

Unix Shell script Process Stack diagnosis.
#!/usr/bin/ksh
# Create Oracle Trace
# Script 3 : Get Dump of Pmon, Smon & lgwr processes
# Pmon,Smon & Lgwr script files created in same directory
# Please use ./option3.sh <SID> for results
#################################################################################
export PID_PMON=$(ps -ef | grep ora_pmon_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
export PID_SMON=$(ps -ef | grep ora_smon_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
export PID_LGWR=$(ps -ef | grep ora_lgwr_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
echo $PID_PMON
echo $PID_SMON
echo $PID_LGWR
/usr/bin/pstack $PID_PMON > $1_PMON.log
/usr/bin/pstack $PID_SMON > $1_SMON.log
/usr/bin/pstack $PID_LGWR > $1_LGWR.log

# SQL script to carry system state dump
# Please execute this file by connecting to background sqlplus sessoion on live
# Once done please upload trace files created at $ORACLE_HOME/UDUMP to Oracle support
set term off;
set scan off;
set define off;
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
set term on;
set scan on;
set define on;

I have also created a small pl/sql procedure to locate blocking locks on system. This procedure will create a txt spool file with relevant details. Monitor these spool files over period of time to see which is a hot object.

CREATE OR REPLACE procedure locate_blocking_sid as
t_sid number;
t_sql_text varchar2(200);
msg varchar2(2000);
maxlinesize NUMBER := 32767;
log_file_handle UTL_FILE.file_type;
cursor c1 is
select sid, blocking_session,event, seconds_in_wait,wait_time
from sys.v_$session where state in (‘WAITING’)
and wait_class != ‘Idle’;
c1_rec c1%ROWTYPE;

cursor C2 (t_sid VARCHAR2, t_blocking_session varchar2) is
select ‘ ‘|| sql_text || sid
from sys.v_$session s, v$sql q
where sid in (t_sid,t_blocking_session)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

BEGIN
log_file_handle := UTL_FILE.fopen(‘/oracle/app/oracle/admin/startp/udump’,’blocking_locks ‘||to_char(sysdate,’DD-MM-YY HH24:MI’)||’.txt’,’w’,maxlinesize);
— log_file_handle := UTL_FILE.fopen(‘/oracle/app/oracle/admin/catp/udump’,’blocking_locks’,’w’);
UTL_FILE.PUT_LINE(log_file_handle, ‘ SQL Running & V$ Session ID ;’|| ‘ Event for Wait ;’||’ Seconds in Wait ;’ || ‘ Wait Time’);
open c1;
loop
FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;

BEGIN
open c2(c1_rec.sid,c1_rec.blocking_session);
Loop
FETCH c2 into msg;
msg := msg || ‘ ; ‘|| trim(c1_rec.event)|| ‘ ;’|| c1_rec.seconds_in_wait||’ ; ‘||c1_rec.wait_time;
EXIT WHEN c2%NOTFOUND;
UTL_FILE.PUT_LINE(log_file_handle,msg );
End Loop;
close c2;
END;
end loop;
close c1;
UTL_FILE.FCLOSE(log_file_handle);
end;
/