enq: HW - contention诊断及解决过程

enq: HW - contention：
Lock used to broker the high watermark during parallel inserts
为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁。若在获取HW锁过程中发生争用，则等待enq: HW - contention事件。HW锁争用大部分是大量执行insert所引发的。
众所周知，Oracle高 水位线标志着该线以下的block均被Oracle格式过，通俗一点讲就是该高水位线以下的block都被Oracle使用过。 通常在执行insert操作时，当高水位线以下block不够用时，Oracle将会推进高水位线。更进一步讲，当有多个进程在同时进行insert操作 时，比较容易引起高水位线争用，主要表现为enq: HW enqueue

案例如下：
OS版本信息：
cpmisdb1:oracle:/oracle>uname -a
Linux cpmisdb1 2.6.18-128.el5xen #1 SMP Wed Dec 17 12:01:40 EST 2008 x86_64 x86_64 x86_64 GNU/Linux
查看版本：
SQL> select * from gv$version;

   INST_ID BANNER
---------- ----------------------------------------------------------------
         1 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
         1 PL/SQL Release 10.2.0.5.0 - Production
         1 CORE 10.2.0.5.0      Production
         1 TNS for Linux: Version 10.2.0.5.0 - Production
         1 NLSRTL Version 10.2.0.5.0 - Production
         2 Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
         2 PL/SQL Release 10.2.0.5.0 - Production
         2 CORE 10.2.0.5.0      Production
         2 TNS for Linux: Version 10.2.0.5.0 - Production
         2 NLSRTL Version 10.2.0.5.0 - Production

10 rows selected.

查看补丁情况：
cpmisdb1:oracle:/oracle>$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | egrep -i 'PSU|DATABASE PATCH SET UPDATE'
                                                    ENCAPSULATED BY EXCEPTION HANDLING
9952230    13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10248542   13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
11724962   13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
12419392   13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
12827745   13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
13343471   13343471  Wed Feb 08 01:34:59 CST 2012   DATABASE PSU 10.2.0.5.6 (INCLUDES CPUJAN2012)
13349665   13343471  Wed Feb 08 01:34:59 CST 2012   ORA-600 [KKSLMTL-VALNOTFOUND] WITH PSU 10.2.0.5.5

在awr中发现该等待时间出现在top5中，随进行查看对象信息：
Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
enq: HW - contention 656,542 121,477 185 64.2 Configuration
CPU time   24,556   13.0   
db file sequential read 2,531,289 20,341 8 10.8 User I/O
enq: TM - contention 12,986 4,276 329 2.3 Application
db file scattered read 1,003,605 3,093 3 1.6 User I/O

SQL> select p1, p2, p3 from v$session_wait where event = 'enq: HW - contention';

        P1         P2         P3
---------- ---------- ----------
1213661190         16  172103411
1213661190         16  172103411
SQL> set line 200
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(172103411) FILE#,
  2  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(172103411) BLOCK#
  3  from dual;

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

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
PLATFROM                       LOBSEGMENT         SYS_LOB0000055758C00006$$
SQL> col OBJECT_NAME for a30
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME from dba_objects where object_name='SYS_LOB0000055758C00006$$';

OWNER      OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL_
---------- ------------------------------ ------------------- --------- ---------
PLATFROM   SYS_LOB0000055758C00006$$      LOB                 14-APR-12 14-APR-12

SQL> select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,INDEX_NAME,PARTITIONED from dba_lobs where SEGMENT_NAME='SYS_LOB0000055758C00006$$';

OWNER                TABLE_NAME           COLUMN_NAME          SEGMENT_NAME              INDEX_NAME                     PAR
-------------------- -------------------- -------------------- ------------------------- ------------------------------ ---
PLATFROM             WL_SERVLET_SESSIONS  WL_SESSION_VALUES    SYS_LOB0000055758C00006$$ SYS_IL0000055758C00006$$       NO
此处，发现是一个含有lob字段的表

