利用44951 event解决LOB SPACE enq HW – contention等待争用

对存有LOB大对象的表的并发插入、更新引起的LOB Segment High Water Mark是常见的LOb并发争用； 特别是在ASSM(Auto Segment Space Management)的表空间上这种LOB的HWM可能比MSSM(Manual Segment Space Management)更为严重，其原因是在MSSM下LOB的HWM bump一次会获取128个chunk， 而在ASSM下默认只获取必要的chunk(default 1) (This is worse in ASSM than MSSM because ASSM only gets the amount of space requested while MSSM gets 128 chunks at a time.)。

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

ALTER TABLE <lob_table>
MODIFY LOB (<column_name>) (allocate extent (size <extent size>));

使用该方法手动为LOB SEGMENT预分配空间时需要注意，存在<NOTE 1229669.1 Bug 8198906 – Segment header corruption if extent allocation operation is interrupted>。

另一种缓解该LOB HW争用的有效方式是从 10.2.0.3上Bug 6376915引入的44951 event，该事件在10.2.0.4和11.1.0.7之后也都被引入。在10.2.0.3中使用该44951 event则需要优先apply Bug 6376915的one-off patch。

44951 event的LEVEL参数定义了在ASSM下当LOB segment的HWM上升时一次获取的chunks数目，一般推荐设置为1024，即一次get 1024个chunk，由于单次跃升的HWM更高了，这让进入enq HW – contention等待的机会变少了。 需要注意的是该44951 event仅仅对ASSM表空间上的LOB SEGMENT有效。

设置方法如下：

alter system set events ‘44951 trace name context forever, level 1024’;

以下是在11.2.0.3 中的实际测试，通过大量并发插入到LOB表模拟enq HW，并通过AWR报告中的性能信息比对设置该44951 event先后的区别：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable
SQL> 

conn maclean/oracle

 CREATE TABLE "MACLEAN_LOB" ( "T1" VARCHAR2(200) NOT NULL , "T2" CLOB, "T3" CLOB)  tablespace users
    LOB ("T2") 
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE ) 
    LOB ("T3") 
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE );

SQL> select segment_space_management from dba_tablespaces where tablespace_name='USERS';

SEGMEN
------
AUTO

exec dbms_workload_repository.create_snapshot;

开3个进程并发插入LOB表

begin
for i in 1..10000 loop
 insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
 /

 exec dbms_workload_repository.create_snapshot;

SQL>   select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
    490496 SYS_LOB0000076982C00003$$
    482304 SYS_LOB0000076982C00002$$

	SQL> truncate table maclean.maclean_lob;

Table truncated.

SQL> SQL> 
SQL> 
SQL>  select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
        64 SYS_LOB0000076982C00003$$
        64 SYS_LOB0000076982C00002$$

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

alter system set events '44951 trace name context forever, level 1024';

		 exec dbms_workload_repository.create_snapshot;

	开3个进程并发插入LOB表

begin
for i in 1..10000 loop
 insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
 /	

 		 exec dbms_workload_repository.create_snapshot;

 select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
    483328 SYS_LOB0000076982C00003$$
    483328 SYS_LOB0000076982C00002$$

以上可以看到虽然设置了44951 level 1024，但并不会因为单次bump hwm的chunks数增加而导致大量空间的浪费。

对比AWR可以发现设置44961 level 1024后 enq HW – contention消耗的DB TIME明显减少：

此外在10.2.0.3之前还有一种方案即设置LOB的PCTVERSION 为0/100，但是该方案会导致LOB占用的SPACE大幅上升，所以不推荐，你有大量的理由至少升级DB到10.2.0.5.9。