Lot of TX row lock contention due to concurrent DMLs is fired to update/delete the same row.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:    

HS:BLOCKING LOCKS OCCURING REGULARLY

EXCLUSIVE LOCKS CAUSING END USERS EXTREME SLOWNESS 

 Lot of TX row lock contention due to concurrent DMLs is fired to update/delete the same row.

Its an application design issue and developers need to serialize the DMLs.

Log files:

HANG ANALYSIS:
==============
Found 33 objects waiting for <cnode/sid/sess_srno/proc_ptr/ospid/wait_event>
<0/1462/179/0x379b5d18/11831/db file sequential read>
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/1462/179/0x379b5d18/11831/db file sequential read>
-- <0/1455/232/0x379b74e8/15866/enq: TX - row lock contention>
-- <0/1415/293/0x379c2388/13384/enq: TX - row lock contention>
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/1512/3/0x379a96a8/16310/enq: TX - row lock contention>
-- <0/1420/188/0x389cc450/7249/enq: TX - row lock contention>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/1541/16079/0x389aa890/25934/enq: TX - row lock contention>
-- <0/1419/5/0x389c2d80/1758/enq: TX - row lock contention>

Solution:

Take the systemstate dump and hanganalyze traces covering the period of locks.

Using SQL*Plus connect as SYSDBA using the following command:
sqlplus " / as sysdba"
Do this 2 times in 2 separate windows, creating 2 sqlplus sessions (SQL1 and SQL2)
In SQL1 gather the hanganalyze by executing the following:
SQL1> oradebug setmypid 
SQL1> oradebug unlimit;
SQL1> oradebug hanganalyze 3

In SQL2 gather the systemstates by executing the following:
SQL2> oradebug setmypid
SQL2> oradebug unlimit;
SQL2> oradebug dump systemstate 266
SQL2> exit

Gather a second hang analyze having waited at least 1 minute to give time to identify process state changes. In SQL1 execute the following:
SQL1> oradebug hanganalyze 3

In SQL2 execute the following to collect a second systemstate dump:
SQL2> oradebug setmypid
SQL2> oradebug unlimit;
SQL2> oradebug dump systemstate 266
SQL2> exit

 Lot of TX row lock contention due to concurrent DMLs is fired to update/delete the same row.

Its an application design issue and developers need to serialize the DMLs.

Get Oracle Certifications for all Exams
Free Online Exams.com