ITPub博客

首页 > 数据库 > Oracle > 用直接路径(direct-path)insert提升性能的两种方法

用直接路径(direct-path)insert提升性能的两种方法

Oracle 作者:lhrbest 时间:2016-03-20 18:51:40 0 删除 编辑

用直接路径(direct-pathinsert提升性能的两种方法

本文属于转载内容,原文地址:http://mp.weixin.qq.com/s?__biz=MzA4MDcyNzc0NQ==&mid=402689013&idx=2&sn=5f57034afc1c9f5721657653e890f4da&scene=23&srcid=0319BdfAQ9epoFjsoO4jgYno#rd&ADUIN=642808185&ADSESSION=1458468975&ADTAG=CLIENT.QQ.5467_.0&ADPUBNO=26567

 

目录

▇1、常规insert方式工作原理

▇2、直接路径(direct-path)insert优点与使用要点

◆2.1、Direct-path insert 方式的优点

◆2.2、使用direct-path insert需要特别注意的要点

▇3、使用直接路径(direct-path)insert的方法

◆3 .1 方法一:使用/*+ APPEND */ hint方式

◆3.2  方法二:DML并行模式的方式

▇4、常规insert与direct-path方式insert性能对比

◆4.1 性能对比测试环境

◆4.2 性能对比过程

◆4.3 性能对比结果

 

1、常规insert方式工作原理

    常见的insert方式有两种:

(1)  insertinto table_name values(....)

(2)  insertinto target_table select* from source_table

上面这两种常规的insert方式,会将insert的数据写入buffer_cache,insert前检查表中是否有block中存有空闲空间可以追加插入,并写入redo log。

 

2、直接路径(direct-path)insert优点与使用要点

2.1、Direct-path insert 方式的优点

(1)  可以将insert数据跳过buffer_cahce,省掉了buffer block的格式化与DBWR操作,直接从PGA写入磁盘

(2)  不检查表中现有的block是否有空闲空间,直接在表的高水位线(HWM)以上插入

(3)  如果在数据库处于非归档模式下,或者是数据就处于归档模式,表级处于nologging状态下,只有少量的空间信息redo写入、不写入数据undo(因为要回滚时可以直接回退到高水线即可,而不需要针对insert生成delete的回滚记录),所以在特定的情况下,直接路径(direct-path)的insert方式,在性能上远远快于常规的串行插入方式。

2.2、使用direct-path insert需要特别注意的要点

2.2.1 DB非force loggging模式下direct-path insert对redo与undo的写入影响

      如果在数据库处于归档模式,以及表处于logging模式下,直接路径(direct-path)性能提升会大打折扣,因为,虽然direct-path能生效,但是仍然会记录下完整的redo和undo。

      也就是说,在归档模式下,还需要将表改成nologging模式,才不会写数据的redo。

2.2.2 DB force logging模式下direct-pathinsert对redo的写入影响

Note: If the database or tablespace is in  FORCE LOGGING mode, then

direct-path INSERT always logs, regardless of the  logging setting.

      如果数据库或表空间在forcelogging模式,则direct-path insert总是会写日志,无论logging如何设置。

 

3、使用直接路径(direct-path)insert的方法

3.1 方法一:使用/*+ APPEND */hint方式

     以下为ORACLE官方技术资料对APPENDhint的说明:

  APPEND hint: Instructs the optimizer to use  direct-path INSERT (data is appended to the

end of the table, regardless of whether there is free  space in blocks below the high

watermark)

3.1.1 数据库非归档模式下使用/*+APPEND*/ hint方式

      当数据库处于非归档模式下,不管表为logging模式还是nologging模式,使用/*+APPEND */ hint,既可以使用direct-path,还将不记录redo和undo

 

用法如下:

INSERT /*+ APPEND */ INTO new_object SELECT * FROM  dba_objects;

3.1.2 数据库处于归模模式下使用/*+APPEND*/ hint方式

      当数据库处于归模模式下,若表为logging模式,即便使用/*+APPEND */ hint,虽然direct-path可以起到作用,但是insert操作仍然会写redo记录,就算你在insert语句上加nologging也不会有效果,redo日志与undo照写不误。

      需要通修改表或修改索引,或修改表空间的no-logging模式来达到不写redo与undo的效果

以下为从metalink(文档ID166727.1)中找到的技术资料:

The APPEND hint is required for using serial  direct-load INSERT.

Direct-load INSERT operations can be done without  logging of redo

information in case the database is in ARCHIVELOG mode.

Redo information generation is suppressed by setting  no-logging

mode for the table, partition, or index into which data  will be

inserted by using an ALTER TABLE, ALTER INDEX, or ALTER  TABLESPACE

command.

用法如下:

Alter table new_object nologging;

INSERT /*+ APPEND */ INTO new_object SELECT * FROM  dba_objects;

 

3.2  方法二:DML并行模式的方式

      DML并行模式下,direct-path插入方式是默认的,当然,在DML并行模式下如果想不使用direct-path插入,可以通过加noappendhint实现。以下是DML并行模式下的direct-path插入:

     并行DML的前提条件:

     (1)ORACLE版本为Oracle Enterprise Edition

      (2)操作的会话开启并行DML

     (3)下面三项要求必须满足一项:

         1)目标表上开启并行属性(DEGREE)

         2)插入语句中指定并行提示(/*+parallel n */)

         3)有设置PARALLEL_DEGREE_POLICY参数的值为AUTO

