登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

飞哥的技术博客

世上无难事,只怕有心人!

 
 
 

日志

 
 
 
 

针对批量插入如何提高性能的问题进行的测试过程  

2009-06-23 20:18:21|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 针对批量插入如何提高性能的问题进行的测试过程收藏

 批量插入如何提高性能?

原条件要求如下:
1)原表:100万条记录;
2)一次插入10万条记录;
3)原表中并且有索引存在;
问题:
1)此时要求提高插入的性能,如何快速完成一次性的插入?
2)如果一次性插入50万条记录情况下,如何快速完成一次性的插入?


1、为了使测试就会更有针对性,首先思考影响批量插入性能的可能因素有哪些?
   然后分别针对这些可能的因素,一一证明这些因素是否真的影响到批量插入的性能,
   并分析哪些因素是影响批量插入的主要因素?
/* wwj:如果你一开始就能猜到测试想证明什么问题,测试就会更有针对性*/

讨论出来影响批量插入性能的可能因素有:
记录数据量:原表:100万条记录,一次插入10万条记录
1)、有无append
2)、nologging(对表以及对索引)
3)、索引的个数
4)、parallel(aa,10)(对表以及对索引)
5)、实时更新索引和重建索引因素
6)、分区表和分区索引
7)、oracle的版本
--ADO往SP中传数组的方法
8) 一次插入50万条记录的话,以上情况又是如何?

2、测试过程:
测试的环境:
数据库版本:oracle 10g
虚拟机内存大小:512M 一个内存

测试步骤:
记录数据量:原表:100万条记录,一次插入10万条记录
a) 创建表:
  drop table aa;
  create table aa as
  select * from all_col_comments;
 
b) 导入100万条记录
begin
for i in 1..24 loop
insert into aa
select * from all_col_comments;
end loop;
end ;

SQL>  select count(*) from aa;
  COUNT(*)
----------
   1037746
  
c)、给新建的表的前三个字段创建复合索引
-- Create/Recreate indexes
--drop index aa_index
create index aa_index on AA (owner, table_name, column_name);
  
d)、建立大约10万条记录的表
drop table temp
create table temp
as
select * from all_col_comments;

begin
for i in 1..2 loop
insert into  temp
select * from all_col_comments;
end loop;
end;

SQL> select count(*) from temp;
  COUNT(*)
----------
    124538

e)、创建一个一次插入10万记录的insert存储过程
create or replace procedure insertaa is
begin
insert  into aa
select * from temp;
end insertaa;

f)、调用存储过程
SQL> begin
insertaa();
end;  2    3
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:02:30.86

---------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------
测试影响因素:
1)、比较 添加了APPEND注释的insert存储过程
create or replace procedure insertaa1 is
begin
insert  /*+ APPEND */  into aa
select * from temp;
end insertaa1;

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:57.13

--------------------------------------------------------------------------------------------------------------------------------------------

2)nologging(对表以及对索引)
修改aa表的属性,使得nologging对表aa
alter table aa nologging;

SQL> begin
  2  insertaa();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:23.69
分析比较:没有添加APPEND的存储过程时间:Elapsed: 00:02:30.86
          没有添加APPEND的存储过程在执行alter table aa nologging之后需要的时间:Elapsed: 00:00:23.69

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.33
分析比较:没有添加APPEND的存储过程时间:Elapsed: 00:02:30.86
          执行添加APPEND的存储过程时间:Elapsed: 00:00:57.13
          添加APPEND的存储过程在执行alter table aa nologging之后需要的时间Elapsed: 00:00:08.33

修改aa表中索引的属性,使得nologging对索引aa_index
alter index  aa_index  nologging;

SQL> begin
  2  insertaa();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.83

分析比较:没有添加APPEND的存储过程时间:Elapsed: 00:02:30.86
          没有添加APPEND的存储过程在执行alter table aa nologging之后需要的时间:Elapsed: 00:00:23.69
          没有添加APPEND的存储过程在继续执行alter index  aa_index  nologging之后需要的时间:Elapsed: 00:00:17.83

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.32

分析比较:没有添加APPEND的存储过程时间:Elapsed: 00:02:30.86
          执行添加APPEND的存储过程时间:Elapsed: 00:00:57.13
          添加APPEND的存储过程在执行alter table aa nologging之后需要的时间Elapsed: 00:00:08.33
          添加APPEND的存储过程在继续执行alter index  aa_index  nologging之后需要的时间:Elapsed: 00:00:08.32
         
         
         
建立存储过程如下:
create or replace procedure insertaa2 is
begin
insert  /*+ APPEND  nologging*/  into aa
select * from temp;
end insertaa1;

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.13

SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.04

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.75

SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.43

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.07
         
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.99

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.11
         
