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欄位進行分割槽
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 (
（二）
如不存在（一）中情況
建議如下：
1、調整資料庫引數
sga_max_size 20GB
shared_pool_size 2.5GB
db_cache_size 16GB
2、將lob欄位進行分割槽
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-750097/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 如何診斷等待事件 enq: HW - contention事件ENQ
- ORACLE 如何診斷高水位爭用（enq: HW – contention）OracleENQ
- enq: HW - contentionENQ
- 等待事件之enq: HW - contention事件ENQ
- enq: HW - contention 問題的處理ENQ
- 大量insert引起的enq: HW - contention等待ENQ
- 【故障解決】enq: PS - contentionENQ
- 【故障診斷】cr塊slot notfound解決過程
- 'enq HW - contention' For Busy LOB Segment (文件 ID 740075.1)ENQ
- 使用oradebug dump processstate 來診斷enq: TX - row lock contentionENQ
- [20161208]等待事件enq: HW - contention事件ENQ
- [20140311]等待事件enq HW - contention事件ENQ
- How To Analyze the Wait Statistic: 'enq: HW - contention' (文件 ID 419348.1)AIENQ
- Enq : HW-contention高水位線的擴充套件競爭ENQ套件
- enq: US - contentionENQ
- enq: TM - contentionENQ
- enq:TM contentionENQ
- enq: DX - contentionENQ
- enq: TS - contentionENQ
- 如何解決enq: TX- index contentionENQIndex
- zt_Oracle enq: TX contention 和 enq: TM contention 等待事件OracleENQ事件
- enq:TX - index contentionENQIndex
- enq: TX - index contentionENQIndex
- 一次DG故障診斷過程分析
- enq: TX - row lock contentionENQ
- 關於enq: US – contentionENQ
- enq: WF - contention等待事件ENQ事件
- enq: CF - contention 等待事件ENQ事件
- enq: TX - index contention等待ENQIndex
- enq: TS - contention 等待事件ENQ事件
- enq: TM - contention TM 等待事件的原因及模擬ENQ事件
- 索引分裂造成的index contention等待事件的診斷索引Index事件
- enq: SQ - contention" waits in RACENQAI
- enq:TM-contention事件等待ENQ事件
- 消除 enq: DX - contention 等待事件ENQ事件
- 【恩墨學院】經典故障分析 - ASSM引發的索引爭用與 enq HW -contention 等待事件SSM索引ENQ事件
- enq: TM - contention解決之道——外來鍵無索引導致鎖爭用ENQ索引
- 一次ORA-4030問題診斷及解決（三）