oracle nologging用法

数据库 0 1041

小小草 2020年11月1日 11:37 发表

摘要：默认情况是logging，就是会记录到redo日志中，force logging是强制记录日志，nologging是尽量减少日志。FORCE LOGGING可以在数据库级别、表空间级别进行设定、而LOGGING与NOLOGGING可以在表级别设定。

一、oracle日志模式分为（logging，force logging，nologging）

默认情况是logging，就是会记录到redo日志中，force logging是强制记录日志，nologging是尽量减少日志。FORCE LOGGING可以在数据库级别、表空间级别进行设定、而LOGGING与NOLOGGING可以在表级别设定。

注：FORCE LOGGING并不比一般的LOGGING记录的日志多,数据库在FORCE LOGGING状态下,NOLOGGING选项将无效,因为NOLOGGING将破坏DATAGUARD的可恢复性.FORCE LOGGING强制数据库在任何状态下必须记录日志。

logging，force logging和nologging是只记录到redo日志中，归档不归档是另外的设置，但是如果用nologging了，那么显然就算归档的话，归档日志就少了，但是可能不能用于介质回复了，因为有些根本没有记录。

二、使用情况

1.logging模式

这是日志记录的缺省模式，无论数据库是否处于归档模式，这并不改变表空间与对象级别上的缺省的日志记录模式。对于临时表空间将不记录日志到联机重做日志文件。

2.nologing模式

此模式不是不记录日志，而是最小化日志产生的数量，通常在下列情况下使用NOLOGGING

nologing模式通常和append联合使用。

append介绍：

/*+append*/

（1） append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo

使用append，一是减少对空间的搜索；二是有可能减少redolog的产生。所以append方式会快很多，一般用于大数据量的处理。建议不要经常使用append，这样表空间会一直在高水位上，除非你这个表只插不删

论坛append解释：

请教一下，oracle中append是做什么用的。
insert /*+append*/ into table1 select * from table2
在使用了append选项以后，insert数据会直接加到表的最后面，而不会在表的空闲块中插入数据。使用append会增加数据插入的速度。

/*+APPEND*/的作用是在表的高水位上分配空间,不再使用表的extent中的空余空间
append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo

不去寻找 freelist 中的free block , 直接在table HWM 上面加入数据。

查redo大小语句：

select name,value,class from v$sysstat where name='redo size';

显示的好方法：

select (1745704-582728) redo1,(1839872-1745704) redo2 from dual;//注意，括号里其实就是两个数减法，然后写进去显示出来而已

（2）NOLOGGING与表模式，插入模式，数据库运行模式(archived/unarchived)的关系：

下面仅说明使用append方式插入数据时日志产生的情况，如使用下面的方式进行插入（这里是表是否事先设置成nologging）

insert/*+append+*/into tb_name select colnam1,colname2 from table_name;

数据库处于归档模式

当表模式为logging状态时,无论是否使用append模式,都会生成redo.当表模式为nologging状态时,只有append模式,不会生成redo。

数据库处于非归档模式

无论是在logging还是nologing的模式下，append的模式都不会生成redo,而no append模式下都会生成redo。

alter table aa nologging

alter table aa logging

insert /*+append*/ into ...nologing
select * from ...

insert /*+ append, parallel */ into ods_list_t nologging
select * from ods_list;

但这样不行:整个表可以插入,但要某一个字段则不能加入nologging
insert /*+ append, parallel */ into ods_list_t(a,b) nologging
select a,b from ods_list;

但可以这样:
insert /*+ append, parallel */ into ods_list_t nologging(a,b)
select a,b from ods_list;

create table ods_list_t nologging as select * from ods_list;

insert /*+ append, parallel */ into ods_list_t nologging
select * from ods_list;

insert /*+ Append parallel(tablename,number) */ into ods_list_t nologging
select * from ods_list;

tablename: 表名
number: 并行度

使用批量拷贝方法
set arraysize 20
set copycommit 5000
copy from username/password@oracle_name append table_name1
using select * from table_name2;

用EXP/IMP 处理大量数据

(1)给当前的两个表分别改名
alter   table   tab1   rename   to   tab11;
alter   table   tab2   rename   to   tab1;
(2)导出改名前的tab2
exp   user/pwd@...   file=...   log=...   tables=(tab1)
(3)把名字改回来
alter   table   tab1   rename   to   tab2;
alter   table   tab11   rename   to   tab1;
(4)导入数据
imp   user/pwd@...   file=...   log=...   fromuser=user   touser=user   tables=(tab1)

