如何计算某个sql语句所产生的redo和undo大小
发布网友
发布时间：2022-04-20 05:08
共1个回答
热心网友
时间：2022-04-10 15:54
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 wner='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 wner='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 al;
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 wner='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 al;
27355556-27301552
-----------------
54004
我们可以看到，此时的54004 跟最开始的50100算是比较接近了。
SQL> SELECT xisn,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 xisn,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 al;
28*8192
----------
229376
从上我们可以看到该delete语句所产生的undo 大小是229376 BYTE。
