Read Consistency for LOBs-Datatypes
In previous chapters, we’ve discussed read consistency, multiversioning, and the role that undo plays in that. Well, when it comes to LOBs, the way read consistency is implemented changes. The LOBSEGMENT does not use undo to record its changes; rather, it versions the information directly in the LOBSEGMENT itself.
The LOBINDEX generates undo just as any other segment would, but the LOBSEGMENT does not. Instead, when you modify a LOB, Oracle allocates a new CHUNK and leaves the old CHUNK in place. If you roll back your transaction, the changes to the LOB index are rolled back, and the index will point to the old CHUNK again. So the undo maintenance is performed right in the LOBSEGMENT itself. As you modify the data, the old data is left in place and new data is created.
This comes into play for reading the LOB data as well. LOBs are read consistent, just as all other segments are. If you retrieve a LOB locator at 9:00 a.m., the LOB data you retrieve from it will be “as of 9:00 a.m.” Just like if you open a cursor (a resultset) at 9:00 a.m., the rows it produces will be as of that point in time.
Even if someone else comes along and modifies the LOB data and commits (or not), your LOB locator will be “as of 9:00 a.m.,” just like your resultset would be. Here, Oracle uses the LOBSEGMENT along with the read-consistent view of the LOBINDEX to undo the changes to the LOB, to present you with the LOB data as it existed when you retrieved the LOB locator. It does not use the undo information for the LOBSEGMENT, since none was generated for the LOBSEGMENT itself.
We can see that LOBs are read consistent easily. Consider this small table with an out-of-line LOB (it is stored in the LOBSEGMENT):
$ sqlplus eoda/foo@PDB1
SQL> create table t( id int primary key,txt clob )lob( txt) store as ( disable storage in row ); Table created.
SQL> insert into t values ( 1, ‘hello world’ ); 1 row created.
SQL> commit;Commit complete.
The read-consistent images for the cursor C came from the undo segments, whereas the read-consistent images for the LOB came from the LOBSEGMENT itself. So, that gives us a reason to be concerned: if the undo segments are not used to store rollback for LOBs and LOBs support read consistency, how can we prevent the dreaded ORA-01555: snapshot too old error from occurring? And, as important, how do we control the amount of space used by these old versions? That is where RETENTION and, alternatively, PCTVERSION come into play.
BasicFiles RETENTION
RETENTION tells the database to retain modified LOB segment data in the LOB segment in accordance with your database’s UNDO_RETENTION setting. If you set your UNDO_ RETENTION to two days, Oracle will attempt to not reuse LOB segment space freed by a modification.
That is, if you deleted all of your rows pointing to LOBS, Oracle would attempt to retain the data in the LOB segment (the deleted data) for two days in order to satisfy your UNDO_RETENTION policy, just as it would attempt to retain the undo information for the structured data (your relational rows and columns) in the UNDO tablespace for two days.
It is important you understand this: the freed space in the LOB segment will not be immediately reused by subsequent INSERTs or UPDATEs. This is a frequent cause of questions in the form of, “Why is my LOB segment growing and growing?” A mass purge followed by a reload of information will tend to cause the LOB segment to just grow, since the retention period has not yet expired.
Alternatively, the BasicFiles LOB storage clause could use PCTVERSION, which controls the percentage of allocated (used by LOBs at some point and blocks under the LOBSEGMENT’s HWM) LOB space that should be used for versioning of LOB data. The default of ten percent is adequate for many uses since many times you only ever INSERT and retrieve LOBs (updating of LOBs is typically not done; LOBs tend to be inserted once and retrieved many times). Therefore, not much space, if any, needs to be set aside for LOB versioning.
However, if you have an application that does modify the LOBs frequently, the default of ten percent may be too small if you frequently read LOBs at the same time some other session is modifying them. If you hit an ORA-22924 error while processing a LOB, the solution is not to increase the size of your undo tablespace, or increase the undo retention, or add more rollback segments if you are using manual undo management. Rather, you should use the following:
ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );and increase the amount of space to be used in that LOBSEGMENT for versioning of data.