APPEND hint的学习:
APPEND  When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used.
If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces
generation of redo.
分析:由于测试中aa表以及aa_index索引都设置为nologging状态,所以使用了APPEND hint之后,是不会产生redo的,存储过程加与不加nologging提示无所谓的,
     并且在oracle提示中nologging提示是不存在的。
    
--------------------------------------------------------------------------------------------------------------------------------------------------
         
3)、索引的个数
SQL> alter table aa nologging;
Table altered.

SQL>  alter index  aa_index  nologging;
Index altered.

在nologging table以及nologging index条件下,并且使用APPEND提示,不多加索引时所测试的情况如下:
SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:51.60

在nologging table以及nologging index条件下,并且使用APPEND提示,多创建两个索引的测试情况如下:
create index aa_index1 on AA (table_name, column_name);
create index aa_index2 on AA (column_name);

SQL> begin
  2  insertaa1();
  3  end;
  4  /

PL/SQL procedure successfully completed.
Elapsed: 00:02:29.57

分析:条件都是在nologging table以及nologging index条件下,并且使用APPEND提示,在相同的条件下,增加索引的个数会影响的批量插入的性能,
      使批量插入性能下降;
      如何才能既能在多个索引情况下,又能够提高性能呢?
      在后面会测试下实时更新索引和重建索引之间的性能比较;

---------------------------------------------------------------------------------------------------------------------------------------------------
4)、parallel(aa,10)(对表以及对索引)
要使parallel并行提示有效,必须要执行ALTER SESSION { ENABLE | FORCE } PARALLEL DML,此时加PARALLEL提示才有作用;
新建存储过程,加APPEND以及parallel提示;
create or replace procedure insertaa2 is
begin
insert  /*+ APPEND  parallel(aa,10)*/  into aa
select * from temp;
end insertaa2;

不加parallel提示的情况下测试如下:(条件都是在nologging table以及nologging index条件下,并且使用APPEND提示,并且还是在只有一个索引的情况下)
SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:01:06.19


加parallel提示的情况下测试如下:(条件都是在nologging table以及nologging index条件下,并且使用APPEND提示,并且还是在只有一个索引的情况下)
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.

SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:44.53


再执行一次不加parallel提示的情况:
SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.59

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.19

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.80

再加parallel提示的情况:
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.94

SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.69

SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:19.45

分析:parallel(aa,10)提示并没有完全提高性能

将parallel(aa,10)改为parallel(aa,3)又会如何?
不加parallel提示的情况下测试如下:
SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.30

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.53

加parallel提示的情况下测试如下:
SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:15.87

SQL> ALTER SESSION ENABLE  PARALLEL DML;
Session altered.
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.55

分析:parallel(aa,5)提示并没有完全提高性能
因为parallel是多线程实现,线程切换和管理需要时间,而这些线程在单CPU下事实上是串行的,
对不分区的表进行parallel插入的话,需要分配多个临时表,先同时对多个临时表进行插入,然后再合并,
对多个临时表的插入也是使用多线程实现,是串行执行的。

5)、实时更新索引和重建索引因素
对表多建几个索引,有什么变化?
多创建两个索引
create index aa_index1 on AA (table_name, column_name);
create index aa_index2 on AA (column_name);

比较rebuild+insert(无索引或索引实效)和insert(有索引)测试(多个索引)的几种情况的测试:
1、rebuild+insert(无索引)情况下:
--做插入操作的存储过程定义,包含新建索引
create or replace procedure insertaa2 is
begin
insert  /*+ APPEND nologging parallel(aa,10)*/ into aa
select * from temp;
EXECUTE IMMEDIATE 'create index aa_index on aa(owner, table_name, column_name)';   --新建索引
EXECUTE IMMEDIATE 'create index aa_index1 on AA (table_name, column_name)';   --新建索引
EXECUTE IMMEDIATE 'create index aa_index2 on AA (column_name)';   --新建索引
end insertaa2;


无索引测试步骤:
1)、建表aa,数据量115万条左右;
2)、调用存储过程插入10万条记录,并且即时创建索引;
SQL> begin
  2  insertaa2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:03:36.41

比较:只有aa_index一个索引的情况下所需时间为:Elapsed: 00:01:30.47
分析:索引个数的增加使得所消耗的时间近三倍,时间的增长幅度比较的大

2、rebuild+insert(索引实效)情况下:

--做插入操作的存储过程定义,包含rebuild索引
create or replace procedure insertaa is
begin
insert  /*+ APPEND nologging parallel(aa,10)*/ into aa
select * from temp;
EXECUTE IMMEDIATE 'alter index aa_index rebuild nologging';   --rebuild索引
EXECUTE IMMEDIATE 'alter index aa_index1 rebuild nologging';   --rebuild索引
EXECUTE IMMEDIATE 'alter index aa_index2 rebuild nologging';   --rebuild索引
end insertaa;

