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

-- 表段、索引段上的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方式追加记录,最后再创建索引。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html