-------------------------------------------------------------------------------------------------------------------
中间件反应如下：
18:54:25
blat:1353991482611 triggerLAT:0 has been modified by another server in the cluster.
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PLATFROM.SYS_C0028620) violated
-------------------------------------------------------------------------------------------------------------------
进一步查看该索引信息：
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE,CREATED,LAST_DDL_TIME from dba_objects where object_name='SYS_C0028620';

OWNER      OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL_
---------- ------------------------------ ------------------- --------- ---------
PLATFROM   SYS_C0028620                   INDEX               14-APR-12 14-APR-12

SQL> col INDEX_TYPE for a10
SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from dba_indexes where INDEX_NAME='SYS_C0028620';

OWNER      INDEX_NAME           INDEX_TYPE TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  UNIQUENES
---------- -------------------- ---------- ------------------------------ ------------------------------ ----------- ---------
PLATFROM   SYS_C0028620         NORMAL     PLATFROM                       WL_SERVLET_SESSIONS            TABLE       UNIQUE

查看问题表上的索引信息：
SQL> col COLUMN_NAME for a20
SQL> select
  2    ind.table_name,
  3    ind.uniqueness,
  4    col.index_name,
  5    col.column_name,
  6    ind.distinct_keys,
  7    ind.sample_size
  8  from
  9    dba_ind_columns  col,
 10    dba_indexes      ind
 11  where
 12    ind.table_owner = 'PLATFROM'
 13      and
 14    ind.table_name in (upper('WL_SERVLET_SESSIONS'))
 15      and
 16    col.index_owner = ind.owner
 17      and
 18    col.index_name = ind.index_name
 19      and
 20    col.table_owner = ind.table_owner
 21      and
 22    col.table_name = ind.table_name
 23  order by
 24    col.table_name,
 25    col.index_name,
 26    col.column_position;

TABLE_NAME                     UNIQUENES INDEX_NAME           COLUMN_NAME          DISTINCT_KEYS SAMPLE_SIZE
------------------------------ --------- -------------------- -------------------- ------------- -----------
WL_SERVLET_SESSIONS            UNIQUE    SYS_C0028620         WL_ID                          158         158
WL_SERVLET_SESSIONS            UNIQUE    SYS_C0028620         WL_CONTEXT_PATH                158         158
此处，发现该表上存在联合主键

查看表结构：
SQL> desc PLATFROM.WL_SERVLET_SESSIONS
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 WL_ID                                           NOT NULL VARCHAR2(100)
 WL_CONTEXT_PATH                                 NOT NULL VARCHAR2(100)
 WL_IS_NEW                                                CHAR(1)
 WL_CREATE_TIME                                           NUMBER(20)
 WL_IS_VALID                                              NUMBER(38)
 WL_SESSION_VALUES                                        BLOB
 WL_ACCESS_TIME                                  NOT NULL NUMBER(20)
 WL_MAX_INACTIVE_INTERVAL                                 NUMBER(38)

查看该表的定义：
SQL> set serveroutput on;
SQL> DECLARE
  2  TT varchar2(4000);
  3  begin
  4  tt := DBMS_METADATA.GET_DDL('TABLE','WL_SERVLET_SESSIONS','PLATFROM');
  5  dbms_output.put_line(tt);
  6  end ;
  7  /

  CREATE TABLE "PLATFROM"."WL_SERVLET_SESSIONS"
   (    "WL_ID" VARCHAR2(100) NOT NULL ENABLE,
        "WL_CONTEXT_PATH" VARCHAR2(100) NOT NULL ENABLE,
        "WL_IS_NEW" CHAR(1),
        "WL_CREATE_TIME" NUMBER(20,0),
        "WL_IS_VALID" NUMBER(*,0),
        "WL_SESSION_VALUES" BLOB,
        "WL_ACCESS_TIME" NUMBER(20,0) NOT NULL
ENABLE,
        "WL_MAX_INACTIVE_INTERVAL" NUMBER(*,0),
         PRIMARY KEY ("WL_ID", "WL_CONTEXT_PATH")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
  TABLESPACE "PLATFORM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PLATFORM"
 LOB
("WL_SESSION_VALUES") STORE AS (
  TABLESPACE "PLATFORM" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))