索引实效的测试步骤:
1)、建表aa,数据量115万条左右;
2)、创建索引
   create index aa_index on AA (owner, table_name, column_name);
   create index aa_index1 on AA (table_name, column_name);
   create index aa_index2 on AA (column_name);

3)、使索引失效
   alter index  aa_index  unusable;
   alter index  aa_index1  unusable;
   alter index  aa_index2  unusable;

SQL> select status
  2  from all_indexes a
  3  where a.owner=upper('daimin')
  4  and a.index_name=upper('aa_index');
STATUS
--------
UNUSABLE  --aa_index索引失效

SQL> select status
  2  from all_indexes a
  3  where a.owner=upper('daimin')
  4  and a.index_name=upper('aa_index1');
STATUS
--------
UNUSABLE     --aa_index1索引失效

SQL> select status
  2  from all_indexes a
  3  where a.owner=upper('daimin')
  4  and a.index_name=upper('aa_index2');
STATUS
--------
UNUSABLE   --aa_index2索引失效


4)、调用存储过程插入10万条记录,并且即时rebuild索引;
SQL> begin
  2  insertaa();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:41.00

比较:只有aa_index一个索引的情况下所需时间为: Elapsed: 00:00:26.15
分析:索引个数的使得所消耗的时间增加幅度不是很大


2、insert(有索引)情况下:
--做插入操作的存储过程定义
create or replace procedure insertaa1 is
begin
insert  /*+ APPEND nologging parallel(aa,10)*/ into aa
select * from temp;
end insertaa1;


有索引的测试步骤:
1)、建表aa,数据量115万条左右;
2)、创建索引
   create index aa_index on AA (owner, table_name, column_name);
   create index aa_index1 on AA (table_name, column_name);
   create index aa_index2 on AA (column_name);

3)、调用存储过程插入10万条记录
SQL> begin
  2  insertaa1();
  3  end;   4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.49

比较:只有aa_index一个索引的情况下所需时间为:Elapsed: 00:00:15.94
分析:索引个数的使得所消耗的时间增加幅度不是很大

结论:还是insert(有索引)情况下快


6)、分区表和分区索引
 wwj 说:
insert其他分区,也可以比对一下rebuild index
Joy Dai 说:
你是说让数据分散,插入到不同的分区?
wwj 说:
相同分区
wwj 说:
是有数据的
Joy Dai 说:
哦,你的意思是指不要插入到最后一个分区?
Joy Dai 说:
插入到中间的分区?
wwj 说:

1)、分区表和分区索引,插入到相同分区测试
create table temp
as
select * from all_col_comments
where rownum<1;

begin
for i in 1..3 loop
insert into  temp
select * from all_col_comments;
end loop;
end;

SQL> select count(*) from temp;
  COUNT(*)
----------
    124538

SQL>  drop table aa;
Table dropped.

SQL> create table aa as
  2  select rownum as r,a.* from all_col_comments a;
Table created..

SQL> begin
  2  for i in 1..24 loop
  3  insert into aa
  4  select 41554*i+rownum as r,a.*  from all_col_comments a;
  5  end loop;
  6  end ;
  7  /
PL/SQL procedure successfully completed.

SQL> select count(*) from aa;
  COUNT(*)
----------
   1039775

SQL> select max(r) from aa;
    MAX(R)
----------
   1038886

设置aa表nologging属性
SQL> alter table aa nologging;
Table altered.

新建索引
SQL> create index aa_index on aa(owner, table_name, column_name);
Index created.


创建分区表
-- Create table
drop table BBp;

SQL> create table BBp
  2  PARTITION  BY RANGE ( r )
  3  (
  4  PARTITION quarter_one  VALUES LESS THAN ( 200000),
  5  PARTITION quarter_two  VALUES LESS THAN ( 400000),
  6  PARTITION quarter_three  VALUES LESS THAN ( 600000),
  7  PARTITION quarter_four   VALUES LESS THAN ( 800000),
  8  PARTITION quarter_five  VALUES LESS THAN ( 1000000),
  9  PARTITION quarter_six VALUES LESS THAN (MAXVALUE)
 10  )
 11  as
 12  select * from aa;
Table created.

创建分区索引
SQL> create index BBp_index on BBp (owner, table_name, column_name) LOCAL;
Index created.

设置BBp表nologging属性
SQL> alter table BBp nologging;
Table altered.

SQL> select count(*) from BBp;
  COUNT(*)
----------
   1039775

针对不同的想法,创建不同的存储过程进行测试:
--将10多条记录插入到不是分区表的表中去的存储过程
create or replace procedure insertaa1 is
begin
insert  /*+ APPEND */  into aa
select 1039775+rownum as r ,t.* from temp t;
end insertaa1;

