undo change vector size oracle,undo相关统计

UNDO相关统计

在日常管理中，除了要用到上一篇文章中所说的那些数据字典之外，还有一个保存统计信息的数据字典是非常重要的，那就是著名的由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文档()中都能找到相应的描述。

下面说一下一些通常比较关注的统计项目。

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')||'%'

/

undo 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。

data 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数据。

同样的其他的项目也可以使用上面的方法进行验证。