Oracle 9i (and also available in 10g) provides a view to see how well the undo tablespace is performing. The V$UNDOSTAT view contains one line for each ten minute interval covering the last 7 days. This view can be used to determine if the undo tablespace needs to be larger or smaller. The following query is a common one for this view:
select to_char(begin_time,'MM/DD HH24:MI') as begin_time,
The first column (BEGIN_TIME) will be the start time of the interval, for that row of data. The next row of data will be for the interval ten minutes later. The V$UNDOSTAT also contains an END_TIME column, which should be equal to the BEGIN_TIME column in the subsequent row.
The UNDOBLKS column indicates the total number of undo blocks that were used during that time period. A good rule of thumb is to find the maximum value in this column and multiply it by the default database block size and use that value for the minimum size of the undo tablespace. The following query can find the maximum number of undo blocks used in any 10 minutes interval:
select max(undoblks) from v$undostat;
The MAXQUERYLEN column shows the maximum query length, in seconds, of any query in that time interval. The undo_retention initialization parameter should be set to a value larger than the maximum query length. Set this parameter to a value larger than the value returned by this query:
select max(maxquerylen) from v$undostat;
If there is not enough space in the undo tablespace, then expired undo blocks may be stolen to satisfy new transactions. Or worse yet, unexpired undo blocks may be stolen. The EXPSTEALCNT column shows how many times expired undo blocks were stolen for that time interval. The UNXPSTEALCNT column shows how many times unexpired undo blocks were stolen. The NOSPACEERRCNT column shows how many times there was no free space of any kind in the undo tablespace. If these columns contain non-zero values, consider increasing the size of the undo tablespace.
It is possible for long running queries to not have undo available for read consistency. When this happens, the long running query will run into the ORA-1555, snapshot too old error message. The SSOLDERRCNT column shows how many times queries received the ORA-1555 error message during that time interval. If this column contains non-zero values, consider raising the undo_retention initialization parameter.