PL/SQL procedure successfully completed.

查看表大小相关信息：
SQL> select count(*) from PLATFROM.WL_SERVLET_SESSIONS;

  COUNT(*)
----------
       223
       
SQL> select segment_name,sum(bytes)/1024/1024/1024 gb from dba_segments where segment_name='SYS_LOB0000055758C00006$$' group by segment_name;

SEGMENT_NAME                                                                              GB
--------------------------------------------------------------------------------- ----------
SYS_LOB0000055758C00006$$                                                                  2

SQL> col SEGMENT_NAME for a30
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME from dba_segments where SEGMENT_NAME='SYS_LOB0000055758C00006$$';

OWNER      SEGMENT_NAME                   PARTITION_NAME                 TABLESPACE_NAME
---------- ------------------------------ ------------------------------ ------------------------------
PLATFROM   SYS_LOB0000055758C00006$$                                     PLATFORM

addm报告部分如下：


FINDING 1: 100% impact (223802 seconds)
---------------------------------------
Significant virtual memory paging was detected on the host operating system.

   RECOMMENDATION 1: Host Configuration, 100% benefit (223802 seconds)
      ACTION: Host operating system was experiencing significant paging but no
         particular root cause could be detected. Investigate processes that
         do not belong to this instance running on the host that are consuming
         significant amount of virtual memory. Also consider adding more
         physical memory to the host.

   RECOMMENDATION 2: DB Configuration, 100% benefit (223802 seconds)
      ACTION: Consider enabling Automatic Shared Memory Management by setting
         the parameter "sga_target" to control the amount of SGA consumed by
         this instance.

