UNDO管理之三：UNDO产生的估算
topUNDO相关统计
在日常管理中，除了要用到上一篇文章中所说的那些数据字典之外，还有一个保存统计信息的数据字典是非常重要的，那就是著名的由V$SYSSTAT、V$SESSTAT、V$MYSTAT组成的系统信息统计家族，系统统计动态视图中所包含的统计信息阵容非常庞大，其中就包含了一些与UNDO相关的统计信息：
SQL> set lines 145 pages 9999
SQL> col name for a60
SQL> SELECT * FROM v$statname WHERE NAME LIKE '%undo%' ;
STAT# NAME CLASS STAT_ID
------ ------------------------------------------------------------ ------ ----------
89 DBWR undo block writes 8 111270822
216 undo change vector size 128 1465971540
217 transaction tables consistent reads - undo records applied 128 1054055970
219 data blocks consistent reads - undo records applied 128 2915445793
228 rollback changes - undo records applied 128 3616249137
242 auto extends on undo tablespace 128 3140365462
244 total number of undo segments dropped 128 3623640507
262 global undo segment hints helped 128 1791494885
263 global undo segment hints were stale 128 3309048233
264 local undo segment hints helped 128 1047863356
265 local undo segment hints were stale 128 2070200837
266 undo segment header was pinned 128 248401831
271 IMU undo retention flush 128 2087226422
277 IMU undo allocation size 128 244193920
285 SMON posted for undo segment recovery 128 2117898593
288 SMON posted for undo segment shrink 128 579492169
16 rows selected.
首先在V$STATNAME里面所有的统计项目在Oracle文档（Statistics Descriptions）中都能找到相应的描述。
下面说一下一些通常比较关注的统计项目。
- undo change vector size
- 此项目统计的是产生的UNDO数据的大小，以bytes计算。这个统计不管是在分析单个事务产生的UNDO情况还是分析整个系统的UNDO产生变化的情况都是非常有用的。
- data blocks consistent reads – undo records applied
- 此项目统计的是发生数据块一致性读所应用的UNDO记录的大小，单位为bytes。
- rollback changes – undo records applied
- 此项目统计回滚操作所应用的UNDO记录的大小，单位为bytes。
下面从实验角度来演示下上述的三个统计数据，实验之前先说明一下，这里用到的mystat.sql和mystat2.sql是借用Tom Kyte在书中《Oracle9i&10g编程艺术:深入数据库体系结构》在第9章中所给出的代码，代码如下。
-- mystat2.sql
-- Copy from Tom Kyte's Expert Oracle Database Architecture
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
-- mystat2.sql
-- Copy from Tom Kyte's Expert Oracle Database Architecture
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
topundo change vector size
“undo change vector size”记录的数据库的UNDO产生的情况，现在就通过观察V$MYSTAT中此项目的变化来观察当前会话事务中UNDO的产生情况。
SQL> @mystat "undo change" NAME VALUE ---------------------------------- ------ undo change vector size 952 SQL> update tt set a=1 where a=1; 1 row updated. SQL> @mystat2 NAME V DIFF ---------------------------------- ------ ---- undo change vector size 1040 88
从实验可以看出更新语句产生的UNDO的大小是88bytes。
topdata blocks consistent reads
现在看看这个一致性读的指标，现在先在一个会话中修改一个表的数据，如下
SQL> update tt set a=1 where a=1; 1 row updated.
然后在另外的会话中读取这条数据，看看这个读取会话的UNDO应用情况：
SQL> select b.name,a.value
2 from v$mystat a, v$statname b
3 where a.statistic# = b.statistic#
4 and b.name like '%undo records applied%';
NAME VALUE
----------------------------------------------------------- ------
transaction tables consistent reads - undo records applied 0
data blocks consistent reads - undo records applied 0
rollback changes - undo records applied 0
SQL> select a from tt where a=1;
A
----------
1
SQL> select b.name,a.value
2 from v$mystat a, v$statname b
3 where a.statistic# = b.statistic#
4 and b.name like '%undo records applied%';
NAME VALUE
----------------------------------------------------------- ------
transaction tables consistent reads - undo records applied 0
data blocks consistent reads - undo records applied 2
rollback changes - undo records applied 0
结果是为了读取这条记录使用2bytes的UNDO数据。
同样的其他的项目也可以使用上面的方法进行验证。
topUNDO空间的估算
在进行数据回填(backfill)的时候，大家最关心的一个问题应该就是大量的数据回填会不会把UNDO给搞爆，而且这个问题应该是在进行回填操作之前就要明确的，不能在UNDO不足之后才去增加UNDO，那怎么做到呢？答案就在动态视图V$MYSTAT里面了，利用V$MYSTAT我们很容易能知道一个简单的DML操作需要多少的UNDO数据，然后根据要回填数据的总量估计一下就行了。
现在假设要给表BIGTABLE的列C更新为一个新值，当前列C的定义为VARCHAR2(2000)，里面填充的数据长度不一的由字符”C”组成的字符串，在列C上面建立有索引。表中的记录总数如下：
SQL> DESC bigtable
Name Null? Type
----------------------------- -------- --------------------
A NUMBER
B CHAR(2000)
C VARCHAR2(2000)
SQL> SELECT COUNT(*) FROM bigtable;
COUNT(*)
----------
14583
需要执行的数据回填的语句为：
UPDATE bigtable SET c=RPAD('D',DBMS_RANDOM.VALUE(0,1999),'D');
此次试验所执行的回填语句比较简单，就是一个update语句，然后更新一个变长的字符串列C。了解UNDO基本工作原理的应该清楚在执行一个update语句的时候Oracle会先将更新之前的数据保存到UNDO之中，假设这里的第一行中C列中的数据是”CCC”，更新后为”DDDD”，那么在UNDO中会保存”CCC”以及一些相关的信息。每行中C列当前字符串越长，消耗的UNDO空间也就越多，为了尽量使我们的估算更加的准确，我们需要找到C列中的某个实际长度接近于C列平均长度的行，然后对这行进行更新，通过V$MYSTAT得到更新所需要的UNDO的大小，然后就可以利用总行数估算出更新全表所需要的UNDO空间的大小了。
首先来得到C列的平均长度：
SQL> SELECT SUM(LENGTH(c))/COUNT(*) AVG_LEN FROM bigtable; AVG_LEN ---------- 1005.43276
再找出一行C列长度与平均长度相近的行：
SQL> SELECT a FROM (
2 SELECT a FROM orainst.bigtable WHERE LENGTH(c)=1005
3 ) where rownum=1;
A
----------
1639
接着看看更新这行会产生的UNDO的大小了：
SQL> @mystat "undo change"
NAME VALUE
------------------------ ------
undo change vector size 240
SQL> UPDATE bigtable
SET c=RPAD('D',DBMS_RANDOM.VALUE(0,1999),'D')
WHERE a=1639;
1 row updated.
SQL> @mystat2
NAME V DIFF
------------------------ ----- ------
undo change vector size 3524 3,284
现在知道更新一行数据所需要的UNDO是3284bytes。因为UNDO数据是以数据块的方式使用的，通常情况下一行记录是不会记录在多个块中的，因此接下来我们计算的不应该是拿单行记录乘以总行数以得出要使用的UNDO的总数，而是要计算全部数据所需要的UNDO块的数量，以一个普通的8k的UNDO数据块来说，它可以存放的UNDO记录的数量为 8*1024/3284，计算如下
SQL> SELECT FLOOR(8*1024/3284) "ROW NUM" FROM dual;
ROW NUM
----------
2
也就是一个块也就只能放下2条UNDO记录，那么14583行需要UNDO大约就是
SQL> SELECT CEIL(14583/2) "BLOCK NUM",
2 CEIL(14583/2)*8/1024 "SIZE(MB)" FROM dual;
BLOCK NUM SIZE(MB)
---------- ----------
7292 56.96875
57M，这个数值对我现在512M的UNDO设置还是不出问题的，接下来要做的就是验证一下这个估计了。
SQL> SELECT sess.SID,sess.serial#, sess.osuser, sess.username,
2 rseg.segment_name SEGMENT, rseg.tablespace_name, trans.used_ublk,
3 trans.used_ublk*8/1024 "UNDO SIZE(KB)", rseg.STATUS
4 FROM v$session sess, v$transaction trans, dba_rollback_segs rseg
5 WHERE sess.taddr=trans.addr AND trans.xidusn=rseg.segment_id(+);
SID SERIAL# OSUSER USERNAME SEGMENT TABLESPACE USED_UBLK UNDO SIZE(KB) STATUS
---- -------- -------- --------- --------------- ---------- ---------- ------------- ----------
404 27 oracle ORAINST _SYSSMU163_1273 UNDO_T2 6560 51.25 ONLINE
636148$
最终实际的结果是6560个UNDO块，51M UNDO数据，看来实际估算的还是多了一些。
当然要是很了解UNDO的实际存储方式的话，也是可以直接计算出来的。当然这也就适合于估计一个没有任何数据的堆表，但是当表的结构变得复杂，再加上索引触发器等等相关的因素之后要准确的估计UNDO还是得借助于V$MYSTAT的帮忙才能使估算更加的准确一些的。
topUNDO产生率
到现在为止我们的尝试都是围绕着V$MYSTAT这一个视图来进行的，实际上统计相关的视图却不止于这一个，其他的还包括会话级统计的V$SESSTAT，系统级统计的V$SYSSTAT以及AWR中会用到的系统级统计的历史记录表DBA_HIST_SYSSTAT，下面要利用的就是这个历史记录表DBA_HIST_SYSSTAT了。
表DBA_HIST_SYSSTAT记录的是每隔1个小时的视图V$SYSSTAT的快照，通常用在AWR上。在这里我们就使用此表来计算系统一段时间以来的UNDO产生的情况，为了解系统整体的变化提供一个参考数据。实际中使用这个历史表也是非常的简单的，一个语句就搞定了，相关的语句如下：
SET pages 999
SET lines 140
col curval FORMAT 99999999999999999
col prevval FORMAT 99999999999999999
SELECT cur_stat.snap_id CurID,
prev_stat.snap_id PrevID,
To_char(sn.begin_interval_time, 'YYYY-MM-DD HH24:MI') BeginTime,
To_char(sn.end_interval_time, 'YYYY-MM-DD HH24:MI') EndTime,
cur_stat.VALUE curval,
prev_stat.VALUE prevval,
ROUND(( cur_stat.VALUE - prev_stat.VALUE )/1024/1024,2) "UNDO Gen(MB/Hour)"
FROM dba_hist_snapshot sn,
(SELECT snap_id, VALUE
FROM dba_hist_sysstat
WHERE stat_name = 'undo change vector size') cur_stat,
(SELECT snap_id,VALUE
FROM dba_hist_sysstat
WHERE stat_name = 'undo change vector size') prev_stat
WHERE sn.snap_id = cur_stat.snap_id
AND cur_stat.snap_id = prev_stat.snap_id + 1
AND sn.begin_interval_time > sysdate-3
ORDER BY 1;
下面给出的是一个简化的运行结果：
CURID PREVID BEGINTIME ENDTIME CURVAL PREVVAL UNDO Gen(MB/Hour)
---------- ---------- ---------------- ---------------- ------------------ ------------------ -----------------
14367 14366 2010-05-14 17:00 2010-05-14 18:00 1193469580 1066233552 121.34
14368 14367 2010-05-14 18:00 2010-05-14 19:00 1208173452 1193469580 14.02
14369 14368 2010-05-14 19:00 2010-05-14 20:00 1426694108 1208173452 208.4
14370 14369 2010-05-14 20:00 2010-05-14 21:00 1434018356 1426694108 6.98
14371 14370 2010-05-14 21:00 2010-05-14 22:00 1442190796 1434018356 7.79
14372 14371 2010-05-14 22:00 2010-05-14 23:00 1449998628 1442190796 7.45
14373 14372 2010-05-14 23:00 2010-05-15 00:00 1460678616 1449998628 10.19
... 此处省略部分数据 ...
注：上面提供的语句实际可以说是一个通用的，只需要将其中’undo change vector size’换成别的（比如说’redo size’）就能统计相应项目的变化率了。
top延伸阅读
- Statistics Descriptions
- V$MYSTAT @ 11gR2 Reference
- V$SESSTAT @ 11gR2 Reference
- V$SYSSTAT @ 11gR2 Reference
- Oracle9i&10g编程艺术:深入数据库体系结构
作者：马齿苋 | 链接：http://www.dbabeta.com/2010/undo-management_undo-generation-stat.html
每一次统计都需要这么繁复的步骤吗