关于Oracle的UNDO,REDO和archive log

比较INSERT,UPDATE,DELETE产生UNDO和REDO大小,一般情况：

UNDO: INSERT < UPDATE < DELETE

REDO: INSERT < DELETE < UPDATE

DDL既产生UNDO也产生REDO.

SELECT不产生UNDO和REDO.

 

最小化REDO的方法:

1.使用Nologging

2.DIRECT PATH INSERT

3.SQL*Loader

4.使用临时表

5.在DIRECT LOAD时,如果有索引,需要先把索引改成unusable状态,产生数据之后使用Nologging重建。

 

redo logs VS. archive log

归档日志archive log是当数据库运行在归档模式下时，一个redo log file(group)写满后，由ARCn进程将重做日志的内容备份到归档日志文件下，然后这个redo log file(group)才能被下一次使用。

 

-- 测试表
CREATE TABLE TEST(ID NUMBER,NAME VARCHAR2(20)); 
-- 计算 redo 大小
CREATE OR REPLACE FUNCTION GET_STAT_VAL(P_NAME VARCHAR2) RETURN NUMBER IS
  L_VAL NUMBER;
BEGIN
  SELECT B.VALUE
    INTO L_VAL
    FROM V$STATNAME A, V$MYSTAT B
   WHERE A.STATISTIC# = B.STATISTIC#
     AND A.NAME = P_NAME;
  RETURN L_VAL;
END GET_STAT_VAL;

-- INSERT: 在Undo中记录插入行ROWID.
SQL> SET SERVEROUTPUT ON
SQL> variable redo number;
SQL> variable undo number;
SQL> exec :redo := GET_STAT_VAL('redo size');
SQL> exec :undo := GET_STAT_VAL('undo change vector size');
SQL> INSERT INTO TEST VALUES(1,'TEST1');
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('undo change vector size') - :undo)||' byes of undo generated!');
SQL> COMMIT;
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('redo size') - :redo)||' byes of redo generated!');

-- UPDATE: 在Undo中记录被更新列的前镜像和被更新行ROWID.
SQL> SET SERVEROUTPUT ON
SQL> variable redo number;
SQL> variable undo number;
SQL> exec :redo := GET_STAT_VAL('redo size');
SQL> exec :undo := GET_STAT_VAL('undo change vector size');
SQL> UPDATE TEST SET NAME = 'TEST TEST' WHERE ID = 1;
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('undo change vector size') - :undo)||' byes of undo generated!');
SQL> COMMIT;
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('redo size') - :redo)||' byes of redo generated!');

-- DELETE: 在Undo中记录被删除行所有列的前镜像和其ROWID.
SQL> SET SERVEROUTPUT ON
SQL> variable redo number;
SQL> variable undo number;
SQL> exec :redo := GET_STAT_VAL('redo size');
SQL> exec :undo := GET_STAT_VAL('undo change vector size');
SQL> DELETE TEST WHERE ID = 1;
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('undo change vector size') - :undo)||' byes of undo generated!');
SQL> COMMIT;
SQL> exec DBMS_OUTPUT.PUT_LINE((GET_STAT_VAL('redo size') - :redo)||' byes of redo generated!');