sql 硬解析、软解析、bulk collect性能比较

时间:2021-06-05 20:09:46

参考Kyte的《Oracle专家高级编程》第11章。

 
环境:Oracle 11g2(memory_target=1808M),PL/SQL Developer 9.0。
测试内容是在一个只有1列的空表中插入100000行,比较速度,单用户模式,分有无主键两种情况。
 
create table t_num (num_id number);
alter table t_num add constraint pk_t_num primary key (num_id);
 
1. 硬解析
declare 
  i integer;
begin
  for i in 1 .. 100000 loop
    execute immediate
      'insert into t_num values ('||i||')';
  end loop;
  commit;
end;
 
结果:24.944s(无主键) ,26.754 (有主键)
注:取第二次跑的结果,第一次先预热shared mem,不过结果发现两次时间差异不大。
truncate table t_num; --每次做完记得truncate,以后不再重复
 
2. 软解析
declare 
  i integer;
begin
  for i in 1 .. 100000 loop
    execute immediate
      'insert into t_num values (:v1)' 
      using i;
  end loop;
  commit;
end;
 
结果:2.402s (无主键) ,3.744 (有主键)
 
3. 软解析(静态sql)
declare 
  i integer;
begin
  for i in 1 .. 100000 loop
    insert into t_num values (i);
  end loop;
  commit;
end;
 
结果:2.153s (无主键) ,3.026 (有主键)
注:的确比2中要好一些,10%速度提升,似乎没有想象中的那么大
 
4. bulk collect
declare 
  i integer;
  TYPE t_typ IS TABLE OF number;
  v_p t_typ := t_typ();
begin
  for i in 1 .. 100000 loop -- 初始化collection
    v_p.extend;
    v_p(i) := i;
  end loop;
 
  forall i in 1 .. v_p.count -- 不能写成  forall i in 1 .. 100000
    insert into t_num(num_id) values (v_p(i));
  commit;
end;
 
结果:0.078s (无主键),0.327 (有主键)
注:bulk collect真是快啊,只是代码上比较麻烦。1.forall后面只能跟一条简单的dml,复杂的语句都不能用,不过这个也符合forall的设计精神;2.forall只针对collection,所以只好之前先新建一个type并初始化。
 
从上面的结果看来,的确用bulk collect会有数量级的性能提升,开发或者导数据的时候,要尽量使用bulk collect的方式。不过这个过程中药考虑到redo和undo的大小,虽然insert的log量比较少,应该一般不会超过大小限制。
 
 
接下去,再补一个关于使用bulk collect来load数据的测试。
 
将刚才的t_num表中插入1000000条数据(是刚才的实验的数据量的10倍),从1~1000000,然后将这些数据load到plsql中的一个collection里面。
 
1. 逐个fetch
declare 
  i integer;
  TYPE t_typ IS TABLE OF number;
  v_p t_typ := t_typ();
  cursor cur_t_num is
    select * from t_num;
begin
  for rec_t_num in cur_t_num loop 
    v_p.extend;
    v_p(v_p.last) :=  rec_t_num.num_id;
  end loop;
  dbms_output.put_line(v_p.count);
end;
 
结果:0.858s (无主键),0.889s (有主键)
 
2. bulk collect
declare 
  i integer;
  TYPE t_typ IS TABLE OF number;
  v_p t_typ := t_typ();
  cursor cur_t_num is
    select * from t_num;
begin
  select * bulk collect into v_p
    from t_num;
 
  dbms_output.put_line(v_p.count);
end;
结果:0.219 (无主键),0.25s (有主键)
 
两个方法相比来看,bulk collect方法时间消耗大约是另一种的1/4,再一次证明了bulk collect在性能上的提升是很明显的。不过比较奇怪的一点是,为什么有主键情况的 select * from t_num这句的执行要比没有主键的时候要慢一些?执行计划里面看起来是一样的,都是TABLE ACCESS FULL,这个需要后续进一步分析。
 
最后的最后,还有一个一条sql搞定1~100000条数据插入的方法:
insert into   t_num  
select rownum from dual
connect by level <= 100000;
 
结果:0.093s (无主键),0.328 (有主键)
性能上和bulk collect的差不多,小数位数的差异应该是误差造成的。