Oracle segment (2) log segment

In the official Oracle documents, there is such a paragraph to explain 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.

Because lob segment uses its own special consistent read implementation, it does not use undo tablespace to retain the front image, instead, when an update occurs, a chunk is allocated in the lob segment to insert a new record. In this case, if a record is modified multiple times, it will have multiple versions, which is a waste of space for large lob objects. Therefore, Oracle needs a way to control the size of the space of the pre-image. pctversion is used to implement this function. Pctversion is a percentage value, that is, the percentage of all current lob spaces used to store the front image. If the space of the front image is greater than pctversion, oracle will reuse these pre-image spaces without scaling.

Compare the impact of different pctversions on space usage

 

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 tex_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. 0625 m

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 13 m

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. 0625 m

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 17 m

SQL 10G> select dbms_lob.getlength (text) from testlob;
Dbms_lob.getlength (text)
--------
4096000