Oracle redo日志产生量测试及比较

115次阅读

共计 16697 个字符，预计需要花费 42 分钟才能阅读完成。

Oracle 数据库日志有几种模式，强制日志、非强制日志模式、对象级别等！通常在数据库模式设置强制后，余下的不管如何都会强制日志！现在我们就 oracle 数据库日志模式产生的日志量进行比较测试，在各种模式下日志产生的情况！

日志记录模式的转换
a. 数据库从非强制日子模式切换到强制日志模式
SQL>alter database force logging;
b. 数据库从强制日志模式切换到非强制日志模式
SQL>alter database noforce logging;
c. 表空间级别从强制日志模式切换到非强制日志模式
SQL>alter tablespace tablespacename noforce logging;
d. 表空间级别从非强制日志模式切换到强制日志模式
SQL>alter tablespace tablespacename force logging;
e. 对象级别日志记录模式
SQL>alter tablet mytest nologging; – 不记录日志模式
SQL>alter tablet mytest logging; – 采用日志记录模式

一、表段，索引段上使用一般 DDL，DML 时，LOGGING 与 NOLOGGING 情况
1. 查看数据库的归档模式
有关设置日志归档模式的问题，请参考：
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 归档日志

SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR
———— —
ARCHIVELOG NO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/mytest/arch
Oldest online log sequence 1024
Next log sequence to archive 1025
Current log sequence 1025

SQL> select tablespace_name,logging,force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FOR
—————————— ——— —
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
PERFSTAT LOGGING NO

2. 使用如下语句进行查询

SQL >SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

3. 在归档模式下比较表段上的 NOLOGGING 与 LOGGING
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———-
redo size 1644

SQL> CREATE TABLE mytest_nolog NOLOGGING AS SELECT * FROM dba_objects; –nologging 模式创建表

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———-
redo size 70064

SQL> select 1644 last,70064 as cur,(70064-1644) diff from dual;– 使用 nologging 模式建表产生的 redo size 为

LAST CUR DIFF
———- ———- ———-
1644 70064 68420

SQL> CREATE TABLE mytest LOGGING AS SELECT * FROM dba_objects; – 使用 logging 模式来创建表

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———-
redo size 1344112

SQL> select 70064 last,1344112 as cur,(1344112-70064) diff from dual;

LAST CUR DIFF
———- ———- ———-
70064 1344112 1274048 —- 查看 logging 模式产生的 redo size 为 -68420=1274048, 比 nologging 日志模, 有 19 倍多！

SQL> select table_name,logging from user_tables where table_name like ‘MYTEST%’;– 查看创建表的日志记录模式

TABLE_NAME LOG
—————————— —
MYTEST YES
MYTEST_NOLOG NO

4. 基于索引来比较 redo size(同样是在归档模式下)
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo_size

NAME VALUE
————— ———-
redo size 1140

SQL> create index idx_mytest on mytest(object_id); – 基于表 mytest 来创建索引

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo_size

NAME VALUE
————— ———- – 基于 loggiing 模式，创建索引产生的 redo size 为 221600-1140=220460
redo size 221600

SQL> alter index idx_mytest rebuild; – 重建索引

SQL> select 221600 last,448132 cur,448132-221600 diff from dual;

LAST CUR DIFF
———- ———- ———-
221600 448132 226532 – 重建索引产生的日志比直接建还要多，主要是重建过程还有一个对旧索引的删除

SQL> alter index idx_mytest rebuild nologging; – 使用 nologging 重建索引。
– 也可以在创建索引的时候直接使用 nologging 关键字
– 如：create index idx_mytest_nolog mytest_nolog(object_id) nologging
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 基于 nologging 日志模式重建索引产生的 redo size 为 469160-448132=21028
redo size 469160

5. 非归档模式下的 LOGGING 与 NOLOGGING

SQL> drop table mytest purge;

SQL> drop table mytest_nolog purge;

SQL> select log_mode,force_logging from v$database; – 切换日志到非归档模式后，下面是查询的结果

LOG_MODE FOR
———— —
NOARCHIVELOG NO

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———-
redo size 1688
SQL> create table mytest as select * from dba_objects; – 创建表对象，使用 logging 日志记录模式

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 使用 logging 日志记录模式，创建表对象之后产生的 redo size 为 -1688 =68548
redo size 70236

SQL> create table mytest_nolog nologging as select * from dba_objects; – 创建表对象，使用 nologging 日志记录模式

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 使用 nologging 日志记录模式创建表对象之后产生的 redo size 为 135464-70236=65228

redo size 135464

6. 小结：
使用 logging 与 nologging 来创建对象或执行 DML 时
对于非归档模式下，其产生的日志信息(redo size) 相差的并不大
对于归档模式下，logging 模式产生的日志将远远大于使用 nologging 模式产生的日志量

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2016-06/132016p2.htm

现在我们通过普通的 insert，append，以及创建表的方式进行比较

1. 数据库运行在非归档模式下
a. 使用 logging 模式创建表
SQL> select log_mode from v$database;

