How to diagnose high-level contention (enq: HW - contention)

2011-03-29  来源:本站原创  分类:Database  人气:101 

Referred to above the high water level to promote the case, when the concurrent sessions at the same time insert, can easily cause a high level contention enq: HW - contention, then the occurrence of such contention, the how to diagnose it?
View v $ session_wait, should have the following wait events:

SQL> select event, p1, p2, p3 from v $ session_wait;
EVENT P1 P2 P3
---------------------- -------- ------- ----------
enq: HW - contention 1213661190 4 17005691

P3 can be converted by DBMS_UTILITY informed contention file and block
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (17005691) FILE #,
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (17005691) BLOCK #
3 from dual;

FILE # BLOCK #
---------- ----------
4228475

And then by file # and block # locate objects

SQL> select owner, segment_type, segment_name
2 from dba_extents
3 where file_id = 4
4 and 228475 between block_id and block_id + blocks - 1;

OWNER SEGMENT_TYPE SEGMENT_NAME
--------------- --------------- -------------------- ----------
SCOTT LOBSEGMENT EMP_DATA_LOB

We know enqueue lock p2, p3 value v $ lock of id1, id2 the same value, also by id2, also happen to know the file and block contention
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (ID2) FILE #,
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (ID2) BLOCK #
3 from v $ lock
4 where type = 'HW';
FILE # BLOCK #
---------- ----------
4228475

P1 values ​​can know by the type and mode locking

SQL> select chr (bitand (1213661190, -16777216) / 16777215) | |
2 chr (bitand (1213661190,16711680) / 65535) "Lock", to_char (bitand (1213661190, 65535)) "Mode" from dual;
Lock Mode
---------- ----------
HW 6

When known, lob object contention occurs when high water level, how to do it? metalink (740075.1) provides us with several solutions, for reference only

Quote

1. When using Automatic Segment Space Management (ASSM)

a) As temporary workaround, manually add extra space to the LOB segment
ALTER TABLE <lob_table>
MODIFY LOB (<column_name>) (allocate extent (size <extent size>));
OR
b) It may related Bug 6376915.
Refer to Note 6376915.8 "Bug 6376915 HW enqueue contention for ASSM LOB segments"
In 10.2.0.4 or above, this fix has been included, and can be enabled by setting event 44951 to a value
between 1 and 1024. A higher value would be more beneficial in reducing contention.
EVENT = "44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 - 1024>"
OR
c) Consider partitioning the LOB in a manner that will evenly distribute concurrent DML across multiple partitions

2. When using Manual Segment Space Management (MSSM)

a) As temporary workaround, manually add extra space to the LOB segment
ALTER TABLE <lob_table>
MODIFY LOB (<column_name>) (allocate extent (size <extent size>));
OR
b) Consider partitioning the LOB in a manner that will evenly distribute concurrent DML across multiple partitions

