表段、索引段上的LOGGING与NOLOGGING

﹏ヽ暗。殇╰゛Y 2022-05-15 03:28 70阅读 0赞

\--====================================

\-- 表段、索引段上的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 _ONLINE LOG FILE]

[Oracle 归档日志][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"

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)][LOGGING _FORCE LOGGING _NOLOGGING]

四、更多参考

有关闪回特性请参考

[Oracle 闪回特性(FLASHBACK DATABASE)][Oracle _FLASHBACK DATABASE]

[Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)][Oracle _FLASHBACK DROP _ RECYCLEBIN]

[Oracle 闪回特性(Flashback Query、Flashback Table)][Oracle _Flashback Query_Flashback Table]

[Oracle 闪回特性(Flashback Version、Flashback Transaction)][Oracle _Flashback Version_Flashback Transaction]

有关基于用户管理的备份和备份恢复的概念请参考：

[Oracle 冷备份][Oracle 1]

[Oracle 热备份][Oracle 2]

[Oracle 备份恢复概念][Oracle 3]

[Oracle 实例恢复][Oracle 4]

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

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

[RMAN 概述及其体系结构][RMAN]

[RMAN 配置、监控与管理][RMAN 1]

[RMAN 备份详解][RMAN 2]

[RMAN 还原与恢复][RMAN 3]

有关Oracle体系结构请参考：

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

[Oracle 表空间与数据文件][Oracle 6]

[Oracle 密码文件][Oracle 7]

[Oracle 参数文件][Oracle 8]

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

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

[Oracle 控制文件(CONTROLFILE)][Oracle _CONTROLFILE]

[Oracle 归档日志][Oracle]

[Oracle _ONLINE LOG FILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/20/5749556.aspx
[Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/08/03/5784713.aspx
[LOGGING _FORCE LOGGING _NOLOGGING]: http://blog.csdn.net/robinson_0612/archive/2011/01/12/6130866.aspx
[Oracle _FLASHBACK DATABASE]: http://blog.csdn.net/robinson_0612/archive/2010/12/27/6100429.aspx
[Oracle _FLASHBACK DROP _ RECYCLEBIN]: http://blog.csdn.net/robinson_0612/archive/2010/12/29/6105327.aspx
[Oracle _Flashback Query_Flashback Table]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112607.aspx
[Oracle _Flashback Version_Flashback Transaction]: http://blog.csdn.net/robinson_0612/archive/2011/01/02/6112981.aspx
[Oracle 1]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791597.aspx
[Oracle 2]: http://blog.csdn.net/robinson_0612/archive/2010/08/16/5816012.aspx
[Oracle 3]: http://blog.csdn.net/robinson_0612/archive/2010/08/05/5791585.aspx
[Oracle 4]: http://blog.csdn.net/robinson_0612/archive/2010/07/27/5768233.aspx
[Oracle 5]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041217.aspx
[RMAN]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032525.aspx
[RMAN 1]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032597.aspx
[RMAN 2]: http://blog.csdn.net/robinson_0612/archive/2010/11/24/6032739.aspx
[RMAN 3]: http://blog.csdn.net/robinson_0612/archive/2010/11/28/6041247.aspx
[Oracle _Oracle_Oracle]: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx
[Oracle 6]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611738.aspx
[Oracle 7]: http://blog.csdn.net/robinson_0612/archive/2010/05/20/5611672.aspx
[Oracle 8]: http://blog.csdn.net/robinson_0612/archive/2010/05/05/5559174.aspx
[Oracle 9]: http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx
[Oracle _CONTROLFILE]: http://blog.csdn.net/robinson_0612/archive/2010/07/15/5737527.aspx