FINDING 2: 72% impact (160626 seconds)
--------------------------------------
SQL statements consuming significant database time were found.

   RECOMMENDATION 1: SQL Tuning, 72% benefit (160626 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "9rd2jbvq2vphk" for
         possible performance improvements.
         RELEVANT OBJECT: SQL statement with SQL_ID 9rd2jbvq2vphk and
         PLAN_HASH 2960722896
         update WL_SERVLET_SESSIONS set wl_session_values = :1 , wl_is_new =
         :2 , wl_is_valid = :3 , wl_access_time = :4 ,
         wl_max_inactive_interval = :5  where wl_id = :6   and (
         wl_context_path = :7  or wl_context_path = :8  ) and ( wl_access_time
         = :9  or wl_access_time = :10  )
      RATIONALE: SQL statement with SQL_ID "9rd2jbvq2vphk" was executed 120538
         times and had an average elapsed time of 1.3 seconds.
      RATIONALE: Waiting for event "enq: HW - contention" in wait class
         "Configuration" accounted for 92% of the database time spent in
         processing the SQL statement with SQL_ID "9rd2jbvq2vphk".
      RATIONALE: Waiting for event "db file sequential read" in wait class
         "User I/O" accounted for 5% of the database time spent in processing
         the SQL statement with SQL_ID "9rd2jbvq2vphk".

FINDING 3: 67% impact (149319 seconds)
--------------------------------------
Contention on the high watermark (HW) enqueue was consuming significant
database time.

   RECOMMENDATION 1: Schema, 28% benefit (61563 seconds)
      ACTION: Consider partitioning the LOB
         "PLATFROM.SYS_LOB0000055758C00006$$" with object id 55759 in a manner
         that will evenly distribute concurrent DML across multiple
         partitions.
         RELEVANT OBJECT: database object with id 55759
      RATIONALE: The SQL statement with SQL_ID "9rd2jbvq2vphk" was found
         waiting for the high watermark (HW) of LOB
         "PLATFROM.SYS_LOB0000055758C00006$$" with object id 55759.
         RELEVANT OBJECT: SQL statement with SQL_ID 9rd2jbvq2vphk
         update WL_SERVLET_SESSIONS set wl_session_values = :1 , wl_is_new =
         :2 , wl_is_valid = :3 , wl_access_time = :4 ,
         wl_max_inactive_interval = :5  where wl_id = :6   and (
         wl_context_path = :7  or wl_context_path = :8  ) and ( wl_access_time
         = :9  or wl_access_time = :10  )

   RECOMMENDATION 2: Schema, 2.2% benefit (4959 seconds)
      ACTION: Consider partitioning the TABLE "PLATFROM.WL_SERVLET_SESSIONS"
         with object id 55758 in a manner that will evenly distribute
         concurrent DML across multiple partitions.
         RELEVANT OBJECT: database object with id 55758
      RATIONALE: The SQL statement with SQL_ID "9rd2jbvq2vphk" was found
         waiting for the high watermark (HW) of TABLE
         "PLATFROM.WL_SERVLET_SESSIONS" with object id 55758.
         RELEVANT OBJECT: SQL statement with SQL_ID 9rd2jbvq2vphk
         update WL_SERVLET_SESSIONS set wl_session_values = :1 , wl_is_new =
         :2 , wl_is_valid = :3 , wl_access_time = :4 ,
         wl_max_inactive_interval = :5  where wl_id = :6   and (
         wl_context_path = :7  or wl_context_path = :8  ) and ( wl_access_time
         = :9  or wl_access_time = :10  )

   RECOMMENDATION 3: Schema, 0.22% benefit (485 seconds)
      ACTION: Consider partitioning the INDEX "PLATFROM.SYS_C0028620" with
         object id 55761 in a manner that will evenly distribute concurrent
         DML across multiple partitions.
         RELEVANT OBJECT: database object with id 55761
      RATIONALE: The SQL statement with SQL_ID "9rd2jbvq2vphk" was found
         waiting for the high watermark (HW) of INDEX "PLATFROM.SYS_C0028620"
         with object id 55761.
         RELEVANT OBJECT: SQL statement with SQL_ID 9rd2jbvq2vphk
         update WL_SERVLET_SESSIONS set wl_session_values = :1 , wl_is_new =
         :2 , wl_is_valid = :3 , wl_access_time = :4 ,
         wl_max_inactive_interval = :5  where wl_id = :6   and (
         wl_context_path = :7  or wl_context_path = :8  ) and ( wl_access_time
         = :9  or wl_access_time = :10  )

   SYMPTOMS THAT LED TO THE FINDING:
      SYMPTOM: Wait class "Configuration" was consuming significant database
               time. (67% impact [149325 seconds])



在awr和addm的报告中也看到以下语句存在执行时间过长和急需调整的建议以及sga相关调整建议。
受影响的sql：
update PLATFROM.WL_SERVLET_SESSIONS
   set wl_session_values        = :1,
       wl_is_new                = :2,
       wl_is_valid              = :3,
       wl_access_time           = :4,
       wl_max_inactive_interval = :5
 where wl_id = :6
   and (wl_context_path = :7 or wl_context_path = :8)
   and (wl_access_time = :9 or wl_access_time = :10)
        
查看主机内存信息：  
cpmisdb1:oracle:/oracle>free -g
             total       used       free     shared    buffers     cached
Mem:            31         23          8          0          1         16
-/+ buffers/cache:          4         26
Swap:           15          0         15
  
查看当前ｄｂ参数设置及连接数情况：
SQL> sho parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 10G
sga_target                           big integer 0
SQL> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 4G
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     20
SQL> sho parameter shar

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 107374182
shared_pool_size                     big integer 2G
shared_server_sessions               integer
shared_servers                       integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 3G
SQL> select inst_id,count(*) from gv$session where username is not null group by inst_id;

   INST_ID   COUNT(*)
---------- ----------
         1        181
         2        186

最后建议采取的措施：
（一）
若存在批量加载数据，则在加载之前使用如下语句对lob预分配空间具体见（ID 740075.1）。
 ALTER TABLE
 MODIFY LOB () (allocate extent (size ));
（二）
如不存在（一）中情况
建议如下：
1、调整数据库参数
sga_max_size        20GB
shared_pool_size    2.5GB
db_cache_size         16GB

2、将lob字段进行分区
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26143577/viewspace-750097/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26143577/viewspace-750097/