-
Title
What does the Undo Size Oracle Advisory in SQL PI show? -
Description
What triggers Undo Size Oracle Advisories?
What does it mean and how can the issue be fixed?
-
Cause
Encountering errors "ORA-30036: unable to extend segment by string in undo tablespace" or "ORA-01555: Snapshot Too Old" indicates that undo size should be tuned.
Tuning undo size includes setting the "undo_retention" parameter to an optimal value and allocating the proper size for the undo tablespace.
The V$UNDOSTAT view provides statistics about undo space consumption, transaction concurrency and length of queries executed in the instance.
-
Resolution
Recommendation- The NOSPACEERRCNT shows how many times there was no free space of any kind in the undo tablespace. If these parameter contain non-zero value, consider increasing the size of the undo tablespace.
- The SSOLDERRCNT shows how many times queries received the ORA-1555 error message. If this column contains non-zero values, consider raising the undo_retention initialization parameter.
The following metrics should be taken into consideration during the tuning process
- Retention Guarantee
- Undo Management
- Auto-extensible
To guarantee the success of long-running queries you can enable "retention guarantee" and then Oracle never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. This option is disabled by default and should be used with caution.
-
Additional Information
The undo tablespace consists of undo segments which hold the "before" images of the data that has been changed by users running transactions. The undo is essential for rollback operations, data concurrency, read consistency and is being used by several flashback features. The longer Oracle keeps changes in the undo tablespace, the more overall space will be required. If there is not enough space in the undo tablespace, the system will use space that has not expired according to the "undo_retention" parameter. Important: There is a different behavior of this parameter in fixed-size undo tablespace and in autoextend undo tablespace.