redo文件大小与insert速度

最新推荐文章于 2024-07-28 03:21:51 发布

原创最新推荐文章于 2024-07-28 03:21:51 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

本文通过一系列实验展示了Oracle数据库中使用NOLOGGING选项的效果，包括对比不同设置下的redo日志大小变化，调整redo log文件大小及进行性能比较。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

实验完毕