LOG_MODE
————
NOARCHIVELOG

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———-
redo size 1764

SQL> create table mytest as select * from dba_objects where 1=0;

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 建表产生的 redo 量 23908-1764=22144
redo size 23908

SQL> insert into mytest select * from dba_objects;

11634 rows created.
Elapsed: 00:00:00.36

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 直接使用 insert 时产生的 redo 量 1281060-23908=1257152
redo size 1281060

SQL> insert /*+ append */ into mytest select * from dba_objects;

11634 rows created.
Elapsed: 00:00:00.26

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 使用 append 模式时产生的 redo 量 1284740-1281060=3680
redo size 1284740 – 普通 insert 比使用 append insert 多产生 1257152/3680=341 倍 redo

b. 使用 nologging 模式创建表
SQL> create table mytest_nolog nologging as select * from dba_objects where 1=0;

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 使用 nologging 创建空表 mytest_nolog 时产生的日志量 1305812-1284740=21072
redo size 1305812

SQL> insert into mytest_nolog select * from dba_objects;

11635 rows created.
Elapsed: 00:00:00.21
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 使用普通 insert 插入记录产生的日志量 2562664-1305812=1256852
redo size 2562664

SQL> insert /* +append */ into mytest_nolog select * from dba_objects;

11635 rows created.
Elapsed: 00:00:00.18
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 使用 append 模式时产生的 redo 量 3766404-2562664=1203740
redo size 3766404

c.redo 的比较
在具有 logging 属性对象中，使用 append 模式时产生的 redo 量 1284740-1281060=3680，
普通 insert 比使用 append insert 多产生 /3680=341 倍 redo
在具有 nologging 属性对象中, 使用 append insert 模式与普通 insert 模式产生的 redo 量相差不太大,
append insert 模式为，而普通的 insert 模式为

2. 数据库运行在归档模式下
a. 前期处理
SQL> drop table mytest purge;

SQL> drop table mytest_nolog purge;

SQL> select log_mode from v$database;

LOG_MODE
————
ARCHIVELOG

b. 创建表对象并进行比较
SQL> create table mytest as select * from dba_objects where 1=0; –logging 模式创建表对象

SQL> create table mytest_nolog nologging as select * from dba_objects where 1=0;–nologging 模式创建表对象

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 查看当前的 redo size 为 46844
redo size 46844

SQL> insert into mytest select * from dba_objects; – 为表 mytest 使用常规 insert 插入记录

11598 rows created.
Elapsed: 00:00:00.25

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 表 mytest 使用常规 insert 插入记录产生的 redo size 为 1299120-46844=1252276
redo size 1299120

SQL> insert into mytest_nolog select * from dba_objects; – 为表 mytest_nolog 使用常规 insert 插入记录

11598 rows created.
Elapsed: 00:00:00.28

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 表 mytest_nolog 使用常规 insert 插入记录产生的 redo size 为 2552880-1299120=1253760
redo size 2552880

SQL> insert /* +append */ into mytest select * from dba_objects;– 表 mytest 使用 insert append 方式

11598 rows created.
Elapsed: 00:00:00.20

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 表 mytest 使用 insert append 插入记录产生的 redo size 为 3750852-2552880=1197972
redo size 3750852

SQL> insert /* +append */ into mytest_nolog select * from dba_objects;– 表 mytest_nolog 使用 insert append��式

11598 rows created.
Elapsed: 00:00:00.18

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———- – 表 mytest_nolog 使用 insert append 插入记录产生的 redo size 为 4948764-3750852=1197912
redo size 4948764

3. 小结
对于表对象插入记录时，使用普通 insert 与使用 append 方式比较
在非归档模式下，表对象在使用 nologging 模式时，两者产生的日志量相差不大，而使用 logging 模式时，常规 insert 的日志量远大于 append 方式。
在归档模式下，表对象使用 logging 模式，两者产生的日志量相差不大。而表对象使用 nologging 模式时，则使用 append 将使得性能有所提高。
在非归档模式下的 inesrt append 操作将是性能最高的。
比如我们在做 dataguard、流复制、第三方工具同步时，针对数据库开启了 force logging 的方式，各种特征的表，在插入时，日志量相差是不大的

4.direct insert append 使用时的注意事项
a. 当使用 insert into … values 语句时，不能够使用 append 方式
b.append 方式为批量插入的记录，因此新插入的记录被存储在 hwm 之上，对于 hwm 之下空闲块将不会被使用。
c. 在 append 方式插入记录后，要执行 commit，才能对表进行查询。否则会出现错误：
ORA-12838: cannot read/modify an object after modifying it in parallel
d. 在归档模式下，表对象具有 nologging 属性，且以 append 方式批量添加记录，才会显著减少 redo 数量。
e. 在非归档模式下，表对象即便具有 logging 属性，也可减少 redo 数量。
f. 对于表上具有索引的表对象，如果新增的记录数量为整个表的很少一部分，则直接以 append 方式批量添加记录，如果原表记录很少，
实时性要求不是很高，而新增记录很多，可以先删除索引，在使用 append 方式追加记录，最后再创建索引。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-06/132016.htm