以数据库为非归档模式用法为例(注意归档模式,还需将表改成nologging模式):

(1)alter session enable parallel dml;

语句还有选项有::ALTER SESSION { ENABLE | FORCE } PARALLEL DML;

(2)alter table new_object_directpath  parallel 8;

(3)insert /*+PARALLEL(new_object_directpath, 8) */into new_object_directpathnologging select * from new_object_old;

 

4、常规insert与direct-path方式insert性能对比

4.1 性能对比测试环境

源表名

test_dba_objects

源表行数

1630104

源表segment大小

184MB

Db 归档模式

归档

logging

语句级nologging

(DB非force logging)

 

4.2 性能对比过程

(1)常规insert方式

A)、建表与修改设定

SQL>create table new_object_directpath as select *  from test_dba_objects where 1=2

SQL>alter table new_object_directpath nologging

SQL> SET TIMING ON

Elapsed: 00:00:00.54

 

B)、insert耗时

SQL> insert into new_object_directpath nologging select * from test_dba_objects;

1630104 rows created.

Elapsed: 00:00:12.43

未产生数据redo与undo

 

(2)APPEND hint的direct-path insert方式

A)、建表与修改设定

SQL>create table new_object_directpath as select *  from test_dba_objects where 1=2

SQL>alter table new_object_directpath nologging

SQL> SET TIMING ON

Elapsed: 00:00:00.54

 

B)、insert耗时

SQL> insert  /*+APPEND */ into new_object_directpath  select * from test_dba_objects;

 

1630104 rows created.

  

Elapsed: 00:00:05.83

未产生数据redo与undo

 

(3)DML并行的direct-path insert方式

A)、建表与修改设定

 

SQL>create table new_object_directpath as select *  from test_dba_objects where 1=2

 

SQL>alter table new_object_directpath nologging

SQL> SET TIMING ON

Elapsed: 00:00:00.54

 

B)、修改表的并行模式

SQL> alter table new_object_directpath parallel 8;

 

C)、insert耗时

SQL> insert /*+parallel (new_object_directpath,8) */  into new_object_directpath select * from test_dba_objects;

 

1630104 rows created.

 

Elapsed: 00:00:05.61

未产生数据redo与undo

 

4.3 性能对比结果

wps5E3B.tmp

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2060489/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Metalink使用指南
请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1384
  • 访问量
    8390328