LOGGING vs NOLOGGING 及 普通插入和直接插入专题研究
前言:
很多人对LOGGING, NOLOGGING概念比较模糊,以及它们在归档和非归档模式下对普通插入和直接插入的速度,产生REDO日志的量的影响上不是很明确,本文将对此进行测试和总结.
总结:
为了清晰明了,我先把总结放在开头.
1. 普通插入不管是在归档还是非归档模式下, 不管是logging还是nologging都产生大量的REDO日志
2. 直接插入只有在归档的logging模式下产生大量的REDO日志,其它模式产生很少的REDO日志.
3. 普通插入归档模式下速度比非归档模式下慢很多.
4. 直接插入在归档logging模式下速度慢其它模式两倍.
测试:
1. Archivelog mode
在归档模式下,
1). LOGGING, NOLOGGING中直接插入对REDO的大小影响比较大,NOLOGGING 产生很少的REDO日志,而LOGGING产生很大的REDO日志.
而对于普通插入来说,影响不大.
2). 在LOGGING模式下,Direct insert /*+ append */ 和普通的insert一样会产生大量的REDO日志,只是直接插入的速度要快些.
SQL> create table t_logging as select * from dba_objects where 1=2;
Table created.
Elapsed: 00:00:00.16
SQL> select table_name, logging from dba_tables where table_name='T_LOGGING';
TABLE_NAME LOG
------------------------------ ---
T_LOGGING YES
Elapsed: 00:00:00.08
SQL> select * from redo_size;
VALUE
----------
17473724
Elapsed: 00:00:00.01
SQL> insert into t_logging select * from dba_objects; --普通insert花费4.83s并产生5733184的REDO日志
50534 rows created.
Elapsed: 00:00:04.83
SQL> select * from redo_size;
VALUE
----------
23206908
Elapsed: 00:00:00.01
SQL> select 23206908-17473724 diff from dual;
DIFF
----------
5733184
Elapsed: 00:00:00.01
SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费1.84s并产生5750044的REDO日志
50534 rows created.
Elapsed: 00:00:01.84
SQL> select * from redo_size;
VALUE
----------
28956952
Elapsed: 00:00:00.01
SQL> select 28956952-23206908 diff from dual;
DIFF
----------
5750044
Elapsed: 00:00:00.01
在NOLOGGING模式下,Direct insert /*+ append */ 才产生很少的REDO日志, 比普通insert少得多.而且速度要快得多.
SQL> select * from redo_size;
VALUE
----------
11683020
Elapsed: 00:00:00.01
SQL> create table t_nologging nologging as select * from dba_objects where 1=2;
Table created.
Elapsed: 00:00:00.19
SQL> select table_name, logging from dba_tables where table_name='T_NOLOGGING';
TABLE_NAME LOG
------------------------------ ---
T_NOLOGGING NO
Elapsed: 00:00:00.07
SQL> insert into t_nologging select * from dba_objects; --普通insert花费5.87s并产生了5757080的REDO
50533 rows created.
Elapsed: 00:00:05.87
SQL> select * from redo_size;
VALUE
----------
17440100
Elapsed: 00:00:00.01
SQL> select 17440100-11683020 diff from dual;
DIFF
-----------------
5757080
Elapsed: 00:00:00.00
SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.97s并产生11124的REDO日志
50533 rows created.
Elapsed: 00:00:00.97
SQL> select * from redo_size;
VALUE
----------
17451224
Elapsed: 00:00:00.00
SQL> select 17451224-17440100 diff from dual;
DIFF
----------
11124
Elapsed: 00:00:00.00
2. NOARCHIVELOG mode
非归档模式
1). LOGGING, NOLOGGING不管是对直接插入还是普通插入影响都不大.
但直接插入在这两种模式下都产生很少的REDO日志,速度也比归档模式下快一点点.
普通插入在这两种模式下都产生差不多一样的REDO日志,且别直接插入要多. 速度上明显比归档模式下快很多.
1.1). 在LOGGING模式下,
SQL> select * from redo_size;
VALUE
----------
19008
Elapsed: 00:00:00.06
SQL> create table t_logging as select * from dba_objects where 1=2;
Table created.
Elapsed: 00:00:00.30
SQL> select table_name, logging from dba_tables where table_name='T_LOGGING';
TABLE_NAME LOG
------------------------------ ---
T_LOGGING YES
Elapsed: 00:00:00.13
SQL> insert into t_logging select * from dba_objects; --普通insert花费0.71s并产生5758088的REDO日志
50531 rows created.
Elapsed: 00:00:00.71
SQL> select * from redo_size;
VALUE
----------
5777096
Elapsed: 00:00:00.02
SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费0.75s并产生11168的REDO日志
50531 rows created.
Elapsed: 00:00:00.75
SQL> select * from redo_size;
VALUE
----------
5788264
Elapsed: 00:00:00.02
SQL> select 5788264-5777096 diff from dual;
DIFF
----------
11168
Elapsed: 00:00:00.00
1.2) 在NOLOGGING 模式下
SQL> create table t_nologging nologging as select * from dba_objects where 1=2;
Table created.
Elapsed: 00:00:00.21
SQL> select * from redo_size;
VALUE
----------
11519140
Elapsed: 00:00:00.06
SQL> select table_name, logging from dba_tables where table_name='T_NOLOGGING';
TABLE_NAME LOG
------------------------------ ---
T_NOLOGGING NO
Elapsed: 00:00:00.07
SQL> insert into t_nologging select * from dba_objects; --普通insert花费1.35s并产生5735500的REDO日志
50532 rows created.
Elapsed: 00:00:01.35
SQL> select * from redo_size;
VALUE
----------
17254640
Elapsed: 00:00:00.05
SQL> select 17254640-11519140 diff from dual;
DIFF
----------
5735500
Elapsed: 00:00:00.00
SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.74s并产生11168的REDO日志
50532 rows created.
Elapsed: 00:00:00.74
SQL> select * from redo_size;
VALUE
----------
17265808
Elapsed: 00:00:00.02
SQL> select 17265808-17254640 diff from dual;
DIFF
----------
11168
Elapsed: 00:00:00.01
Summary:
archive mode:
logging
SQL> insert into t_logging select * from dba_objects; --普通insert花费4.83s并产生5733184的REDO日志
SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费1.84s并产生5750044的REDO日志
nologging
SQL> insert into t_nologging select * from dba_objects; --普通insert花费5.87s并产生了5757080的REDO
SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.97s并产生11124的REDO日志
noarchive mode:
logging
SQL> insert into t_logging select * from dba_objects; --普通insert花费0.71s并产生5758088的REDO日志
SQL> insert /*+ append */ into t_logging select * from dba_objects; --直接insert花费0.75s并产生11168的REDO日志
在NOLOGGING 模式下
SQL> insert into t_nologging select * from dba_objects; --普通insert花费1.35s并产生5735500的REDO日志
SQL> insert /*+ append */ into t_nologging select * from dba_objects; --直接insert花费0.74s并产生11168的REDO日志
参考文档:
http://blog.csdn.net/huanghui22/archive/2006/10/16/1337420.aspx
http://tolywang.itpub.net/post/48/427215
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/9466564/viewspace-608888/ ，如需转载，请注明出处，否则将追究法律责任。
