Performance failure for row lock contention in Oracle

Source: Internet
Author: User
Tags sessions

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.