批量插入数据之nologging

来源：互联网发布：网络星期一编辑：程序博客网时间：2024/05/18 02:07

Oracle nologging tips

The nologging option is a great way to speed-up inserts and index creation. It bypasses the writing of the redo log, significantly improving performance. However, this approach is quite dangerous if you need to roll-forward through this time period during a database recovery. In nologging mode you are running without a safety net when you run nologging operations and you must:

Backup before and after - You must take a backup, both before and after all nologging operations
Only nologging operations during the nologging window - Between the backups (the nologging processing window), ONLY nologging operations should be run in the middle of this "backup sandwich".

The nologging clause is quite convoluted and dependent on several factors.

Database noarchivelog mode - If your database is in "noarchivelog" mode and you are no using the APPEND hint for inserts, you WILL STILL generate redo logs!
Database archivelog mode - If you are in archivelog mode, the table must be altered to nologging mode AND the SQL must be using the APPEND hint. Else, redo WILL be generated.

You can use nologging for batch inserts into tables and for creating indexes:

You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed.

alter table customer nologging;

insert /*+ append */ into customer values ('hello',';there');

You can create indexes with nologging - The only danger with usingnologging is that you must re-run the create index syntax if you perform a roll-forward database recovery. Using nologging with create index can speed index creation by up to 30%.

create index newidx . . . nologging;

Other nologging options - Only the following operations can make use of the NOLOGGING option:

    alter table...move partition
    alter table...split partition
    alter index...split partition
    alter index...rebuild
    alter index...rebuild partition
    create table...as select
    create index
    direct load with SQL*Loader
    direct load INSERT (using APPEND)

For more information on using nologging for optimal performance, see my book Oracle Tuning: The Definitive Reference.

Oracle批量插入性能提升方法

1、批量插入—nologging

1.1 nologging 原理

如图，logging是对事务过程的记录，相反的Nologging就是不记录该事务记录，不写入redo文件。

1.2 nologging 作用

批量插入数据，一般人都会想到使用NOLOGING选项，因为他避免了产生大量的日志信息。虽然它能够带来性能上的快速提升，但是对于主要的数据而言，数据备份和恢复确是灾难性的，因此使用的时候需要注意以下几点：

1、打开nologging选项之前备份，以及关闭nologging之后的再次备份（为什么这么提？有其必然的理由，恢复测试看完就明白了）。

2、所有的nologging操作必须在打开和关闭nologging之间操作，否则操作无效。

1.3 nologging 正确使用

如何正确使用nologging呢，以下注意事项：

1、当数据库处于非归档模式的时候，插入大量数据的时候必须使用HINT /*+ APPEND*/ ，否则任然产生REDO信息。

2、当数据库处于归档模式下，首先必须将表置为nologging模式，插入数据也必须使用HINT /*+ APPEND*/ ,否则同样产生日志信息。

以上结论可以查看我之前的BLOG的验证：NOLOGGING何时生效

1.4 nologging 使用场景

使用 nologging主要场景说明：

1、批量插入:

1 ) 置表为Nologging模式：alter table customer nologging;

2)批量插入语句：insert /*+ append */ into tab;

2、创建索引：create index newidx . . . nologging;

3、其它使用场景：

alter table...move partition

alter table...split partition

alter index...split partition

alter index...rebuild

alter index...rebuild partition

create table...as select

create index

direct load with SQL*Loader

direct load INSERT (using APPEND)

1.5表nologging/logging模式查看和切换

表是否是logging状态,可以用下列语句查看：

SQL> selecttable_name,logging from dba_tables where table_name=upper('pnologging');

TABLE_NAME LOGGING

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

PNOLOGGING

表nologging/logging模式切换：

SQL> alter table pnologging logging;

Tablealtered

那么在Oracle内部还存在一个内部参数：_disable_logging默认是false。通过更改为true可以让Oracle在修改表中的记录的时候完全不记录redo,这个参数要甚用。平时，我们只作为性能测试用。

参数具体参考：http://www.eygle.com/archives/2005/10/oracle_hidden_disable_logging.html

1.6 NOLOGGING对恢复的影响测试(一)

参考：http://space6212.itpub.net/post/12157/509133

Nologging在提高性能的同时，会给恢复带来一定的影响。首先要验证在先发生nologging操作，再备份数据文件的情况下，是否可以完全恢复数据。

1、建分区表PLOGGING和PNOLOGGING

表空间

create tablespace ping datafile '/opt/ora10g/oradata/gis/ping.dbf' size 100m;

表

CREATE TABLE PLOGGING

( A NUMBER,

B NUMBER

)

PARTITION BY LIST (A)

(PARTITION P1 VALUES (1) TABLESPACE PING,

PARTITION P2 VALUES (2) TABLESPACE PING);

CREATE TABLE PNOLOGGING

