03.21.06
pctversion in lob segment
在oracle的官方文档上有这么一段话来解释pctversion
PCTVERSION integer
Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.
You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode.
由于lob segment采用了自己特殊的一致性读的实现，不是使用undo tablespace来保留前映象，而是当发生update时在lob segment内分配一个chunk去插入一条新的记录。这样的话，如果一条记录修改了多次，那么它就存在多个版本，对于很大的lob对象来说，这是十分浪费空间的。所以oracle需要有一个办法来控制这个保留前映象的空间的大小，pctversion就是为了实现这个功能的。pctversion是一个百分比的值，就是当前所有lob空间中用来存放前映象的百分比，如果前映象的空间大于pctversion了，那么oracle将会重用这些前映象空间而不去扩展。
对比一下不同pctversion对空间占用的影响
SQL 10G>select TABLE_NAME,SEGMENT_NAME,CHUNK,PCTVERSION,RETENTION from user_lobs where segment_name=’TEXT_LOB’;
TABLE_NAME SEGMENT_NAME CHUNK PCTVERSION RETENTION
—————————— —————————— ———- ———- ———-
TESTLOB TEXT_LOB 8192 1
SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB .0625M
SQL 10G>insert into testlob values(1,rpad(’a',4000));
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
————————
4000
SQL 10G>begin
2 for i in 1..10 loop
3 update testlob set text=text||text;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB 13M
SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>alter table testlob modify lob(text) (pctversion 99);
Table altered.
SQL 10G>insert into testlob values(1,rpad(’a',4000));
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB .0625M
SQL 10G>begin
2 for i in 1..10 loop
3 update testlob set text=text||text;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||’M’ from user_segments where segment_name=’TEXT_LOB’;
SEGMENT_NAME BYTES/1024/1024||’M’
——————————————————————————— —————————————–
TEXT_LOB 17M
SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
————————
4096000
可以看到pctversion的设置对lob空间大小还是有影响的，在很大的lob segment中这种影响会表现的十分明显，有兴趣的人可以对大lob段做测试。