减少插入语句生成重做日志的方法小结

re2212

下面是根据自己的测试和别人的经验进行总结的
重做日志文件（redo log file）对Oracle数据库来说至关重要，它们是数据库的事务日志。Oracle维护着两类重做日志文件：
在线（online）重做日志文件和归档（archived）重做日志文件。这两类重做日志文件都用于恢复；其主要目的是，
万一实例失败或介质失败，它们就能派上用场。
如果数据库所在主机掉电，导致实例失败，Oracle会使用在线重做日志将系统恰好恢复到掉电之前的那个时间点。
如果磁盘驱动器出现故障（这是一个介质失败），Oracle会使用归档重做日志以及在线重做日志将该驱动器上的
数据备份恢复到适当的时间点。

归档重做日志文件实际上就是已填满的"旧"在线重做日志文件的副本。系统将日志文件填满时，
ARCH进程会在另一个位置建立在线重做日志文件的一个副本，也可以在本地和远程位置上建立多个另外的副本.
如果由于磁盘驱动器损坏或者其他物理故障而导致失败,就会用这些归档重做日志文件来执行介质恢复.
Oracle拿到这些归档重做日志文件，并把它们应用于数据文件的备份，使这些数据文件能与数据库的其余部分保持一至.
归档重做日志文件是数据库的事务历史
查询redo大小的语句
SELECT a.NAME,
b.VALUE cur_size_byte,
round(b.VALUE / 1024, 3) || 'KB' cur_size_kb
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'
数据库归档模式
数据库归档用来保存redo的日志文件副本,一般安装时默认未开启数据库的归档模式。
在NOARCHIVELOG模式的数据库中,除了数据字典的修改外,CREATE TABLE不会记录日志.
如果你想在NOARCHIVELOG模式的数据库上看到差别,可以把对表T的DROP TABLE和CREATE TABLE换成DROP INDEX和CREATE INDEX。
默认情况下,不论数据库以何种模式运行，这些操作都会生成日志。
因为不同的模式可能导致不同的行为。你的生产系统可能采用ARCHIVELOG模式运行.
倘若你执行的大量操作在ARCHIVELOG模式下会生成redo,而在NOARCHIVELOG模式下不会生成redo,
你肯定想在测试时就发现这一点，而不要等到系统交付给用户时才暴露出来！
查看是否归档
查看数据库是否开启归档
select name,log_mode from v$database;
启用归档
startup mount
alter database archivelog;
alter database open;
禁止归档
shutdown immediate
startup mount
alter database noarchivelog
alter database open

force logging(强制日志)模式：
如果数据库强制日志模式开启后,则Oracle无论什么操作都进行redo的写入。
查看强制日志模式
通过
select force_logging from v$database
可以看到当前数据库是否开启了强制日志模式状态
开启强制日志模式
如果未开启数据库强制日志模式（默认未开启），则可以通过
alter database force logging开启,之后Oracle无论什么操作都进行redo的写入,不依赖于数据库的归档模式等其他因素.
关闭强制日志模式
如果已经开启了数据库强制日志模式，则可以通过
alter database no force logging关闭强制日志模式。
使数据库恢复先前的设置,数据库是否写入redo由数据库的归档模式等其他因素决定

disable_logging
那么在Oracle内部还存在一个内部参数：_disable_logging 默认是false
通过更改为true可以让Oracle在修改表中的记录的时候完全不记录redo,这个参数要甚用,平时,我们只作为性能测试用。
查看：show parameter disa /disable/_disable_logging
开启：alter system set "_disable_logging"=true scope=both;
禁用：alter system set "_disable_logging"=false

表的归档模式
查看表的logging模式
查看表是否是logging状态用如下SQL:
select table_name,logging from dba_tables where table_name='tablename';
修改表的logging模式
修改表的logging状态sql:
alter table table_name nologging/logging
减少redo写入
本节所讲的都是当数据库未开启强制日志模式时的操作。
对象的操作在执行时会产生重做日志,采用某种方式,生成的redo会比平常(即不使用NOLOGGING子句时)少得多.
注意,这里说"redo"少得多,而不是"完全没有redo".所有操作都会生成一些redo,不论数据库的日志模式是什么,
所有数据字典操作都会计入日志。

