如何加速Oracle大批量数据处理sql
1、提升DML操做的办法:数据库
简单说来:1、暂停索引,更新后恢复.避免在更新的过程当中涉及到索引的重建.2、批量更新,每更新一些记录后及时进行提交动做.避免大量占用回滚段和或临时表空间.3、建立一临时的大的表空间用来应对这些更新动做.服务器
4、批量更新,每更新一些记录后及时进行提交动做.避免大量占用回滚段和或临时表空间.session
5、建立一临时的大的表空间用来应对这些更新动做.oracle
6、加大排序缓冲区alter session set sort_area_size=100000000;
insert into tableb select * from tablea;
commit;app
若是UPDATE的是索引字段,就会涉及到索引的重建,暂停索引不会提升多少的速度,反而有可能下降UPDATE速度,由于在更新是索引能够提升数据的查询速度,重建索引引发的速度下降影响不大。分布式
ORACLE优化修改参数最多也只能把性能提升15%,大部分都是SQL语句的优化!工具
update整体来讲比insert要慢:几点建议:1、若是更新的数据量接近整个表,就不该该使用index而应该采用全表扫描2、减小没必要要的index,由于update表一般须要update index
3、若是你的服务器有多个cpu,采用parellel hint,能够大幅度的提升效率另外,建表的参数很是重要,对于更新很是频繁的表,建议加大PCTFREE的值,以保证数据块中有足够的空间用于UPDATE, 从而下降CHAINED_ROWS。性能
2、各类批量DML操做:优化
(1)、oracle批量拷贝:
set arraysize 20
set copycommit 5000
copy fromusername/password@oraclenameappend table_name1
using select * from table_name2;
(2)、常规插入方式:
insert into t1 select * from t;
为了提升速度可使用下面方法,来减小插入过程当中产生的日志:
alter table t1 nologging;
insert into t1 select * from t;
commit;
(3)、CTAS方式:
create table t1
as
select * from t;
为了提升速度可使用下面方法,来减小插入过程当中产生的日志,而且能够制定并行度:
create table t1 nologging parallel(degree 2) as select * from t;
(4)、Direct-Path插入:
insert /*+append*/ into t1 select * from t;
commit;
为了提升速度可使用下面方法,来减小插入过程当中产生的日志:
alter table t1 nologging;
insert /*+append*/ into t1 select * from t;
Direct-Path插入特色:
一、append只在insert … select …中起做用,像insert /*+ append */ into t values(…)这类的语句是不起做用的。在update、delete操做中,append也不起做用。
二、Direct-Path会使数据库不记录直接路径导入的数据的重作日志,会对恢复带来麻烦。
三、Direct-Path直接在表段的高水位线以上的空白数据块中写数据,不会重用高水位线如下的空间,会对空间的使用形成必定的浪费,对查询的性能也会形成必定的影响。而常规插入会优先考虑使用高水位线之下有空闲空间存在的数据块。所以理论上Direct-Path插入会比常规插入速度更快,由于Direct-Path直接使用新数据块,而常规插入要遍历freelist获取可用空闲数据块,若是同nologging配合,这种速度优点会更加明显。
四、以append方式插入记录后,要执行commit,才能对表进行查询。不然会出现错误:ORA-12838:没法在并行模式下修改以后读/修改对象。
五、用append导入数据后,若是没有提交或者回滚,在其余会话中任何对该表的DML都会被阻塞(不会报错),但对该表的查询能够正常执行。
六、在归档模式下,要把表设置为nologging,而后以append方式批量添加记录,才会显著减小redo数量。在非归档模式下,没必要设置表的nologging属性,便可减小redo数量。若是表上有索引,则append方式批量添加记录,不会减小索引上产生的redo数量,索引上的redo数量可能比表的redo数量还要大。
七、数据直接插入数据文件,绕过buffer cache而且忽略了引用完整性约束。
八、无论表是否在nologging下,只要是direct insert,就不会对数据内容生成undo。
九、Oracle在Direct-Path INSERT操做末尾,对具备索引的表执行索引维护,这样就避免了在drop掉索引后,再rebuild。
十、Direct-Path INSERT比常规的插入须要更多的空间。由于它将数据插入在高水位之上。并行插入非分区表须要更多的空间,由于它须要为每个并行线程建立临时段。
十一、在插入期间,数据库在表上得到排他锁,用户不能在表上执行并行插入、更新或者删除操做,并行的索引建立和build也不被容许。但却能够并行查询,但查询返回的是插入以前的结果集。
(5)、并行DML:
若是你的服务器有多个cpu,采用parellel hint,能够大幅度的提升效率ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(tableA, 2) */INTO tableA
SELECT * FROM tableB;
为了提升速度可使用下面方法,来减小插入过程当中产生的日志:
INSERT /*+ PARALLEL(tableA, 2) */INTO tableA NOLOGGING
SELECT * FROM tableB;
oracle默认并不会打开PDML,对DML语句必须手工启用。即须要执行
alter table enable parallel dml命令。
并行DML特色:
1、在并行DML模式中,默认的就是DIRECT-PATH插入,为了运行并行DML模式,必须知足如下条件:
a、必须是Oracle企业版;
b、必须在session中使并行DML生效,执行如下sql语句:
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
c、必须指定table的并行属性,在建立的时候或者其余时候,或者在insert操做时使用“PARALLEL”提示。
d、为了使Direct-Path Insert模式失效,在INSERT语句中指定“NOAPPEND”提示,覆盖并行DML模式。
2、并行Direct-Path INSERT到分区表:
相似于serial Direct-Path INSERT,每一个并行操做分配给一个或者多个分区,每一个并行操做插入数据到各自的分区段的高水位标志之上,commit以后,用户就能看到更新的数据。
三、并行Direct-Path INSERT到非分区表:
每一个并行执行分配一个新的临时段,并插入数据到临时段。当commit运行后,并行执行协调者合并新的临时段到主表段,用户就能看到更新的数据。
四、Direct-Path INSERT可使用Log或者不使用Log。
五、另外不得不说的是,并行不是一个可扩展的特性,只有在数据仓库或做为DBA等少数人的工具在批量数据操做时利于充分利用资源,而在OLTP环境下使用并行须要很是谨慎。事实上PDML仍是有比较多的限制的,例如不支持触发器,引用约束,高级复制和分布式事务等特性,同时也会带来额外的空间占用,PDDL同样是如此。