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/