LOB SPACE enq HW – contention 等待事件

--LOB SPACE enq HW – contention
--我们有时候对有lob表做并发insert的时候，会发现等待事件  LOB SPACE enq HW – contention
--看看这个等待事件的说明：

SQL> select NAME,PARAMETER1,PARAMETER2,PARAMETER3 from v$event_name where name like '%HW%';

NAME                                                             PARAMETER1      PARAMETER2      PARAMETER3
---------------------------------------------------------------- --------------- --------------- ---------------
enq: HW - contention                                             name|mode       table space #   block

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

--通过P3进行DBMS_UTILITY转换可以获知发生争用的文件和block

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK# from dual; 

     FILE#     BLOCK# 
       ———-     ———- 
         4     228475 

--进而通过file#和block#定位对象

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

OWNER SEGMENT_TYPE SEGMENT_NAME 
————— —————        —————————— 
SCOTT LOBSEGMENT   EMP_DATA_LOB 

--我们看到的确是lob字段高水位并发引起等待。

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 

 

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#, 
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK# 
from v$lock 
where type = 'HW'; 

 

--针对该LOB HWM高水位争用问题，常见的一种解决方法是为LOB segment预分配空间，因为预分配了空间所以出现高水位争用的机会少了，
--则 enq HW – contention争用出现的概率也随之降低，

--常见解决办法：预分配空间。但是move会影响到业务。

uniform统一分区的区 分配的大一点

uniform统一分区的区 分配的大一点
create tablespace oa_lob_space datafile size 20g extent management local uniform size 8m segment space management auto;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;
alter tablespace oa_lob_space add datafile size 20g;

alter table oa_mailmaintb rename to oa_mailmaintb_bak;

alter table dipoa.oa_mailmaintb_bak move lob (content) store as (tablespace oa_lob_space enable storage in row);
alter table dipoa.oa_mailmaintb_bak move lob (alladdresseename) store as (tablespace oa_lob_space enable storage in row);
alter table dipoa.oa_mailmaintb_bak move lob (operatorids) store as (tablespace oa_lob_space enable storage in row);
alter table dipoa.oa_mailmaintb_bak modify lob (content) (allocate extent (size 8G));
alter table dipoa.oa_mailmaintb_bak modify lob (alladdresseename) (allocate extent (size 4G));
alter table dipoa.oa_mailmaintb_bak modify lob (operatorids) (allocate extent (size 4G));

select table_name,index_name from dba_indexes where status='UNUSABLE';

alter index DIPOA.PK_MAILMAINTB_ID rebuild online parallel 8;
alter index DIPOA.IDX_ADDRESSER_TYPEID rebuild online parallel 8;
alter index DIPOA.IDX_ATS rebuild online parallel 8;
alter index DIPOA.IX_MAILMAINTB_TYPEID rebuild online parallel 8;
alter index DIPOA.IX_MAILMAINTB_TOPIC rebuild online parallel 8;
alter index DIPOA.IX_MAILMAINTB_SENDTIMINGDATE rebuild online parallel 8;
alter index DIPOA.IX_MAILMAINTB_SENDTIME rebuild online parallel 8;
alter index DIPOA.IDX_MAILMAINTB_SEEFLAG rebuild online parallel 8;
alter index DIPOA.IDX_ADDRESSER rebuild online parallel 8;
alter index DIPOA.IX_ADDRESSEE rebuild online parallel 8;

alter table oa_mailmaintb_bak rename to oa_mailmaintb;

select table_name,index_name from dba_indexes where status='UNUSABLE';