V$UNDOSTAT

This view displays a histogram of statistical data to show how well the system is working. Each row in the view keeps statistics collected in the instance for a 10-minute interval.You can use this view to estimate the amount of undo space required for the current workload. The database uses this view to tune undo usage in the system. This view is available in both SMU (system managed undo) mode and RBU (rollback segment undo) mode.

Column	Datatype	Description
BEGIN_TIME	DATE	Identifies the beginning of the time interval.
END_TIME	DATE	Identifies the end of the time interval.
UNDOTSN	NUMBER	Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.
UNDOBLKS	NUMBER	Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
TXNCOUNT	NUMBER	Identifies the total number of transactions executed within the period.
MAXQUERYLEN	NUMBER	Identifies the length of the longest query (in number of seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION parameter.
MAXCONCURRENCY	NUMBER	Identifies the highest number of transactions executed concurrently within the period.
UNXPSTEALCNT	NUMBER	The number of attempts to obtain undo space by stealing unexpired extents from other transactions.
UNXPBLKRELCNY	NUMBER	The number of unexpired blocks removed from certain undo segments so they can be used by other transactions.
UNXPBLKREUCNT	NUMBER	The number of unexpired undo blocks reused by transactions.
EXPSTEALCNT	NUMBER	The number of attempts to steal expired undo blocks from other undo segments.
EXPBLKRELCNT	NUMBER	The number of expired undo blocks stolen from other undo segments.
EXPBLKREUCNT	NUMBER	The number of expired undo blocks reused within the same undo segments.
SSOLDERRCNT	NUMBER	Identifies the number of OER errors occurring within a period. You can use this statistic to decide whether or not the UNDO_RETENTION parameter is set properly given the size of the undo tablespace. Lowering the UNDO_RETENTION value can reduce the occurrence of this error.
NOSPACEERRCNT	NUMBER	Identifies the number of OER errors reported in the current instance. If there is a nonzero value in this column, the current undo tablespace needs more space (in the UNDO_RETENTION parameter).