表段、索引段上的LOGGING与NOLOGGING

--====================================

-- 表段、索引段上的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)

Oracle 归档日志

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 冷备份

Oracle 热备份

Oracle 备份恢复概念

Oracle 实例恢复

Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考：

RMAN 概述及其体系结构

RMAN 配置、监控与管理

RMAN 备份详解

RMAN 还原与恢复

有关Oracle体系结构请参考：

Oracle 实例和Oracle数据库(Oracle体系结构)

Oracle 表空间与数据文件

Oracle 密码文件

Oracle 参数文件

Oracle 数据库实例启动关闭过程

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 归档日志