您现在的位置是:首页 > 正文

批量插入数据之nologging

2024-01-30 22:09:00阅读 0


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) 创建索引

网站文章

  • 想成为最牛B的程序员吗?

    想成为最牛程序员吗?Tip 1 要方法而不是记忆我的一个程序员朋友常跟我说记住超过200个C++函数是多么的有帮助。“我从来不必去查找函数的定义,因此我可以比其他程序员编程快上50%。”他自豪的说。可结果是什么?难道他不知道编译器的代码自动完成功能可以节约大量查找函数及输入函数的时间吗,另外当C#发布出来后,他在记忆函数上面的努力就白费了。当然,编程中对函数的熟记是一件必需的事情,但是...

    2024-01-30 22:08:31
  • css3动画用什么规则,使用css3中的什么规则来定义动画

    css3动画用什么规则,使用css3中的什么规则来定义动画

    使用css3中的“@keyframes”规则来定义动画。“@keyframes”规则用于指定动画规则,定义一个CSS动画的一个周期的行为,可以创建简单的动画;可通过沿动画序列建立关键帧来指定动画序列循环期间的中间步骤。本教程操作环境:windows7系统、CSS3&&HTML5版、Dell G3电脑。@keyframes是CSS3的一种规则,可以用来定义CSS动画的一个周期的行为,...

    2024-01-30 22:08:25
  • uniapp (小程序)uni-datetime-picker组件点击x清除组件内容很难点到

    因为uni-datetime-picker组件源码中input标签盖住了图标,修改源码样式即可。

    2024-01-30 22:08:14
  • JS正则表达式

    判断字符串中是否含e:<!DOCTYPE html><html lang="en"><head> <meta charset="UTF-8"> <title>Title</title> ...

    2024-01-30 22:07:38
  • 【js】【算法】权重随机数

    /**按权重随产生机值,返回随机到的对象 * weights_arr{ * weight:10, * } */ RandomByWeight(weights_arr...

    2024-01-30 22:07:33
  • 软件测试之Linux常用命令

    介绍:命令用于修改文件或者目录的时间属性,包括访问时间和修改时间,若文件不存在,系统会建立一个新的文件。我们平时用得最多的是通过touch创建一个空文件,实际上通过touch命令可以修改文件的atim...

    2024-01-30 22:07:06
  • 【CIO人物展】中粮信托数字化中心总经理谢胜强:技术创新突破资源限制,推动信托行业数字化2.0升级...

    【CIO人物展】中粮信托数字化中心总经理谢胜强:技术创新突破资源限制,推动信托行业数字化2.0升级...

    ‍‍‍‍现场颁奖丨文末报名本文由中粮信托数字化中心总经理谢胜强投递并参与《2023中国数智化转型升级优秀CIO》榜单/奖项评选。丨推荐企业—网易数帆大数据产业创新服务媒体——聚焦数据· 改变商业信托行...

    2024-01-30 22:06:57
  • java多线程买票,同步锁

    java多线程买票,同步锁

    经典多线程例子:售卖火车票 要求销售1000张票,要求有10个窗口来进行销售, 请编写多线程程序来模拟这个效果public class MyTask implements Runnable{ private int ticket = 10; @Override public void run() { while(true){ synchr

    2024-01-30 22:06:50
  • 探究SpringBoot启动时实现自动执行代码

    探究SpringBoot启动时实现自动执行代码

    目前开发的SpringBoot项目在启动的时候需要预加载一些资源。而如何实现启动过程中执行代码,或启动成功后执行,是有很多种方式可以选择,我们可以在static代码块中实现,也可以在构造方法里实现,也...

    2024-01-30 22:06:21
  • 未授予用户在此计算机上的请求登录…

    未授予用户在此计算机上的请求登录…

    问题一:提示:登录失败:未授予用户在此计算机上的请求登录类型。 Windows 运行窗口,输入局域网地址,如\\192.168.1.19。提示: \\192.168.1.19无法访问。您可能没有权限使用网络资源。请与这台服务器的管理员联系以查明您是否有访问权限。 登录失败:未授予用户在此计算机上的请求登录类型。 解决方法(已经验证):控制面板——凭据管理器——添加Windows凭...

    2024-01-30 22:06:15