undo信息查询

下面的视图在自动undo管理模式下提供了undo空间使用的信息

The following dynamic performance views are useful for obtaining space information about the undo tablespace:

View	Description
V$UNDOSTAT	Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
V$ROLLSTAT	For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace
V$TRANSACTION	Contains undo segment information
DBA_UNDO_EXTENTS	Shows the status and size of each extent in the undo tablespace.
DBA_HIST_UNDOSTAT	Contains statistical snapshots of V$UNDOSTAT information. Please refer to Oracle Database 2 Day DBA for more information.

v$undostat视图对于监控在当前实例下事务执行使用的undo空间很有用，可以计算undo空间的消耗，事务的并行，undo retention的优化，长时间运行sql的长度。

在视图的每一行中包含了10分钟间隔的信息的收集。行是按begin_time列降序排列的。每一行被begin_time和end_time标记，代表了这个时间段的信息，这个视图包含576行，4天的数据。

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
         TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
         FROM v$UNDOSTAT WHERE rownum <= 144;
  
  BEGIN_TIME          END_TIME               UNDOTSN   UNDOBLKS   TXNCOUNT     MAXCON
  ------------------- ------------------- ---------- ---------- ---------- ----------
  10/28/2004 14:25:12 10/28/2004 14:32:17          8         74   12071108          3
  10/28/2004 14:15:12 10/28/2004 14:25:12          8         49   12070698          2
  10/28/2004 14:05:12 10/28/2004 14:15:12          8        125   12070220          1
  10/28/2004 13:55:12 10/28/2004 14:05:12          8         99   12066511          3
  ...
  10/27/2004 14:45:12 10/27/2004 14:55:12          8         15   11831676          1
  10/27/2004 14:35:12 10/27/2004 14:45:12          8        154   11831165          2

这个视图显示了1天内的undo空间的消耗情况。