直接路径插入(Direct Path Insert)是Oracle一种数据加载提速技术,可以在使用insert语句或SQL*Loader工具大批量加载数据时使用。直接路径插入处理策略与普通insert语句完全不同,Oracle会通过牺牲空间,安全性,并发性能来换取加载速度。
一、Direct-path Insert简介
普通insert语句叫做"传统插入"(Conventional Insert),数据在插入过程中会先缓存在buffer cache中,写入磁盘时会检查并重用数据块中的可重用空间,记录redo日志,维护完整性约束等,这些维护操作都是性能开销,而"直接路径插入"会忽略这些维护操作,换取插入性能的提升。
在海量数据加载场景,特别是向新表大批量加载数据时(加载数据存在原始备份、新表没有可重用空间、并发访问很低)。我们的第一需求可能是加载速度。针对此类场景Oracle提供了一种性能更高的数据加载方式:“直接路径插入”(Direct-Path Insert)。
传统插入与直接路径插入主要有以下5点区别:
- 传统插入会经过buffer cache缓存后再写数据文件,而直接路径插入会直接写数据文件,这也是Direct-Path Insert名称的由来。
- 传统插入会重用数据块中的空闲空间,即新旧数据混在一起。而Direct-Path Insert会直接在高水位线(High-Water Mark, HWM)之上追加写数据,即只在新的数据块中写数据,旧数据块中即使有空间也不会重用(更多的空间消耗)
- 传统插入会维护引用完整性约束,Direct-Path Insert不会维护完整性约束(必须删除或禁用引用完整性约束)
- 传统插入必须生成redo日志,Direct-Path Insert可以选择关闭redo日志(无法进行Media Recovery)
- 传统插入不会影响表上其他DML操作,而Direct-Path Insert会获取表级的X锁,因此表上的insert, delete, update都会被阻塞(无法并发)
二、Direct-Path Insert应用场景
Direct-Path Insert可以在下列场景中使用:
- 使用insert into … values … 语句时通过hint指示Oracle使用Direct-Path Insert
- 使用insert into … as select … 语句时通过hint指示Oracle使用Direct-Path Insert
- 使用并行执行,Oracle会自动使用Direct-Path Insert
- 使用SQL*Loader工具向加载数据时指定使用Direct-Path Insert
2.1 insert into … values … 语句使用Direct-Path Insert
少量的insert into … values …语句通常没必要使用直接路径插入。而在PL/SQL程序中,如果需要通过insert into … values … 语句插入大量数据,则可以选择直接路径插入来提升执行速度。通过在insert关键字后附加/*+ append_values */提示来指示Oracle使用直接路径插入。
示例:建立2张同样的表,分别用传统插入和直接路径插入向表中加载1000万的数据,并记录执行时间:
create table t1(id integer, name varchar2(32));
create table t2(id integer, name varchar2(32));
declare
type idtype is table of t1.id%type index by pls_integer;
type nametype is table of t1.name%type index by pls_integer;
pids idtype;
pnames nametype;
iterations constant pls_integer := 10000000;
moment1 integer;
moment2 integer;
moment3 integer;
begin
for j in 1..iterations loop
pids(j) := j;
pnames(j) := 'No.' || to_char(j);
end loop;
moment1 := dbms_utility.get_time;
forall x in 1..iterations
insert into t1(id, name) values(pids(x), pnames(x));
commit;
moment2 := dbms_utility.get_time;
forall x in 1..iterations
insert /*+ append_values */ into t2(id, name) values(pids(x), pnames(x));
commit;
moment3 := dbms_utility.get_time;
dbms_output.put_line('Execution Time Compare (seconds):');
dbms_output.put_line('----------------------------------');
dbms_output.put_line('Conventional Insert: '|| to_char((moment2 - moment1)/100));
dbms_output.put_line('Direct-Path Insert: '|| to_char((moment3 - moment2)/100));
end;
/
- 表t1和t2的表结构相同,使用循环向其中插入1000万条数据
- 第一个循环使用传统插入,耗时7.14秒,第二个循环使用直接路径插入,耗时3.52秒
2.2 insert into … select … 子查询直接路径插入
使用insert into … select … 通过子查询向表中加载数据时,在insert或select关键字后附加/*+ append */提示来使用直接路径插入。
示例:将表t2的数据使用Direct-Path Insert加载到t1中
insert /*+ append */ into t1 select * from t2;
commit;
insert into t1 select /*+ append */ * from t2;
commit;
注意:使用直接路径插入的数据,在提交前是不能查询和更新的,必须显式commit之后才可以使用。上面的两个insert语句中间必须有一个commit,否则第二条insert会失败(ORA-12838)
2.3 并行模式下使用Direct-Path Insert
当开启并行模式后,insert语句会自动变为Direct-Path Insert,但也可以选择使用提示/*+ noappend parallel */来禁用Direct-Path Insert。
示例:使用并行模式,首先要在会话级别打开并行DML:
alter session enable parallel dml;
检查是否满足下面3个条件中的任意一个(满足任意条件即可使用Direct-Path Insert):
- 表已经打开并行属性
- insert的时候显式使用parallel提示
- 将初始化参数parallel_degree_poicy设置为auto
修改表的并行属性和在insert语句中显式使用parallel提示:
alter table t1 parallel;
insert /*+ parallel(t1,4) */ into t1 select * from t2;
修改parallel_degree_policy参数需要较高的权限:
alter system set parallel_degree_policy=auto;
2.4 使用SQL*Loader工具时指定Direct-Path Insert
SQL* Loader是Oracle提供的一个数据加载工具,用于将数据从外部文件加载到数据库的表中。在加载数据时,可以采用Direct-Path Insert提升加载速度。由于SQL* Loader的功能非常强大,使用也稍复杂,下面仅使用SQL*Loader的Express模式(不需要控制文件,且有大量默认选项)演示直接路径插入。
SQL* Loader加载数据时,指定direct=true选项可以指示其使用Direct-Path Insert,这里准备了一个简单的数据文件t1.dat,只有3行数据。
采用SQL* Loader的express模式将数据加载进入表t1,加载时指定direct=ture:
sqlldr hr/hr table=t1 direct=true
- SQL*Loader的express模式会自动在当前目录下搜索table_name.dat文件,所以这里不需要指定数据文件
- 日志的Path used: Direct代表其采用了直接路径插入
三、Direct-Path Insert与重做日志
与传统插入强制生成重做日志不同,Direct-Path Insert可以选择关闭重做日志的生成,减少性能开销(但也意味着无法进行Media Recovery)。
如果关闭了重做日志,Oracle只会生成很少量的无效重做日志,万一数据库崩溃了,这些使用Direct-Path Insert插入的数据块会被标记为损坏(因为没有重做日志无法进行Media Recovery),因此建议使用nologging模式插入数据后进行一次备份。
通过修改表/索引/分区/LOB的logging模式,可以关闭和打开该对象上重做日志的生成:
alter table t1 nologging;
alter table t1 logging;
注意:如果DBA在数据库或表空间级别设置的了force logging,那么你在表级别是无法关闭重做日志的,即使使用nologging选项也会被忽略。
alter database force logging;
alter database no force logging;
alter tablespace users force logging;
alter tablespace users no force logging;