database was slow, due to undo retention

 Yesteday, I had a call from client, saying db was very slow.

Thu Feb 25 14:50:31 2010


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.1.0.7.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
  Time: 25-FEB-2010 14:50:31
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535

TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505

TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=202.157.69.223)(PORT=62300))

ORA-01555 caused by SQL statement

Are you observing any errors for undo tablespace or are you just concerned with the growth of the undo tablespace. Please note that unless you get ora-1555 or ora -30036 you need not increase the undo tablespace.

Run this query when undo tablespace is full

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

ACTIVE - Undo Extent is Active, Used by a transaction.

EXPIRED - Undo Extent is expired (Exceeded the Undo Retention).

UNEXPIRED - Undo Extent will be required to honour UNDO_RETENTION.

in case you see lot of expired extents, then no need to increase undo tablespace as the extents will be reused.