分析Oracle的重做信息

DROP TABLE a;
CREATE TABLE A
(
V CHAR(2000)
);
--先确认每行的大小
DECLARE
ln_ave_row_len NUMBER;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||USER||'.A';
FOR i IN 1..1000 LOOP
    INSERT INTO a VALUES(i||'');
END LOOP;
COMMIT;
dbms_stats.gather_table_stats(USER,'A');
SELECT avg_row_len INTO ln_ave_row_len FROM dba_tab_statistics a WHERE table_name='A';
dbms_output.put_line('平均行长:'||ln_ave_row_len);
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||USER||'.A';
END;

DECLARE
ln_redo_size_pre NUMBER DEFAULT -1;
ln_redo_size_aft NUMBER DEFAULT -1;
BEGIN
--
dbms_output.put_line('测试插入');
SELECT b.VALUE INTO ln_redo_size_pre FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
FOR i IN 1..1000 LOOP
    INSERT INTO a VALUES(i||'');
END LOOP;
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交前：插入1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 
COMMIT;
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交后：插入1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 

dbms_output.put_line('测试更新');
SELECT b.VALUE INTO ln_redo_size_pre FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
UPDATE a SET v='a';
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交前：更新1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 
COMMIT;
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交后：更新1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 

 

dbms_output.put_line('测试删除');
SELECT b.VALUE INTO ln_redo_size_pre FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
DELETE FROM a;
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交前：删除1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 
COMMIT;
SELECT b.VALUE INTO ln_redo_size_aft FROM v$statname a,v$mystat b
WHERE a.STATISTIC#=b.STATISTIC#
  AND a.NAME='redo size';
dbms_output.put_line('提交后：删除1000行：'||(ln_redo_size_aft-ln_redo_size_pre)); 
END;

结果：
平均行长:2001

测试插入
提交前：插入1000行：2357812
提交后：插入1000行：2357936
测试更新
提交前：更新1000行：6568952
提交后：更新1000行：6569076
测试删除
提交前：删除1000行：2369208
提交后：删除1000行：2369332