( C NUMBER,

D NUMBER

)

PARTITION BY LIST (C)

(PARTITION P1 VALUES (1) TABLESPACE PING,

PARTITION P2 VALUES (2) TABLESPACE PING);

2、PLOGGING表logging模式插入测试数据

SQL> insertinto plogging select 2,2 from dba_tables WHERE ROWNUM<1000;

999 rows inserted

SQL> commit;

Commit complete

3、PNOLOGGING表nologging模式插入测试数据

SQL> altertable pnologging nologging;

Table altered

SQL> insertinto /*+ Append */ pnologging select 1,1 from dba_tables WHERE ROWNUM<1000;

999 rows inserted

SQL> commit;

Commitcomplete

SQL> altertable pnologging logging;

Table altered

4、确认当期数据量

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

999

SQL> selectcount(*) from plogging;

COUNT(*)

----------

999

5、RMAN备份表空间

RMAN> backup tablespace ping;

Starting backup at 11-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafilebackupset

channel ORA_DISK_1: specifying datafile(s) inbackupset

input datafile fno=00040 name=/opt/ora10g/oradata/gis/ping.dbf

channel ORA_DISK_1: starting piece 1 at11-FEB-12

channel ORA_DISK_1: finished piece 1 at11-FEB-12

piece handle=/opt/ora10g/product/10.2.0/db_1/dbs/07n32leh_1_1tag=TAG20120211T173929 comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01

Finished backup at 11-FEB-12

6、PLOGGING和PNOLOGGING表logging模式插入测试数据

SQL> altertable pnologging logging;

Table altered

SQL> insertinto pnologging select 1,1 from dba_tables WHERE ROWNUM<501;

500 rows inserted

SQL> insertinto plogging select 1,1 from dba_tables WHERE ROWNUM<501;

500 rows inserted

SQL> commit;

Commit complete

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

1499

SQL> selectcount(*) from plogging;

COUNT(*)

----------

1499

7、模拟损坏表空间数据文件

[oracle@localhost gis]$ echo /dev/null > ping.dbf

SQL> Altersystem flush buffer_cache;

System altered

SQL> select* from pnologging ;

select * from plogging

