Tuning Undo - Part 2

The V$UNDOSTAT provides useful information in tuning the undo tablespace.

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view represents statistics across instances, thus each begin time, end time, and statistics value will be a unique interval per instance.

Important columns

UNXPSTEALCNT - The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests.

UNXPBLKRELCNT - The number of unexpired blocks removed from undo segments to be used by other transactions

UNXPBLKREUCNT - The number of unexpired undo blocks reused by transactions.

EXPSTEALCNT - The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests

EXPBLKRELCNT - The number of expired extents stolen from other undo segments to satisfy a space request

EXPBLKREUCNT - The number of expired undo blocks reused within the same undo segments

SSOLDERRCNT - The number of ORA-1555 errors that occurred during the interval

NOSPACEERRCNT - The number of Out-of-Space errors

Inference:

1. When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

2. If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

3. If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

Thanks