Trick to slow down rate of LOB fragmentation

In a traditional table, Oracle stores previous image of the data (due to delete or update actions) in the UNDO tablespace, so this image could be rolled back in case of failure. However for tables with LOBs, oracle uses something different. The previous image of the data is stored inside the table itself, and this amount of space reserved as the virtual UNDO is controlled by the PCTVERSION parameter.


 


The default value is 10, meaning 10% of the LOB storage space is reserved for maintaining the old versions of the LOB. The older versions of the LOB data will not be overwritten until they consume 10% of the overall LOB storage space.


 


In real live, if you are dealing with LOB datatypes, sometimes you end up with fragmentation in the table. It would seem that even though you have deleted quite a lot of data, when you are inserting, Oracle does not seem to reuse the supposedly freed up space.




 To get around this problem, we simply set the PCTVERSION down (in our case to 0). The downside of this is you might get an ORA-22924 error (snapshot too old), in which case try to increase it a little. But so far in our case, 0 works fine.


Use the storage clause


STORE AS (ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0 cache STORAGE(BUFFER_POOL DEFAULT))

Thursday, December 22nd, 2011 at 14:27
Tags: ,
RSS feed for comments on this post
No comments yet.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>