相关文章
  • How to diagnose high-level contention (enq: HW - contention) 2011-03-29

    Referred to above the high water level to promote the case, when the concurrent sessions at the same time insert, can easily cause a high level contention enq: HW - contention, then the occurrence of such contention, the how to diagnose it? View v $

  • oracle10g enq: TX - contention Wait Event 2010-06-25

    oracle10g enq: TX - contention wait event <switch> 10g, enqueue TX wait divided into four categories, namely 1. Enq: TX - row lock contention 2. Enq: TX - index contention 3. Enq: TX - ITL 4. Enq: TX - contention The first three of the more obvious

  • Oracle enq: DX - contention of the resolution process 2010-03-19

    First, the settlement process At half past nine on March 15, 2010 about large-scale distributed database transaction to wait. EVENT -------------------------------------------------- -------------- enq: DX - contention enq: DX - contention enq: DX -

  • Oracle enq: CF - contention cause data hang 2010-04-22

    Receiving customer calls, the database lost response, the database version of all business suspended Quote SQL> select * from v $ version where rownum = 1; BANNER -------------------------------------------------- -------------- Oracle Database 10g E

  • Oracle high-water line (high water mask) in a different section of the advance management mode 2011-03-29

    As we all know, Oracle marks the high water line below the block had been Oracle format, popular thing about that is that the high-water line of the block are used by Oracle. Usually perform insert operation, when the high-water line when the block i

  • Find online enq: TX - row lock contention wait event summary 2011-06-23

    [Turn] to wait for the event --- enq: TX - row lock contention enq is a locking mechanism to protect shared resources, a queuing mechanism, first in first out (FIFO) TX lock occurs because there are several general 1 different session update or delet

  • How to analyze AWR - zz 2011-03-27

    This switched http://www.os2ora.com How to analyze AWR (0) Kaya at os2ora.com Automatic Workload Repository is an important component of the introduction of 10g. Inside storage of the recent period of time, the default is seven days, the state of dat

  • Oracle ADDM automatic diagnostic monitoring tool introduced 2011-04-14

    Oracle AWR Introduction (AWR - Automatic Workload Repository) http://blog.csdn.net/tianlesoftware/archive/2009/10/17/4682300.aspx One. ADDM Overview ADDM (Automatic Database Diagnostic Monitor) is a self-implantation diagnosis Oracle database engine.

  • The meaning of various small icons Eclipse 2010-07-25

    The meaning of various small icons Eclipse The default type (Video Package) Public Types Default interface (including video) Public interface The default internal types (including video) Private internal type Protected internal type Public internal t

  • [Transfer] DFS lock handle 2010-11-27

    Problems and phenomena Production support of my colleagues received the report: The database response is very slow, a lot of database operations can not be completed, DB was hung out to live in the state. They also found that a node through OEM (10G

  • Oracle10g Automatic Database Diagnostic Monitor tool (ADDM) Guide 2010-10-23

    Introduction Chapter ADDM In Oracle9i and before, DBA who already has a lot of very good use of performance analysis tools, such as, tkprof, sql_trace, statspack, set event 10046 & 10053, etc. These tools can help the DBA quickly locate performance p

  • Database table is locked 2011-03-06

    The afternoon of March 3, there was a serious system failure, a large number of daemon throws the following exception: DBCP object created 2011-03-03 12:35:19 by the following code was never closed: java.lang.Exception at org.apache.commons.dbcp.Aban

  • A customer database performance diagnostic report 2011-03-16

    A description of the problem *** Database due to the recent volume of business increased, persistent transaction response is slow, at its peak (9:00 or so) or even get stuck, and that leads prospects unable to respond. Continuous observation by remot

  • 当Parallel DML遇到分布式事务.分区表和LOB时 2014-02-05

    今天遇到一个问题,某sql的并发度虽然已经全部获得但是只有少数几个slave干活. alter session enable parallel dml; insert /*+ parallel(t 16)*/一个本地分区表t select /*+ full(a parallel(a 32))*/* from 远程分区表[email protected] where 时间>=xxxx and 时间<xxxxx; insert要求有16个并发,虽然在v$PX_SESSION看到其获得的degree是16,但是并不是

  • (Transfer) oracle working mechanisms 2010-07-14

    We started talking from a user request, ORACLE complete the work of the mechanism is, first of all a user process sends a connection request, if using a host name or host name of local service hit using a machine name (not IP address ), then the requ

  • Read By Other Session 2010-12-02

    Read By Other Session Definition: When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data in

  • enq: TX - row lock contention 2011-07-19

    Today, customers received e-mail every morning that more than 10 points last week, the database will have a lot of locks, a collection of information under the AWR customers and found that ranked second in the TOP5 is enq: TX - row lock contention. S

  • MySQL transaction isolation level 2010-06-22

    SQL standard defines four categories isolation level, including a number of specific rules to limit what changes inside and outside the transaction is visible, which is not visible. Low-level isolation level generally support more concurrent processi

  • MySQL transaction isolation level Xiangjie 2010-09-24

    SQL standard defines four categories isolation level, including a number of specific rules to limit what changes inside and outside the transaction is visible, which is not visible. Low-level isolation level generally support more concurrent processi

  • Stored procedure to set the lock level set transaction isolation level 2011-03-19

    Grammar SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ] Remarks You can only set an isolation level option, and set the options on that connection will always remain in effect un