LOB segment上的HW enqueue问题 （9205 version）
第一次遇到这个问题是在2006年，后来陆陆续续的遇到过好几次，都是在9205的版本上。
可是一直没有在自己的blog上好好总结一下。我以为自己写过了，今天想找给别人时才发现没有写过。
数据库版本当时为9205。问题表现为大量的active session等待在mod为6的HW enqueue上。
当前获得enqueue的session执行非常慢，平时小于一秒钟的DML操作现在大约需要几十秒钟才能完成。
查询v$lock得到下面的信息：
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
———- — ———- ———- ———- ———- ———- ———-
3575 HW 16 159461642 6 0 18 1
3546 HW 16 159461642 0 6 257 0
3542 HW 16 159461642 0 6 348 0
3468 HW 16 159461642 0 6 31 0
3329 HW 16 159461642 0 6 46 0
2634 HW 16 159461642 0 6 171 0
2565 HW 16 159461642 0 6 94 0
2532 HW 16 159461642 0 6 77 0
2497 HW 16 159461642 0 6 304 0
2179 HW 16 159461642 0 6 386 0
1972 HW 16 159461642 0 6 116 0
1927 HW 16 159461642 0 6 183 0
1777 HW 16 159461642 0 6 209 0
1690 HW 16 159461642 0 6 249 0
1637 HW 16 159461642 0 6 164 0
1616 HW 16 159461642 0 6 17 0
1601 HW 16 159461642 0 6 109 0
1353 HW 16 159461642 0 6 134 0
1236 HW 16 159461642 0 6 320 0
1218 HW 16 159461642 0 6 64 0
1163 HW 16 159461642 0 6 234 0
1150 HW 16 159461642 0 6 291 0
1082 HW 16 159461642 0 6 150 0
1074 HW 16 159461642 0 6 219 0
987 HW 16 159461642 0 6 195 0
713 HW 16 159461642 0 6 374 0
683 HW 16 159461642 0 6 331 0
672 HW 16 159461642 0 6 275 0
535 HW 16 159461642 0 6 361 0
404 HW 16 159461642 0 6 400 0
ID2是DBA,我们可以用“oracle DBA convertor”工具得到相应的File#和Block#
SQL> !dba 159461642
Oracle DBA convertor
by Stephan Haisley, Center Of Expertise, Oracle Corporation
RDBA: 0×981310a (159461642) File#: 38 Block#: 78090SQL> select segment_name,partition_name,header_file,header_block from
SQL> dba_segments where segment_name=’SYS_LOB0000009458C00013$$’;SEGMENT_NAME PARTITION_NAME HEADER_FILE HEADER_BLOCK
—————————————- —————————— ———– ————
SYS_LOB0000009458C00013$$ SYS_LOB_P19 38 65289
SYS_LOB0000009458C00013$$ SYS_LOB_P20 46 52489
SYS_LOB0000009458C00013$$ SYS_LOB_P21 38 78089 — 78090 = 78089+1
然后你会发现这个Block并不是Lob segment header，而是Lob segment header后面一个block。注意这一点非常重要，这是这一类问题的标准特征。
如果等待的block是segment header，那么你遇到的不是本文所描述的问题。
一般的HW enqueue发生在segment header上，而后面这个block是什么呢？
说到这个block，要从Lob segment的undo方式说起。Lob segment将old image存放在自己的segment中，而不是undo segment中。
这块存放old image的空间的大小有两种方式来定义，pctversion或者是retention。
对此不熟悉的同学可以参考我以前的两篇文章当ORA-01555遇到了LOB和PCTVERSION or RETENTION?
而这个block就是用来记录这些可以被回收的old image blocks，采用bitmap方式存储。
当可回收的old image space超过PCTVERSION定义的百分比时，新增加的数据可以从这些old image space中回收。
但是oracle在回收时有两个bug：
Bug 4867884(Note:4867884.8) - Lob HW lock contention with space reclaimation — fixed in 10.1.0.6, 10.2.0.3, 11g (Future version)
Bug 4113930(Note:4113930.8) - Space reclaimation shows HW enqueue contention with concurrent insert of LOBs — fixed in 9.2.0.8 , 10.1.0.5 , 10.2.0.1
触发这个bug有两个条件
1. 可回收的old image space超过PCTVERSION定义的百分比
2. concurrent transaction超过一定的数值，具体的值不清楚，我遇到问题时的concurrent transaction数目大约为30
所以解决这个问题就从上面两点来入手。
1.
最快解决问题的方法就是调大pctversion。
dump segment header后的那个block（38，78090）
alter system dump datafile 38 block 78090;
找到一行free blocks:10109615，根据这个可以算出free blocks所占的百分比，然后调整pctversion使其大于该百分比，这样就不会有回收空间的动作。
这一方法可以很快的解决问题，就是比较浪费空间。如果有维护的时间，可以通过move的方式来回收空间。
2.
从segment上的并发数入手，例如减少并发量，或者hash partition table使每个segment上面的并发数减少
们可以用“oracle DBA convertor”工具,
请讲这是一个什么工具,怎么安装使用?
Stephan Haisley写的一个将DBA转化为file#和block#的工具
你也可以通过这个procedure来转化:
================
INPUT PARAMETERS
================
EXEC CDBA(”,”)
=======
Script:
=======
CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS
x NUMBER;
digits# NUMBER;
results NUMBER := 0;
file# NUMBER := 0;
block# NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;
BEGIN
IF upper(imode) = ‘H’ THEN
digits# := length( iblock );
FOR x IN 1..digits# LOOP
cur_digit := upper(substr( iblock, x, 1 ));
IF cur_digit IN (’A',’B',’C',’D',’E',’F') THEN
cur_digit# := ascii( cur_digit ) - ascii(’A') +10;
ELSE
cur_digit# := to_number(cur_digit);
END IF;
results := (results *16) + cur_digit#;
END LOOP;
ELSE
IF upper(imode) = ‘D’ THEN
results := to_number(iblock);
ELSE
dbms_output.put_line(’H = Hex Input … D = Decimal Input’);
RETURN;
END IF;
END IF;
file# := dbms_utility.data_block_address_file(results);
block# := dbms_utility.data_block_address_block(results);
dbms_output.put_line(’.');
dbms_output.put_line( ‘The file is ‘ || file# );
dbms_output.put_line( ‘The block is ‘ || block# );
END;
/