redo文件大小与insert速度

SQL> set serverout ON;


DROSQL> SQL> P TABLE TEST PURGE;
CREATE TABLE TEST AS SELECT * FROM scott.test WHERE 1=2;
DECLARE
  V_VALUE1 V$SYSSTAT.VALUE%TYPE;
  V_VALUE2 V$SYSSTAT.VALUE%TYPE;
  V_M      NUMBER;
BEGIN
  SELECT VALUE INTO V_VALUE1 FROM V$SYSSTAT WHERE NAME = 'redo size';
  INSERT INTO TEST SELECT TEST.* FROM SCOTT.TEST, T10;
  COMMIT;
  SELECT VALUE INTO V_VALUE2 FROM V$SYSSTAT WHERE NAME = 'redo size';
  V_M := ROUND((V_VALUE2 - V_VALUE1) / 1024 / 1024, 2);
  DBMS_OUTPUT.PUT(V_M);
  DBMS_OUTPUT.PUT_LINE('M');
END;
表已删除。


SQL> 
表已创建。


SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14  /
81.36M


PL/SQL 过程已成功完成。


SQL> 
DROPSQL>  TABLE TEST PURGE;
CREATE TABLE TEST AS SELECT * FROM scott.test WHERE 1=2;
ALTER TABLE TEST NOLOGGING;
DECLARE
  V_VALUE1 V$SYSSTAT.VALUE%TYPE;
  V_VALUE2 V$SYSSTAT.VALUE%TYPE;
  V_M      NUMBER;
BEGIN
  SELECT VALUE INTO V_VALUE1 FROM V$SYSSTAT WHERE NAME = 'redo size';
  INSERT INTO TEST SELECT TEST.* FROM SCOTT.TEST, T10;
  COMMIT;
  SELECT VALUE INTO V_VALUE2 FROM V$SYSSTAT WHERE NAME = 'redo size';
  V_M := ROUND((V_VALUE2 - V_VALUE1) / 1024 / 1024, 2);
  DBMS_OUTPUT.PUT(V_M);
  DBMS_OUTPUT.PUT_LINE('M');
END;
/

表已删除。
SQL> 
表已创建。
SQL> 
表已更改。


SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14  80.53M
结论1，归档模式下NOLOGGING效果不大

PL/SQL 过程已成功完成。

SQL> set serverout ON

SET timing ON


DROP TABLE TEST PURGE;
CREATE TABLE TEST AS SELECT * FROM scott.test WHERE 1=2;
ALTER TABLE TEST NOLOGGING;
DECLARE
  V_VALUE1 V$SYSSTAT.VALUE%TYPE;
  V_VALUE2 V$SYSSTAT.VALUE%TYPE;
SQL>   V_M    SQL>   NUMBER;
SQL> BEGIN
SQL>   SELECT VALUE INTO V_VALUE1 FROM V$SYSSTAT WHERE NAME = 'redo size';
  INSERT INTO TEST SELECT TEST.* FROM SCOTT.TEST, T100 WHERE t100.id <=30;
  COMMIT;
  SELECT VALUE INTO V_VALUE2 FROM V$SYSSTAT WHERE NAME = 'redo size';
  V_M := ROUND((V_VALUE2 - V_VALUE1) / 1024 / 1024, 2);
  DBMS_OUTPUT.PUT(V_M);
  DBMS_OUTPUT.PUT_LINE('M');
END;
/
表已删除。

已用时间:  00: 00: 08.26
SQL> 
表已创建。

已用时间:  00: 00: 00.03
SQL> 
表已更改。

已用时间:  00: 00: 00.03
244.2M

PL/SQL 过程已成功完成。
已用时间:  00: 00: 41.50
SQL> select bytes/1024/1024 from v$log;
BYTES/1024/1024
---------------
    50
    50
    50

已用时间:  00: 00: 00.01

此时查看alert_orcl.log会发现

Thread 1 cannot allocate new log, sequence 27
Checkpoint not complete

我们来改变redolog文件的大小

SQL> alter system checkpoint;
系统已更改。

已用时间:  00: 00: 00.11
SQL> select group#,status,bytes/1024/1024 M from v$log;
    GROUP# STATUS     M
---------- ---------------- ----------
1 INACTIVE    50
2 INACTIVE    50
3 CURRENT    50


已用时间:  00: 00: 00.03
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
数据库已更改。

已用时间:  00: 00: 00.06
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 2
*
第 1 行出现错误:
ORA-01567: 删除日志 2 时将为实例 orcl (线程 1) 保留两个以下的日志文件
ORA-00312: 联机日志 2 线程 1: 'J:\ORCL\REDO02.LOG'

已用时间:  00: 00: 00.14
SQL> alter database add logfile group 1 'J:\ORCL\REDO01.LOG' size 500m;
数据库已更改。

已用时间:  00: 00: 10.60
SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
数据库已更改。

已用时间:  00: 00: 00.04
SQL> alter database add logfile group 2 'J:\ORCL\REDO02.LOG' size 500m;
数据库已更改。

已用时间:  00: 00: 10.84
SQL> ALTER SYSTEM SWITCH LOGFILE
  2  /
系统已更改。

已用时间:  00: 00: 00.09
SQL> alter system checkpoint;
系统已更改。

已用时间:  00: 00: 00.17
SQL> select group#,status,bytes/1024/1024 M from v$log;
    GROUP# STATUS     M
---------- ---------------- ----------
1 CURRENT   500
2 UNUSED   500
3 INACTIVE    50

已用时间:  00: 00: 00.03
SQL> ALTER DATABASE DROP LOGFILE GROUP 3;
数据库已更改。

已用时间:  00: 00: 00.07
SQL> alter database add logfile group 3 'J:\ORCL\REDO03.LOG' size 500m;
数据库已更改。

已用时间:  00: 00: 10.85
SQL> select group#,status,bytes/1024/1024 M from v$log;
    GROUP# STATUS     M
---------- ---------------- ----------
1 CURRENT   500
2 UNUSED   500
3 UNUSED   500
已用时间:  00: 00: 00.03
SQL> 
DROP TABLE TSQL> EST PURGE;
CREATE TABLE TEST AS SELECT * FROM scott.test WHERE 1=2;
ALTER TABLE TEST NOLOGGING;
DECLARE
  V_VALUE1 V$SYSSTAT.VALUE%TYPE;
  V_VALUE2 V$SYSSTAT.VALUE%TYPE;
  V_M      NUMBER;
BEGIN
  SELECT VALUE INTO V_VALUE1 FROM V$SYSSTAT WHERE NAME = 'redo size';
  INSERT INTO TEST SELECT TEST.* FROM SCOTT.TEST, T100 WHERE t100.id <=30;
  COMMIT;
  SELECT VALUE INTO V_VALUE2 FROM V$SYSSTAT WHERE NAME = 'redo size';
  V_M := ROUND((V_VALUE2 - V_VALUE1) / 1024 / 1024, 2);
  DBMS_OUTPUT.PUT(V_M);
  DBMS_OUTPUT.PUT_LINE('M');
END;
/
表已删除。
已用时间:  00: 00: 05.09
SQL> 
表已创建。
已用时间:  00: 00: 00.01
SQL> 
表已更改。
已用时间:  00: 00: 00.03
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14  240.54M
PL/SQL 过程已成功完成。
已用时间:  00: 00: 06.54

时间由  00: 00: 41.50 变为 00: 00: 06.54

实验完毕