本文探讨了Oracle数据库中表段和索引段使用LOGGING与NOLOGGING模式的区别，尤其是在不同归档模式下产生的redo日志大小差异。通过实验对比了这两种模式下创建表和索引、执行DML操作时的redo日志量。
--====================================
-- 表段、索引段上的LOGGING与NOLOGGING
--====================================
在有些情况下，对于表段和索引段可以采用记录日志的模式，也可以使用不记录日志的模式。如在对表段、索引段使用数据泵导入时，可以
使用NOLOGGING模式，而使用DATA GUARD或对可用性较高的场景中需要记录日志，甚至使用强制记录日志。本文介绍了在表段，索引段使用
LOGGING与NOLOGGING时产生redo的大小以及DIRECT INSERT APPEND 的使用方法。
NOLOGGING跟数据库的运行模式有关，i和i的默认安装都是非归档模式，并且自动归档默认是禁用。在安装g、g时，可以选择是否归
档。NOLOGGIING将记录少量日志信息到日志文件。如果数据库级别或表空间级别使用了FORCE LOGGING强制日志记录模式，则该选项无效。
一、表段，索引段上使用一般DDL，DML时，LOGGING与NOLOGGING情况
1.查看数据库的归档模式
有关设置日志归档模式的问题，请参考：
Oracle 联机重做日志文件(ONLINE LOG FILE)
sys@ORCL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
sys@ORCL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/bk/arch
Oldest online log sequence 50
Next log sequence to archive 51
Current log sequence 51
sys@ORCL> 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
scott@ORCL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
2.创建一个查看redo size 的视图redo_size
CREATE OR REPLACE FORCE VIEW "SYS"."REDO_SIZE"
AS
SELECT a.name,b.value
FROM v$statname a
JOIN v$mystat b
ON a.statistic# = b.statistic#
WHERE a.name = 'redo size';
sys@ORCL> create public synonym redo_size for redo_size;
sys@ORCL> grant select on redo_size to scott;
3.在归档模式下比较表段上的NOLOGGING与LOGGING
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ----------
redo size 1644
scott@ORCL> CREATE TABLE tb_obj_nolog NOLOGGING AS SELECT * FROM dba_objects; --nologging模式创建表
scott@ORCL> select 1644 last,70064 as cur,(70064-1644) diff from dual;--使用nologging模式建表产生的redo size为
LAST CUR DIFF
---------- ---------- ----------
1644 70064 68420
scott@ORCL> CREATE TABLE tb_obj_log LOGGING AS SELECT * FROM dba_objects; --使用logging模式来创建表
sscott@ORCL> select * from redo_size; --查看当前的redo size
NAME VALUE
--------------- ---------- --查看logging模式产生的redo size 为-68420=1274048,比nologging日志模
redo size 1344112 --式多出了19倍多
scott@ORCL> select table_name,logging from user_tables where table_name like 'TB_OBJ%';--查看创建表的日志记录模式
TABLE_NAME LOG
------------------------------ ---
TB_OBJ_LOG YES
TB_OBJ_NOLOG NO
4.基于索引来比较redo size(同样是在归档模式下)
scott@ORCL> select * from redo_size; --查看当前的redo_size
NAME VALUE
--------------- ----------
redo size 1140
scott@ORCL> create index idx_tb_obj_log on tb_obj_log(object_id); --基于表tb_obj_log来创建索引
scott@ORCL> select * from redo_size; --查看当前的redo_size
NAME VALUE
--------------- ---------- --基于loggiing模式，创建索引产生的redo size 为221600-1140=220460
redo size 221600
scott@ORCL> alter index idx_tb_obj_log rebuild; --重建索引
scott@ORCL> select 221600 last,448132 cur,448132-221600 diff from dual;
LAST CUR DIFF
---------- ---------- ----------
221600 448132 226532 --重建索引后产生的redo size 为226532，比直接创建时产生的redo size还要多
scott@ORCL> alter index idx_tb_obj_log rebuild nologging; --使用nologging重建索引。
--也可以在创建索引的时候直接使用nologging关键字
--如：create index idx_tb_obj_nolog tb_obj_nolog(object_id) nologging
scott@ORCL> select * from redo_size; --查看当前的redo size
NAME VALUE
--------------- ---------- --基于nologging日志模式重建索引产生的redo size为469160-448132=21028
redo size 469160
5.非归档模式下的LOGGING与NOLOGGING
scott@ORCL> drop table tb_obj_log purge;
scott@ORCL> drop table tb_obj_nolog purge;
scott@ORCL> select log_mode,force_logging from v$database; --切换日志到非归档模式后，下面是查询的结果
LOG_MODE FOR
------------ ---
NOARCHIVELOG NO
scott@ORCL> select * from redo_size; --查看当前的redo size
NAME VALUE
--------------- ----------
redo size 1688
scott@ORCL> create table tb_obj_log as select * from dba_objects; --创建表对象，使用logging日志记录模式
scott@ORCL> select * from redo_size; --查看当前的redo size
NAME VALUE
--------------- ---------- --使用logging日志记录模式，创建表对象之后产生的redo size 为-1688 =68548
redo size 70236
scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects; --创建表对象，使用nologging日志记录模式
scott@ORCL> select * from redo_size; --查看当前的redo size
NAME VALUE
--------------- ---------- --使用nologging日志记录模式创建表对象之后产生的redo size为135464-70236=65228
redo size 135464
6.小结：
使用logging与nologging来创建对象或执行DML时
对于非归档模式下，其产生的日志信息(redo size)相差的并不大
对于归档模式下，logging模式产生的日志将远远大于使用nologging模式产生的日志量
二、使用DIRECT INSERT APPEND方式的LOGGING与NOLOGGING情况
DIRECT INSERT APPEND是Oracle插入数据到数据库的一种方式之一。使用APPEND方式来导入，其实是将记录直接存放到高水位线(HWM)之上，
而不考虑高水位线之下的空闲块。
1.数据库运行在非归档模式下
a.使用logging模式创建表
scott@ORCL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ----------
redo size 1764
scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0;
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --建表产生的redo量23908-1764=22144
redo size 23908
scott@ORCL> insert into tb_obj_log select * from dba_objects;
11634 rows created.
Elapsed: 00:00:00.36
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --直接使用insert时产生的redo量1281060-23908=1257152
redo size 1281060
scott@ORCL> insert /*+ append */ into tb_obj_log select * from dba_objects;
11634 rows created.
Elapsed: 00:00:00.26
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --使用append模式时产生的redo量1284740-1281060=3680
redo size 1284740 --普通insert比使用append insert多产生1257152/3680=341倍redo
b.使用nologging模式创建表
scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --使用nologging创建空表tb_obj_nolog时产生的日志量1305812-1284740=21072
redo size 1305812
scott@ORCL> insert into tb_obj_nolog select * from dba_objects;
11635 rows created.
Elapsed: 00:00:00.21
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --使用普通insert插入记录产生的日志量2562664-1305812=1256852
redo size 2562664
scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;
11635 rows created.
Elapsed: 00:00:00.18
scott@ORCL> select * from 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.前期处理
scott@ORCL> drop table tb_obj_log purge;
scott@ORCL> drop table tb_obj_nolog purge;
sys@ORCL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
b.创建表对象并进行比较
scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0; --logging模式创建表对象
scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;--nologging模式创建表对象
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --查看当前的redo size 为46844
redo size 46844
scott@ORCL> insert into tb_obj_log select * from dba_objects; --为表tb_obj_log使用常规insert插入记录
11598 rows created.
Elapsed: 00:00:00.25
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --表tb_obj_log使用常规insert插入记录产生的redo size 为1299120-46844=1252276
redo size 1299120
scott@ORCL> insert into tb_obj_nolog select * from dba_objects; --为表tb_obj_nolog使用常规insert插入记录
11598 rows created.
Elapsed: 00:00:00.28
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --表tb_obj_nolog使用常规insert插入记录产生的redo size 为2552880-1299120=1253760
redo size 2552880
scott@ORCL> insert /* +append */ into tb_obj_log select * from dba_objects;--表tb_obj_log使用insert append方式
11598 rows created.
Elapsed: 00:00:00.20
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --表tb_obj_log使用insert append插入记录产生的redo size 为3750852-2552880=1197972
redo size 3750852
scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;--表tb_obj_nolog使用insert append方式
11598 rows created.
Elapsed: 00:00:00.18
scott@ORCL> select * from redo_size;
NAME VALUE
--------------- ---------- --表tb_obj_nolog使用insert append插入记录产生的redo size 为4948764-3750852=1197912
redo size 4948764
c.redo的比较
归档模式下，具有nologging特性的表tb_obj_nolog，使用insert append方式插入的速度最快，且日志量最小，为。而
logging特性的表tb_obj_log使用insert append方式时的日志量为，相差不是很大。对于使用普通的insert插入，则
产生的日志量差异比较大。
3.小结
对于表对象插入记录时，使用常规insert 与使用direct insert append方式比较
在非归档模式下，表对象在使用nologging模式时，两者产生的日志量相差不大，而使用logging模式时，常规insert的日志量远
大于direct insert append方式。
在归档模式下，表对象使用logging模式，两者产生的日志量相差不大。而表对象使用nologging模式时，则使用insert append
将使得性能有所提高。
在非归档模式下的inesrt append操作将是性能最高的。
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方式追加记录，最后再创建索引。
三、日志记录模式请参考
日志记录模式(LOGGING 、FORCE LOGGING 、NOLOGGING)
四、更多参考
有关闪回特性请参考
Oracle 闪回特性(FLASHBACK DATABASE)
Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle 闪回特性(Flashback Query、Flashback Table)
Oracle 闪回特性(Flashback Version、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考：
Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)
有关RMAN的恢复与管理请参考：
有关Oracle体系结构请参考：
Oracle 实例和Oracle数据库(Oracle体系结构)
Oracle 联机重做日志文件(ONLINE LOG FILE)
扫一扫
910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