--因为124538<200000,该插入操作是插入到第一个分区quarter_1中(插入到带有数据的分区中去)
create or replace procedure insertBB1 is
begin
insert  /*+ APPEND */  into BBp
select rownum as r ,t.* from temp t;
end insertBB1;


--因为1000000+124538>1000000,该插入操作是将数据插入到最后一个几乎没有数据的分区quarter_13中去
create or replace procedure insertBB2 is
begin
insert  /*+ APPEND */  into BBp
select 1000000+rownum as r ,t.* from temp t;
end insertBB2;


-- temp表中的数据被分散插入到不同分区
create or replace procedure insertBB3 is
begin
insert  /*+ APPEND */  into BBp
select trunc(dbms_random.value(1, 1039775))+rownum as r ,t.* from temp t;
end insertBB3;


调用存储过程:
SQL> begin
  2  insertaa1();   --将10多条记录插入到不是分区表的表中
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:02:36.77

SQL> begin
  2  insertbb1();    --所有数据插入有数据的相同分区
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:58.61

SQL> begin
  2  insertbb2();   --所有数据插入到一个接近空的分区
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:48.33

SQL> begin
  2  insertbb3();  --所有数据分散插入到不同分区
  3  end;
  4  /
PL/SQL procedure successfully completed.

Elapsed: 00:03:11.01

比较: 将数据批量插入到一个接近空的分区速度最快,将数据批量分散插入到不同分区速度最慢


2)对比先使索引失效,然后rebuild index的方法

针对不同的想法,创建不同的存储过程进行测试:
使索引失效
alter index  aa_index  unusable;

SQL> select status
  2  from all_indexes a
  3  where a.owner=upper('daimin')
  4  and a.index_name=upper('aa_index');
STATUS
----------------
UNUSABLE

--将10多条记录插入到不是分区表的表中去的存储过程
create or replace procedure insertaa1 is
begin
insert  /*+ APPEND */  into aa
select 1039775+rownum as r ,t.* from temp t;
EXECUTE IMMEDIATE 'alter index aa_index rebuild nologging';   --rebuild索引aa_index
end insertaa1;

SQL> begin
  2  insertaa1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:01:17.25


使分区索引失效
ALTER INDEX BBp_index MODIFY PARTITION quarter_one UNUSABLE;

--因为124538<200000,该插入操作是插入到第一个分区quarter_one中(插入到带有数据的分区中去)
create or replace procedure insertBB1 is
begin
insert  /*+ APPEND */  into BBp
select rownum as r ,t.* from temp t;
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_one nologging';   --rebuild quarter_1分区的索引BBp_index
end insertBB1;

SQL> begin
  2  insertbb1();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.15


使分区索引失效
ALTER INDEX BBp_index MODIFY PARTITION quarter_six UNUSABLE;

--因为1000000+124538>1000000,该插入操作是将数据插入到最后一个几乎没有数据的分区quarter_six中去
create or replace procedure insertBB2 is
begin
insert  /*+ APPEND */  into BBp
select 1000000+rownum as r ,t.* from temp t;
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_six nologging';   --rebuild quarter_six分区的索引BBp_index
end insertBB2;

SQL> begin
  2  insertbb2();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.78


使索引失效
alter index  BBp_index  unusable;

-- temp表中的数据被分散插入到不同分区
create or replace procedure insertBB3 is
begin
insert  /*+ APPEND */  into BBp
select trunc(dbms_random.value(1, 1039775))+rownum as r ,t.* from temp t;
EXECUTE IMMEDIATE 'alter index BBp_index rebuild nologging';   --rebuild bbp的索引BBp_index
end insertBB3;

SQL> begin
  2  insertbb3();
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
ORA-06512: at "DAIMIN.INSERTBB3", line 5
ORA-06512: at line 2

然后修改存储过程如下:
-- temp表中的数据被分散插入到不同分区
create or replace procedure insertBB3 is
begin
insert  /*+ APPEND */  into BBp
select trunc(dbms_random.value(1, 1039775))+rownum as r ,t.* from temp t;
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_one nologging';
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_two nologging';
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_three nologging';
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_four nologging';
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_five nologging';
EXECUTE IMMEDIATE 'alter index BBp_index rebuild partition quarter_six nologging';
end insertBB3;

SQL> begin
  2  insertBB3();
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:01:28.62

比较:使用先使索引失效,然后rebuild index的方法都比实时更新索引的批量插入的性能好,并且在使用rebuild index的方法中仍然是
      将数据批量插入到一个接近空的分区速度最快,将数据批量分散插入到不同分区速度最慢


  评论这张
 
阅读(1149)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018