本次优化涉及到oracle数据库大量insert 数据索引带来的开销,使用append、parallel的方式进行改善性能。
一、首先熟悉一下oracle hint的方法
Oracle Hints是一种机制,用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现:
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标,是all_rows还是first_rows。
3) 表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度
本次案例会用到2个hint,append 和parallel,环境是一个数据仓库场景,业务数据处理具有单一性,非常适合通过并行提高单次大业务的速度。
二、APPEND方式加载数据
/*+ append */以直接加载的方式将数据加载入库,它
是从HWM的位置开始插入,可能会造成空间浪费的。
非归档模式下可以减少redo的产生;归档模式下,一般情况下数据库会强制logging,归档减少不了。
insert into t /*+ append */ select * from t;
三、再来说一下oracle的并行
对于OLTP类型的数据库,除非只用于做统计、报表类的表或索引,建议不对相关表或索引调置并行度。在数据库有开启并行查询的情况下,在表或索引上存在默认并行度,将导致数据库优先采用全表或全索引扫描的执行计划,另外将生成多个并行子进程,对于OLTP类应用将反而降低相关SQL的执行效率。
Oracle在并行处理时,会启动多少个并行进程来同时执行任务,并行度越高, 并行进程越多,执行速度 会越快,默认是noparallel,如果我们设置并行度为default值,那么此时的并行度是:
服务器CPU数*每个CPU启用的线程数(PARALLEL_THREADS_PER_CPU),OLTP环境 所以一般建议使用Noparallel,或者将并行度设置为1,而不是default。
并行度hint的使用方法:
/*+ parallel(table_name n) */
在sql中指定执行的并行度,这个值将会覆盖自身的并行度
select /*+ parallel(t 4) */ count(*) from t;
/*+ no_parallel(table_name) */
在sql中指定执行的不使用并行
select /*+ no_parallel(t) */ count(*) from t;
四、索引和insert的关系
oracle在insert数据的过程中,会同时更新索引,索引过多对insert非常不利,索引要按需创建,加载数据时虽然推荐禁用索引,但一般表上会同时存在查询操作,所以索引也必须存在。
五、在优化客户系统前,先做一个测试。
1、准备表和数据
testtb 表从dba_objects中取数86381 条数据,testtb 将作为数据加载到testtb1的数据来源,testtb 上面没有索引,在业务上使用insert into select 计算数据时,select段要首先控制其性能,这里测试咱不涉及select段的性能。
SQL> create table testtb as select * from dba_objects where 1=0;
SQL> create table testtb1 as select * from dba_objects where 1=0;
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:01.49
我们看到插入数据<2s钟,此时增加一个索引
create index ind_OBJECT_NAME_testtb on testtb1(OBJECT_NAME);
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:14.46 <<插入入数据将近15s
再增加索引
create index ind_OBJECT_ID_testtb on testtb1(OBJECT_ID);
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:20.00<<插入入数据将近20s
再增加索引
create index ind_CREATED_testtb on testtb1(CREATED);
create index ind_CREATED_OBJECT_ID_testtb on testtb1(CREATED,OBJECT_ID);
SQL> insert into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:01:01.18<<插入入数据已经到60s以上
假如这些索引都是我们必须的,接下来该hint出场了
insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
SQL> insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
345524 rows created.
Elapsed: 00:00:21.43<<插入入数据21s
我们来看看执行过程
insert into testtb1 select * from testtb;
![5a1ee58cea206189b4193a2774299f7f88d576f3](https://yqfile.alicdn.com/5a1ee58cea206189b4193a2774299f7f88d576f3.png?x-oss-process=image/resize,w_1400/format,webp)
insert /*+Append parallel(8)*/ into testtb1 select * from testtb;
![075c1a9363cc6c71caeb0e0b39743b8292167056](https://yqfile.alicdn.com/075c1a9363cc6c71caeb0e0b39743b8292167056.png?x-oss-process=image/resize,w_1400/format,webp)
加hint提示后,select段使用的并行,但是load as select并没有使用并行,也就是说,parallel只对select段起作用了。
我们再来看看alter session enable parallel dml;
![c3ca61d10fec6f570ae6f92447b6bfd645d0f979](https://yqfile.alicdn.com/c3ca61d10fec6f570ae6f92447b6bfd645d0f979.png?x-oss-process=image/resize,w_1400/format,webp)
此时加载数据也有并行,用户是使用pakage 和存储过程执行业务流,
create or replace procedure proc_1 AUTHID CURRENT_USER parallel_enable as
begin
insert /*+Append parallel(2)*/ into testtb1 select * from testtb;
commit;
DBMS_OUTPUT.put_line('Haved insert OK!');
end;
/
通过10046事件跟踪存储过程的执行
oradebug setmypid;
oradebug unlimit;
oradebug event 10046 trace name context forever, level 12;
exec proc_1;
oradebug event 10046 trace name context off;
oradebug tracefile_name;
经过测试,存储过程中的insert段是无法使用会话级并行的。这一点将在12c得到优化,hint中增加了enable_parallel_dml提示,同时这个参数包含了append的特性。
![4af9ccafb333af1c9bab0b0e03db4a681bd07afb](https://yqfile.alicdn.com/4af9ccafb333af1c9bab0b0e03db4a681bd07afb.png?x-oss-process=image/resize,w_1400/format,webp)
2、真实的场景
![9617ce562864bf33b500fc287f70adf98207841f](https://yqfile.alicdn.com/9617ce562864bf33b500fc287f70adf98207841f.png?x-oss-process=image/resize,w_1400/format,webp)
![6a44546c7cf78d9c5dd8c1ddafb9ed627710f793](https://yqfile.alicdn.com/6a44546c7cf78d9c5dd8c1ddafb9ed627710f793.png?x-oss-process=image/resize,w_1400/format,webp)
insert 语句已经使用了append 和parallel提示进行了优化,但执行1972s还是不能接受,查看插入的对象表,生产环境上该表的索引有8个,包含3个复合索引,测试环境虽然也有8个索引,但是插入速度比生产快,但两者环境不同,不能按照具体数值比较,测试环境索引是优化后的,去掉了多列的复合索引,开发兄弟把生产的索引和测试环境比照进行了重建,当晚执行时间下降到了359s,因此索引一定要按需创建、优化创建。
![e60b361ec7c7cc2da9657eda770d5dcbf0fcdd40](https://yqfile.alicdn.com/e60b361ec7c7cc2da9657eda770d5dcbf0fcdd40.png?x-oss-process=image/resize,w_1400/format,webp)
这条sql执行273s,也是insert into ……select……的类型操作,select部分通过/*+ append parallel */提示从89s 缩减到8s。
六、总结
1.
建立必要的索引,废弃的索引尽快清理
2.
建立不同数据的生命周期,按周期清理,历史归并等
3.
合理使用并行
/*+ parallel(8)*/
5. Insert 表可通过/*+ APPEND */提示提高插入速度,但要持续关注空间使用。