如何计算某个sql语句所产生的redo和undo大小？

下午，群中一个网友提出了如下一个问题，如下：


7's Life(58410752)  16:15:19
能计算 一个insert产生 的 redo 和 undo 大小吗  

Roger  16:15:42
可以

7's Life(58410752)  16:16:08
R 大师  请指点一下  
呵呵  
不会是 使用 trace 文件吧 


回答该问题其实非常的简单，花了20分钟做了如下简单的测试，供大家参考。

SQL> create table killdb as select * from dba_objects;

Table created.

SQL> select count(*) from killdb;

  COUNT(*)
----------
     50100

SQL> set autotrace traceonly statistics
SQL> set lines 150
SQL> update  killdb 
  2     set owner='www.killdb.com' 
  3   where object_id >1000 and object_id <1200;

199 rows updated.


Statistics
----------------------------------------------------------
         51  recursive calls
        208  db block gets
        798  consistent gets
          0  physical reads
      53908  redo size   
        668  bytes sent via SQL*Net to client
        619  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        199  rows processed

此时我们知道这个update语句会产生53908 byte的redo 日志。当然这个是sql未执行前就进行的计算，
如果要计算某个sql执行完毕以后所产生的redo size，我们还可以通过查询v$mystat试图获得结果，如下：

SQL> set autot off
SQL> select a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size';

NAME                       VALUE
--------------------- ----------
redo size                5807132

SQL> update  killdb 
  2     set owner='www.killdb.com' 
  3   where object_id >1000 and object_id <1200;

199 rows updated.

SQL> commit;

Commit complete.

SQL> select a.name, b.value
  2    from v$statname a, v$mystat b
  3   where a.statistic# = b.statistic#
  4     and a.name = 'redo size';

NAME                       VALUE
--------------------- ----------
redo size                5829644

SQL> select 5829644 - 5807132 from dual;

5829644-5807132
---------------
          22512

我们可以明显的看到，通过查询v$mystat 得出的结果跟前面通过看sql执行计划统计信息
结果有较大的差别，区别在哪儿呢？

这是session级别的，因为还涉及到一些递归的操作，也会产生redo，所以上面单纯的查询
session的redo产生大小，并不准确，我们应该查询整个db instacne的，如下：

SQL> create table killdb2 as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select name,value from v$sysstat where name = 'redo size';

NAME                       VALUE
--------------------- ----------
redo size               27301552

SQL> update killdb2 
  2     set owner='www.killdb.com' 
  3   where object_id >1000 and object_id <1200;

199 rows updated.

SQL> commit;

Commit complete.

SQL> select name,value from v$sysstat where name = 'redo size';

NAME                       VALUE
--------------------- ----------
redo size               27355556

SQL> select 27355556 - 27301552 from dual;

27355556-27301552
-----------------
            54004

我们可以看到，此时的54004 跟最开始的50100算是比较接近了。

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

no rows selected

SQL> select ubafil,ubablk,start_ubablk,used_ublk from v$transaction;

no rows selected

SQL> delete from killdb where rownum < 1000;

999 rows deleted.

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
        10         39        354       1256          2         26

SQL> select ubafil,ubablk,start_ubablk,used_ublk from v$transaction;

    UBAFIL     UBABLK START_UBABLK  USED_UBLK
---------- ---------- ------------ ----------
         2       1256         1229         28

SQL> conn /as sysdba
Connected.

SQL> select sum(KTUXESIZ) 
  2    from x$ktuxe 
  3   where KTUXEUSN=10 and KTUXESLT=39 and KTUXESQN=354;

SUM(KTUXESIZ)
-------------
           28

SQL> select 28*8192 from dual;

   28*8192
----------
    229376

从上我们可以看到该delete语句所产生的undo 大小是229376 BYTE。