“LOB that over here!”

One of the few errors that strikes fear in the heart of a DBA is the dreaded:

ORA-01555 snapshot too old
and
ORA-22924 snapshot too old

Of course there are plenty of blogs instructing the DBA to simply “increase the undo_retention”, and there are cases where this works as expected. However, LOBs can be different as two different mechanisms exist for undo management. A LOB column can be configured to use retention to manage before mages of the data, but that can be confusing as each LOB column MAY have its own retention setting. The DBA_LOBS view reports whether LOG column uses retention or pctversion to manage undo, and the associated setting being used. Let’s -dig into this a bit deeper.

LOB columns can have before images of data managed by time (retention) or by space (pctversion), Retention uses the traditional UNDO tablespace to store before images of data, and is subject to the configured retention period. There is only one way to assign retention to a LOB:

• Use the configured unto_retention value set for the database

Different LOB columns can display different retention settings depending upon when retention was set. The default, out of the box, retention id 900 seconds. When a LOB is changed to use retention rather than pctversion the current undo_retention value is set. Unfortunately should undo_retention be modified after a LOB is changed that new value will NOT be reflected in any LOB columns set to use retention. DBA_LOBS will provide pctversion and retention values for all LOBs in the database with three possible combinations:

1) Retention is set to a specific value and pctversion is NULL
2) pctversion is set and retention is NULL
3) Both are NULL

When BOTH retention and pctversion are NULL then the LOB is likely partitioned and DBA_LOB_PARTITIONS should supply the undo management settings. If partitioning is not available then it’s likely that the LOB is set to retention and the default undo_retention setting for the database is used. When pctversion is configured the UNDO tablespace is NOT used, storing any before images of the data in an area of the LOB reserved for this purpose. DBA_LOBS will report the percent of the LOB storage reserved for undo management. Increasing the pctversion setting will allocate more of the LOB storage for before images, hopefully correcting the source of the ORA-01555/ORA-22924 error.

Let’s go back to table X and column Y and address a “snapshot too old” error. We will begin with the easiest to address, Y is using the database-wide undo_retention setting, and it’s set to 1200 seconds. A query involving column Y throws an ORA-22924 error; the DBA checks the alert log for the query duration. Setting the system-wide undo_retention to the query duration (or slightly longer) should preserve the before image long enough to successfully execute the problem query. A repeat run should be error-free. If not, repeat the process, using the new query duration reported in the alert log.

Let us now say that LOB Y has its own retention setting of 900 seconds (set when that LOB was altered to use retention rather than pctversion).. As discussed previously changing the undo_retention won’t fix anything, the LOB itself must be modified.a By default when a LOB is set to retention the current undo_retention is set for the LOB being modified. Until the LOB is switched to pctversion and back to retention no change to undo_retention will be applied. Recently the system-wide undo_retention was set to 28800 seconds. The process of setting the new undo_retention for that LOB is as follows:

SQL> alter table X modify lob (Y) (pctversion 10);

Table altered.

SQL> alter table X modify lob (Y) (retention);

Table altered.

Examining DBA_LOBS for the current settings we find that retention is now set to 28800, up considerably from the initial setting of 900.

If pctversion is the undo method then simply altering the current pctversion setting is the action to take. If the LOB is set to a pctversion of 10 then setting it to a larger value should address the problem:

SQL> alter table X modify lob (Y) (pctversion 20);

Table altered.

Now 20 percent of the LOB storage is reserved for updates, which should correct the error.

In all of these situations the DBA must monitor the problem query to ensure it completes error-free. Since addressing such errors is an iterative process the initial change may not correct the error. Remember that SQL*Plus reports the query duration up until the error is thrown, so changes to the undo settings will likely increase the duration if the error is still being thrown. Know that it is not unusual for undo_retention to be set as high as 50000.

Another possibility is that retention is configured but the undo is guaranteed; guaranteed undo can throw other errors besides the “usual” 1555/22924 errors. Checking undo see if undo is guaranteed is fairly straightforward:

SQL> select retention from dba_tablespaces where tablespace_name like ‘%UNDO%’ ;

RETENTION

NOGUARANTEE

SQL>

If GUARANTEE is returned it may be worth the effort to remove the undo guarantee. [Guaranteed undo retention can be great for long-0running queries and transactions but can cause issues for other transactions as they may fail for lack of undo space. Normally unexpired undo can be “stolen: by other transactions if the space is needed to keep the transaction alive. A guarantee on the UNDO space prevents these steals from occurring. Guaranteed retention is a tablespace-level setting; as such it is very unusual to have UNDO guarantees in place for that very reason.] Be aware that changes to a production database usually require an outage and a maintenance request of some form along with approval of the work to be completed. Again monitor the database closely after such changes are completed to ensure other problems do not arise from the change.

LOB columns can be an interesting challenge for a DBA, especially when “snapshot too old” errors are thrown. Knowing the “ins and outs” of LOB undo management should make the task of managing LOB columns a bit easier. The DBA_LOBS view is the source for that information — that should be the first place the DBA should be looking when ORA-01555/ORA-22924 errors appear. The more the DBA knows the faster the situation can be resolved, and that makes EVERYONE happy.

Happiness is a good thing.