enq: HW - contention診斷及解決過程

YallonKing發表於2012-11-28
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
  
檢視當前db引數設定及連線數情況:
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章