如何减少redo
create table时减少redo的方法
创建表时crate table as加入nolongging选项减少redo，格式如下
create table [table_name] nologging as [select表达式]。
insert into减少redo的方法
insert 大批量数据时加入/*+append */选项减少redo写入,格式如下
insert /*+append */ into [table_name] [select表达式]

数据库归档模式下生成redo规则
create table时nologging效果
归档模式下创建的表，默认为logging模式。
创建表时crate table as加入nolongging选项减少redo写入明显
验证
下面比较以下两种create table as时产生的redo size量。
SELECT a.NAME,
b.VALUE cur_size_byte,
round(b.VALUE / 1024, 3) || 'KB' cur_size_kb
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'
查询当前的重做日志大小记录下来
create table test_1 as select * from test;
SELECT a.NAME,
b.VALUE cur_size_byte,
round(b.VALUE / 1024, 3) || 'KB' cur_size_kb
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'
查询当前的重做日志大小减去前面记录下来的值计算刚才这个操作产生的redo大小标记为redo_1

SELECT a.NAME,
b.VALUE cur_size_byte,
round(b.VALUE / 1024, 3) || 'KB' cur_size_kb
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'
查询当前的重做日志大小记录下来
create table test_2 nologging as select * from test;
SELECT a.NAME,
b.VALUE cur_size_byte,
round(b.VALUE / 1024, 3) || 'KB' cur_size_kb
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
AND lower(a.NAME) LIKE '%' || lower('redo size') || '%'
查询当前的重做日志大小减去前面记录下来的值计算刚才这个操作产生的redo大小标记为redo_2
比较redo_1和redo_2的大小就知道crate table as加入nolongging或不加nologging选项的区别了

insert into时加入append效果
表模式logging
当表模式为logging状态时,无论是append模式还是no append模式,redo都会生成,即加入append选项无法生效。
验证
下面比较以下两种insert时产生的redo size量,可以看出redo量是差不多的。
计算重做大小的方法与上面的一样就不说了
1、insert /*+append */ into test_1 select * from test;
commit;
2、insert into test_1 select * from test;
commit;

表模式nologging
当表模式为nologging状态时,只有加入append模式会明显减少生成redo。
验证
1、insert /*+append */ into test_1 select * from test;
commit;
2、insert into test_1 select * from test;
commit;

数据库非归档模式生成redo规则
create table 使用nologging对产生redo的影响
非归档模式下创建的表，默认为nologging模式。
在NOARCHIVELOG模式的数据库中，除了数据字典的修改外，CREATE TABLE不会记录日志。
因此创建表时（crate table as）加入nologging选项减少redo写入不明显,即nologging选项加不加都差不多
验证
下面比较以下两种create table as时产生的redo size量。
create table test_1 as select * from test;
create table test_2 nologging as select * from test;

insert into时append效果
表模式logging
当表模式为logging状态时,加入append模式明显减少生成redo,而no append模式下不会减少生成。
验证
insert /*+append */ into test_1 select * from test;
commit;
insert into test_1 select * from test;
commit;

表模式nologging
当表模式为nologging状态时，append的模式会减少生成redo,而no append模式不会减少生成。
验证
insert /*+append */ into test_1 select * from test;
commit;
insert into test_1 select * from test;
commit;


不错~稍微补充一下~就是在
archivelog 模式下的logging表加append hint后redo还是会稍微减少一些的~
这个主要的原因在于direct path这种方式是通过HW进行rollback的~所以不需要写undo
并且rollback的速度也会比普通方式加载的速度更快

disable_logging＝true的情况下，要关闭archivelog，不然redo文件会破坏，无法启动。