Oracle动态性能视图学习笔记(3)_v$undostat

Oracle动态性能视图学习笔记(3)_v$undostat


参考文档<>Chapter 24


##################################################################
1 Overview
##################################################################
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction
concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode. Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.

(这个视图可以统计undo表空间的使用情况，事务的并发度，查询语句的执行时间。通过这个视图可以估算undo表空间的适合大小。
默认情况下每隔10Min采样一次数据，共保留7 days)


##################################################################
2 Useful Columns for v$undostat
##################################################################
1) End_time: End time for each ten minute interval

2) UNDOBLKS: The total number of undo blocks consumed.

3) MAXCONCURRENCY: Maximum number of transactions executed concurrently.
(最大的并发度)

4) TXNCOUNT: Total number of transactions executed within the interval.
(间隔期间内总共的事务数)

5) MAXQUERYLEN: Maximum length of queries, in seconds executed in the instance
(最长的查询时间)

6) EXPSTEALCNT: Number of times an undo extent must be transferred from one undo segment to another within the interval.
(undo extent出现Segment迁移的次数，什么时候会出现这种情况呢？)


7) UNDOTSN: undo tablespaces in service during each time period
undo表空间的ts#号

8) SSOLDERRCNT:Identifies the number of times the error ORA-01555 occurred.
出现ORA-01555错误的次数



##################################################################
3 示例
##################################################################
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select begin_time, end_time, undoblks, MAXCONCURRENCY, txncount,MAXQUERYLEN, EXPSTEALCNT, SSOLDERRCNT, undotsn from v$undostat;

SQL> show parameter retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     10800

SQL> select 10800/3600 from dual;

10800/3600
----------
         3


UNDO表空间的监测:
正在使用的undo block.返回的每一行代表一个Extent,每个Extent包含的Blocks数可能为1024,128.64,....
select tablespace_name,segment_name, blocks, bytes/1024, status,BLOCK_ID,BLOCK_ID+blocks-1 from dba_undo_extents where status='ACTIVE';
or
select tablespace_name, sum(bytes/1024/1024) from dba_undo_extents where status='ACTIVE' group by tablespace_name;



select begin_time, end_time, UNDOBLKS ten, ((undoblks*6*3)*8*1.5)/1024/1024 undo_size, TXNCOUNT,MAXQUERYLEN,MAXCONCURRENCY
from v$undostat
order by undoblks desc ;

6 -- 一小时 6 * 10
3 --- 为undo_retention时间．
8 --- blocksize=8k
1.5 --为预估1.5倍空间

从这里可以预估undo表空间size 大小．

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-669514/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-669514/