ORA-01115: IO error reading block from file 40 (block #780)

ORA-01110: data file 40: '/opt/ora10g/oradata/gis/ping.dbf'

ORA-27091: unable to queue I/O

ORA-27072: File I/O error

Additional information: 4

Additional information: 779

8、恢复数据库

SQL> alter database datafile '/opt/ora10g/oradata/gis/ping.dbf' offline;

Database altered.

RMAN> restore tablespace ping;

Starting restore at 11-FEB-12

using target database control file instead ofrecovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=116 devtype=DISK

channel ORA_DISK_1: starting datafilebackupset restore

channel ORA_DISK_1: specifying datafile(s) torestore from backup set

restoring datafile 00040 to /opt/ora10g/oradata/gis/ping.dbf

channel ORA_DISK_1: reading from backup piece/opt/ora10g/product/10.2.0/db_1/dbs/07n32leh_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/product/10.2.0/db_1/dbs/07n32leh_1_1tag=TAG20120211T173929

channel ORA_DISK_1: restore complete, elapsedtime: 00:00:03

Finished restore at 11-FEB-12

RMAN> recover tablespace ping;

Starting recover at 11-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time:00:00:02

Finished recover at 11-FEB-12

SQL> alter database datafile '/opt/ora10g/oradata/gis/ping.dbf' online;

Database altered.

9、结果

SQL> selectcount(*) from plogging;

COUNT(*)

----------

1499

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

1499

结果的最重要的，看到恢复之后的数据库的数量，和恢复前的一样，说明了做完nologging大数量插入后做备份，之后的操作没有再次进行nologging操作，此次nologging操作的数据是可以被恢复的。

1.7 NOLOGGING对恢复的影响测试(二)

1、建分区表PLOGGING和PNOLOGGING

表空间

create tablespace ping datafile '/opt/ora10g/oradata/gis/ping.dbf' size 100m;

表

CREATE TABLE PLOGGING

( A NUMBER,

B NUMBER

)

PARTITION BY LIST (A)

(PARTITION P1 VALUES (1) TABLESPACE PING,

PARTITION P2 VALUES (2) TABLESPACE PING);

CREATE TABLE PNOLOGGING

( C NUMBER,

D NUMBER

)

PARTITION BY LIST (C)

(PARTITION P1 VALUES (1) TABLESPACE PING,

PARTITION P2 VALUES (2) TABLESPACE PING);

2、PLOGGING和PNOLOGGING表logging模式插入测试数据（分区2）

SQL> insertinto plogging select 2,2 from dba_tables WHERE ROWNUM<1000;

999 rows inserted

SQL> insertinto pnologging select 2,2 from dba_tables WHERE ROWNUM<1000;

999 rows inserted

SQL> commit;

Commit complete

此步操作主要是为了体现表内默认存在的数据量，方便和后面的进行对比。

3、确认当期数据量

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

999

SQL> selectcount(*) from plogging;

COUNT(*)

----------

999

4、RMAN备份表空间

RMAN> backup tablespace ping;

Starting backup at 13-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafilebackupset

channel ORA_DISK_1: specifying datafile(s) inbackupset

input datafile fno=00040 name=/opt/ora10g/oradata/gis/ping.dbf

channel ORA_DISK_1: starting piece 1 at13-FEB-12

channel ORA_DISK_1: finished piece 1 at13-FEB-12

piece handle=/opt/ora10g/product/10.2.0/db_1/dbs/08n37l9v_1_1 tag=TAG20120213T150743comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01

Finished backup at 13-FEB-12

5、PLOGGING表logging模式插入测试数据（分区1）

SQL> insertinto plogging select 1,1 from dba_tables WHERE ROWNUM<501;

999 rows inserted

6、PNOLOGGING表nologging模式插入测试数据（分区1）

SQL> altertable pnologging nologging;

Table altered

SQL> insertinto /*+ Append */ pnologging select 1,1 from dba_tables WHERE ROWNUM<501;

999 rows inserted

SQL> commit;

Commitcomplete

SQL> altertable pnologging logging;

Table altered

7、确认当期数据量

SQL> selectcount(*) from plogging;

COUNT(*)

----------

1499

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

1499

SQL> insertinto plogging select 1,1 from dba_tables WHERE ROWNUM<501;

500 rows inserted

SQL> insertinto pnologging select 1,1 fromdba_tables WHERE ROWNUM<501;

500 rows inserted

SQL> commit;

Commit complete

SQL> selectcount(*) from plogging;

COUNT(*)

----------

1999

SQL> selectcount(*) from pnologging;

COUNT(*)

----------

1999

8、模拟损坏表空间数据文件

[oracle@localhost gis]$ echo /dev/null > ping.dbf

SQL> Altersystem flush buffer_cache;

System altered

SQL> select* from pnologging ;

select * from plogging

ORA-01115: IO error reading block from file 40 (block #828)

ORA-01110: data file 40: '/opt/ora10g/oradata/gis/ping.dbf'

ORA-27091: unable to queue I/O

ORA-27072: File I/O error

Additional information: 4

Additional information: 827

9、恢复数据库

SQL> alter database datafile 40 offline;

Database altered.

RMAN> restore tablespace ping;

Starting restore at 13-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafilebackupset restore

channel ORA_DISK_1: specifying datafile(s) torestore from backup set

restoring datafile 00040 to /opt/ora10g/oradata/gis/ping.dbf

channel ORA_DISK_1: reading from backup piece/opt/ora10g/product/10.2.0/db_1/dbs/08n37l9v_1_1

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/ora10g/product/10.2.0/db_1/dbs/08n37l9v_1_1 tag=TAG20120213T150743

channel ORA_DISK_1: restore complete, elapsedtime: 00:00:04

Finished restore at 13-FEB-12

RMAN> recover tablespace ping;

Starting recover at 13-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time:00:00:02

Finished recover at 13-FEB-12

SQL> alter database datafile '/opt/ora10g/oradata/gis/ping.dbf' online;

Database altered.

10、结果

SQL> select count(1) from pnologging;

select count(1) from pnologging

ORA-01578: ORACLE 数据块损坏 (文件号 40,块号 3186)

ORA-01110: 数据文件 40: '/opt/ora10g/product/10.2.0/db_1/dbs/07n32leh_1_1'

ORA-26040: 数据块是使用 NOLOGGING 选项加载的

SQL> select count(1) from pnologging;

COUNT(*)

----------

1999

没有发生过nologging操作的表都可以完全恢复，发生过nologging的表的数据不能完全恢复。

结论：

如果备份后再发生nologging操作，则

1. 产生nologging操作的表不能被完全恢复。

2. nologging操作只会影响与之发生联系的表，不会影响其他表。

3. 如果表是非分区表，则会影响全表。如果nologging影响的表是分区表，nologging操作只会影响受该操作影响的数据对应的分区，而不影响其他分区。

例如T有两个分区P1和P2，在备份后对T发生了nologging操作，但只影响了P1的数据，则恢复时，可以正常访问P2的数据，而访问P1时会报错。

4. 即使nologging操作后的操作都是logging方式，也不能恢复后面的数据。

5. nologging虽然可以提高性能，但是可能会造成数据无法恢复，因此使用这一特性时要非常谨慎。

一般在如下情况下才建议使用：（或者Nologging前要备份，nologging后也要立刻备份）

1) 临时表

2) 中间表

3) 数据可轻易重新生成的表

4) 创建索引