Performance failure for row lock contention in Oracle

This is a set of Windows RAC environments, as well as an environment that previously dealt with a performance failure caused by a row cache lock. Here is a record of the process:

1 for the one-hour AWR collection and analysis, first of all, from the report header to see DB time nearly 500 minutes, (db time)/elapsed=8, this ratio is high:

2 See the Top 5 event again:

The first thing to see is the Enq:tx–row lock contention event, which means that the system has a more serious row-level lock wait event in the one hours.

Top 5 Timed Events

Typically, there are several possible reasons for the Enq:tx–row lock contention event:

Different sessions update or delete the same record;

A unique index has a duplicate index;

The bitmap index is either updated or concurrently inserts the same field value into the bitmap index field;

Concurrent update operations on the data on the same block;

Wait for the index block to complete splitting;

Also, from the section's statistics section, see the following information:

% of Capture shows% of row lock waits for per top segment compared

With Total row lock waits to all segments captured by the Snapshot

See row lock waits occur on an index.

3 So what exactly is the operation that caused this enq:tx–row lock contention to wait for the event? See what sessions are currently generating enq:tx–row lock contention waiting events in the system?

View Sourceprint?

Sql> Select Event,sid,p1,p2,p3 from v$session_wait where event= ' enq:tx-row lock contention ';

02

The EVENT SID P1 P2 P3

--------------------------------------------------------------------------------------------------------

Enq:tx-row Lock contention 224 1415053316 1441815 144197

Enq:tx-row Lock contention 238 1415053316 1441815 144197

Modified Enq:tx-row lock contention 247 1415053316 1441815 144197

Enq:tx-row lock contention 248 1415053316 1441815 144197

Enq:tx-row lock contention 253 1415053316 1441815 144197

Ten sql>

A session with a SID of 224,238,247,248,253 produces a enq:tx–row lock contention wait event.

4 View the current session in the system, on which object produced the Enq:tx–row lock contention wait event?

View Sourceprint?

Sql> Select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where event= ' Enq:tx-row lo CK contention ';

02

row_wait_obj# row_wait_file# row_wait_block# row_wait_row#

-------------------------------------------------------

05 369195 0 0 0

06 369195 0 0 0

07 369195 0 0 0

08 369195 0 0 0

09 369195 0 0 0

10 369195 0 0 0

11

6 Rows selected

Sql>

5 What is the object of 369195 for this database object?

View Sourceprint?

Sql> Select object_name,object_id from dba_objects where object_id=369195;

02

object_name object_id

---------------------------------------------

369195 BIND_PROCESS_LOG_REFNO

Sql> Select owner,object_name,object_id,data_object_id, object_type from dba_objects where Object_name= ' BIND_ Process_log_refno ';

More Wonderful content: http://www.bianceng.cn/database/Oracle/