Oracle 数据库日志有几种模式，强制日志、非强制日志模式、对象级别等！通常在数据库模式设置强制后，余下的不管如何都会强制日志！现在我们就 oracle 数据库日志模式产生的日志量进行比较测试，在各种模式下日志产生的情况！

日志记录模式的转换
a. 数据库从非强制日子模式切换到强制日志模式
SQL>alter database force logging;
b. 数据库从强制日志模式切换到非强制日志模式
SQL>alter database noforce logging;
c. 表空间级别从强制日志模式切换到非强制日志模式
SQL>alter tablespace tablespacename noforce logging;
d. 表空间级别从非强制日志模式切换到强制日志模式
SQL>alter tablespace tablespacename force logging;
e. 对象级别日志记录模式
SQL>alter tablet mytest nologging; – 不记录日志模式
SQL>alter tablet mytest logging; – 采用日志记录模式

一、表段，索引段上使用一般 DDL，DML 时，LOGGING 与 NOLOGGING 情况
1. 查看数据库的归档模式
有关设置日志归档模式的问题，请参考：
Oracle 联机重做日志文件(ONLINE LOG FILE)
Oracle 归档日志

SQL> select log_mode,force_logging from v$database;

LOG_MODE FOR
———— —
ARCHIVELOG NO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/mytest/arch
Oldest online log sequence 1024
Next log sequence to archive 1025
Current log sequence 1025

SQL> select tablespace_name,logging,force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FOR
—————————— ——— —
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS LOGGING NO
PERFSTAT LOGGING NO

2. 使用如下语句进行查询

SQL >SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

3. 在归档模式下比较表段上的 NOLOGGING 与 LOGGING
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———-
redo size 1644

SQL> CREATE TABLE mytest_nolog NOLOGGING AS SELECT * FROM dba_objects; –nologging 模式创建表

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;

NAME VALUE
————— ———-
redo size 70064

SQL> select 1644 last,70064 as cur,(70064-1644) diff from dual;– 使用 nologging 模式建表产生的 redo size 为

LAST CUR DIFF
———- ———- ———-
1644 70064 68420

SQL> CREATE TABLE mytest LOGGING AS SELECT * FROM dba_objects; – 使用 logging 模式来创建表

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———-
redo size 1344112

SQL> select 70064 last,1344112 as cur,(1344112-70064) diff from dual;

LAST CUR DIFF
———- ———- ———-
70064 1344112 1274048 —- 查看 logging 模式产生的 redo size 为 -68420=1274048, 比 nologging 日志模, 有 19 倍多！

SQL> select table_name,logging from user_tables where table_name like ‘MYTEST%’;– 查看创建表的日志记录模式

TABLE_NAME LOG
—————————— —
MYTEST YES
MYTEST_NOLOG NO

4. 基于索引来比较 redo size(同样是在归档模式下)
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo_size

NAME VALUE
————— ———-
redo size 1140

SQL> create index idx_mytest on mytest(object_id); – 基于表 mytest 来创建索引

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo_size

NAME VALUE
————— ———- – 基于 loggiing 模式，创建索引产生的 redo size 为 221600-1140=220460
redo size 221600

SQL> alter index idx_mytest rebuild; – 重建索引

SQL> select 221600 last,448132 cur,448132-221600 diff from dual;

LAST CUR DIFF
———- ———- ———-
221600 448132 226532 – 重建索引产生的日志比直接建还要多，主要是重建过程还有一个对旧索引的删除

SQL> alter index idx_mytest rebuild nologging; – 使用 nologging 重建索引。
– 也可以在创建索引的时候直接使用 nologging 关键字
– 如：create index idx_mytest_nolog mytest_nolog(object_id) nologging
SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 基于 nologging 日志模式重建索引产生的 redo size 为 469160-448132=21028
redo size 469160

5. 非归档模式下的 LOGGING 与 NOLOGGING

SQL> drop table mytest purge;

SQL> drop table mytest_nolog purge;

SQL> select log_mode,force_logging from v$database; – 切换日志到非归档模式后，下面是查询的结果

LOG_MODE FOR
———— —
NOARCHIVELOG NO

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———-
redo size 1688
SQL> create table mytest as select * from dba_objects; – 创建表对象，使用 logging 日志记录模式

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 使用 logging 日志记录模式，创建表对象之后产生的 redo size 为 -1688 =68548
redo size 70236

SQL> create table mytest_nolog nologging as select * from dba_objects; – 创建表对象，使用 nologging 日志记录模式

SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = ‘redo size’;; – 查看当前的 redo size

NAME VALUE
————— ———- – 使用 nologging 日志记录模式创建表对象之后产生的 redo size 为 135464-70236=65228

redo size 135464

6. 小结：
使用 logging 与 nologging 来创建对象或执行 DML 时
对于非归档模式下，其产生的日志信息(redo size) 相差的并不大
对于归档模式下，logging 模式产生的日志将远远大于使用 nologging 模式产生的日志量