PL/SQL性能

本文摘着网友岁月留痕《有关使用PL/SQL提高性能的学习》

1、使用rowid进行跌代处理，通过rowid检索数据是最快的方法，甚至比唯一参考索引还快。
如果在循环中需要根据cursor取到的条件进行进一步的操作，则在cursor中同时取出rowid，并且根据rowid定位记录会是个高效率的方法，比取到主键或者唯一性索引的效率都好。
以下的例子是取表的10万条记录进行后续处理，id是该表的主键。优化后节省将近2秒钟，效率提升16%。
优化前：
declare
   cursor cur_test is
   select id from user_tab
   where rownum <= 100000;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      update user_tab
      set user_name = user_name
      where id = n_row.id;
     
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
20050420 10:40:14.791
20050420 10:40:26.509
PL/SQL procedure successfully completed.
优化后：
declare
   cursor cur_test is
   select rowid from user_tab
   where rownum <= 100000;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      update user_tab
      set user_name = user_name
      where rowid = n_row.rowid;
     
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
20050420 10:40:38.112
20050420 10:40:48.051
PL/SQL procedure successfully completed.
2、确保比较运算中的数据类型相同。
要确保在where条件中的等号左右的数据类型是一致的，否则oracle虽然会进行自动的转换，但是效率上会有所缺失。
3、根据条件出现的频率来排序IF条件。
如果在块儿中间有多个IF条件判断，则应该考虑将命中率高的IF条件放在前面，这样对效率的提升也是有好处的。
下面的例子循环1亿次，判断条件的次序调整，节省将近4秒钟，效率提升23%。现在判断条件后只是进行简单的累加，如果是做更复杂的处理，效率的提升会更明显。
优化前：
declare
   ln_number number := 0;
   ln_if number := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      if ln_if = 1 then
         ln_number := ln_number + 1;
      elsif ln_if = 0 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:56:44.411
20050420 10:57:01.062
PL/SQL procedure successfully completed.
优化后：
declare
   ln_number number := 0;
   ln_if number := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      if ln_if = 0 then
         ln_number := ln_number + 1;
      elsif ln_if = 1 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:57:01.498
20050420 10:57:14.385
PL/SQL procedure successfully completed.
4、使用PLS_INTEGER PL/SQL数据类型进行整数运算。
这种数据类型可以用于代替各种数值数据系列类型的声明中，只要变量的值是一个整数，且在-2147483647到+2147483647。该数据类型可以使用更少的内部命令来处理，因此用这种数据类型可以提高性能。
这种数据类型可以替代整数的NUMBER型的变量定义，而实际的处理效率是比NUMBER型更快捷的。
以下的例子是个简单的迭加，循环1亿次，使用该数据类型节省大概0.5秒的时间，效率提升16%，不知道对于更复杂的数据处理，是不是效率上会有更大的提升。
优化前：
declare
   ln_number number;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      ln_number := ln_number + 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:47:17.676
20050420 10:47:21.301
PL/SQL procedure successfully completed.
优化后：
declare
   ln_number pls_integer;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      ln_number := ln_number + 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:47:21.989
20050420 10:47:25.149
PL/SQL procedure successfully completed.
5、减少对SYSDATE的调用
单独取SYSDATE的时候不再需要从dual表中获得，可以直接付给变量：
ld_date := sysdate;
但是应该减少调用SYSDATE的次数，如果可以，应该在块儿的最开始付给某个变量，而不是需要的时候再取得，除非你需要精确的时间戳。
下面的例子循环10万次，分别在循环中取得时间和循环外取得时间，节省时间0.152秒，效率提升99%。但是要注意这两个不是等价的优化，如果你需要每次循环取得不同的时间戳，就应该将按照前面的脚本编写。
优化前：
declare
   ld_date date;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000 loop
      ld_date := sysdate;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:32:41.432
20050420 11:32:41.586
PL/SQL procedure successfully completed.
优化后：
declare
   ld_date date;
   ld_date1 date;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   ld_date1 := sysdate;
   for n_row in 1..100000 loop
      ld_date := ld_date;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:32:42.218
20050420 11:32:42.220
PL/SQL procedure successfully completed.
6、减少MOD函数的使用，可以用其他函数代替，但是效率不同。
不是所有的oracle函数的效率支出是等同的，MOD就是个消耗会比较多的函数，虽然他的功能是不错的，但是如果可以用其他的函数取代，应减少该函数的使用次数。
下面的例子是进行了100万次循环，分别使用mod作为判断条件或者不使用，优化后节省0.7秒多，效率提升90%。
优化前：
declare
   ln_number pls_integer := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ln_number := ln_number + 1;
      if mod(ln_number,1000) = 0 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:20:34.435
20050420 11:20:35.225
PL/SQL procedure successfully completed.
优化后：
declare
   ln_number pls_integer := 0;
   ln_num1 pls_integer := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ln_number := ln_number + 1;
      ln_num1 := ln_num1 + 1;
      if ln_num1 = 1000 then
         ln_number := ln_number + 2;
         ln_num1 := 0;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:20:35.461
20050420 11:20:35.542
PL/SQL procedure successfully completed.
7、将参考表加载到PL/SQL表中可以加快查询速度。这是因为利用了PL/SQL中数组索引的优势。
下面这个例子是主表100万条记录，根据主键取参考表的对应字段，参考表只有6条记录。优化后的节省将近45秒，效率提升97%。
优化前：
declare
   cursor cur_test is
   select changecentsreason_id
   from usercentsdetails_tab
   where rownum <= 1000000;
  
   ls_name changecentsreason_tab.changecentsreason%type;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      select changecentsreason
      into ls_name
      from changecentsreason_tab
      where id = n_row.changecentsreason_id;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
20050420 10:21:09.461
20050420 10:21:55.407
PL/SQL procedure successfully completed.
优化后：
declare
   type ref_array is table of varchar2(20) index by binary_integer;
   ref_tab ref_array;
  
   cursor cur_test is
   select changecentsreason_id
   from usercentsdetails_tab
   where rownum <= 1000000;
  
   cursor cur_ref is
   select id,changecentsreason
   from changecentsreason_tab;
  
   ls_name changecentsreason_tab.changecentsreason%type;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row1 in cur_ref loop
      ref_tab(n_row1.id) := n_row1.changecentsreason;
   end loop;
  
   for n_row in cur_test loop
      ls_name := ref_tab(n_row.changecentsreason_id);
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
20050420 10:19:20.333
20050420 10:19:21.604
PL/SQL procedure successfully completed.
8、时间范围的取得
以前为了查询一整天的记录，我通常的写法是：
between to_date(sysdate) - 1 and to_date(to_char(to_date(sysdate),'yyyymmdd')||' 23:59:59','yyyymmdd hh24:mi:ss') - 1
多次的格式转换对性能也是有影响的，写成：
between to_date(sysdate) - 1 and to_date(sysdate) - 0.000011574
0.000011574表示1秒。这样的效率应该会有所提升。
下面的例子循环了100万次，优化后节省36秒多，效率提升89%。不过这是比较特别的情况，因为其实不需要在循环中这样做的，循环只是放大了效率提升的效果。
优化前：
declare
   ld_date date ;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ld_date := to_date(to_char(to_date(sysdate),'yyyymmdd')||' 23:59:59','yyyymmdd hh24:mi:ss') - 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:24:19.530
20050420 11:24:50.525
PL/SQL procedure successfully completed.
优化后：
declare
   ld_date date ;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ld_date := to_date(sysdate) - 0.000011574;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:24:50.750
20050420 11:25:05.229