如何计算某个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试图获得结果，如下：
QL> 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。

原文地址：http://www.killdb.com/2011/11/15/how_to_calculate_the_size_of_redo_and_undo_generated_by_a_sql_statement.html