--------------------------------------------------

（3）真正总结（有用的）：

可以看出（这是insert /*+ append */ into ddddd nologging as select ...... from ...）

1. 不管哪种模式下append要与nologging方式联用才能达到很好的效果。

2. 非归档与归档方式，只用NOLOGGING是不起效果的。

3. 非归档下append已达到不错的效果，但不及与nologging的联用方式。

4. 归档下单append起不到效果。

另外，如果库处在FORCELOGGING模式下，此时的nologging方式是无效的，这个我也测试过
5.非归档模式下：
没有优化前    （1281372 redo size）
（1）、单一的使用nologging参数，对redo的产生没有什么影响。（1214836 redo size）
（2）、单一的使用append提示，redo的减少很显著              （43872 redo size）
（3）、nologging+append，更显著                             （1108 redo size）

6.归档模式下：
没有优化前：
（1）、单独使用nologging参数，（1231904 redo size）
（2）、单独使用append提示，（1245804 redo size）
（3）、nologging + append，（3748 redo size）

a、使用nologging参数并不代表在dml操作中，oracle不产生redo，只是对于指定表的更新数据不产生redo，但是 oracle还是要记录这些操作，所以无论怎么优化，dml操作肯定要产生redo，但是使用这些参数对redo size的影响还是非常可观的。
b、单独使用nologging参数，对redo size没有多少影响，只有和append配合时，才能产生效果。
c、单独使用append提示，对redo的产生影响很大，这是我到现在都不明白的道理，按说append是绕过freelists，直接去寻找新块，能减少对freelists的争用，为什么会少这么多redo呢？
d、归档模式和非归档模式下，参数影响不一样，尤其是单独使用append参数时，看来oracle对归档模式下出于安全考虑还是要多一些。

三、查询总结

1.查看不同级别的日志模式

查看数据库级别日志的归档模式、记录模式

SQL>select log_mode,force_logging from v$database;

LOG_MODE FORCE_LOGGING

------------ -------------

ARCHIVELOG NO

查看表空间级别的日志记录模式

SQL>select tablespace_name,logging,force_logging from dba_tablespaces;

TABLESPACE_NAME LOGGING FORCE_LOGGING

--------------- --------- -------------

SYSTEM LOGGING NO

UNDOTBS1 LOGGING NO

SYSAUX LOGGING NO

TEMP NOLOGGING NO

USERS LOGGING NO

查看对象级别的日志记录模式

scott@ORCL>select table_name,logging from user_tables;

TABLE_NAME LOG

------------------------------ ---

TB_A YES

TB_B YES

2.日志记录模式的转换

a.数据库从非强制日子模式切换到强制日志模式

sys@ORCL>alterdatabase force logging;

b.数据库从强制日志模式切换到非强制日志模式

sys@ORCL>alterdatabaseno force logging;

c.表空间级别从强制日志模式切换到非强制日志模式

sys@ORCL>alterdatabaseno force logging;

d.表空间级别从非强制日志模式切换到强制日志模式

sys@ORCL>alterdatabaseno force logging;

e.对象级别日志记录模式

sys@ORCL>altertable tb_a nologging; --不记录日志模式

sys@ORCL>altertable tb_a logging; --采用日志记录模式

注意：

（1）表是否是nologging模式，还需要查user_tables.logging字段才行。
我刚才在oracle 10g中测试,create table xxx nologging as select *....产生的表并不是nologging方式，还得手工改一下:
alter table xxxx nologging.
改完后，在归档模式,insert /*+append*/确实日志很少。

（2）在使用append 快速加载数据完成后，需要commit or roll ，才能进行进行对该表进程操作否则会报错：ORA-12838: cannot read/modify an object after modifying it in parallel错误。insert和append insert的原理上是完全不同的，insert append可以实现直接路径加载，速度比常规加载方式快很多。但有一点需要注意: insert append时在表上加“6”类型的Exclusive锁，即排它锁,为表级锁,会阻塞表上的所有DML语句。因此在有业务运行的上传环境下要慎重使用。

点赞 0 收藏(0)