ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 有关使用PL/SQL提高性能的学习:

有关使用PL/SQL提高性能的学习:

原创 Linux操作系统 作者:orchidllh 时间:2005-04-20 00:00:00 0 删除 编辑

最近遇到一些有关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

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/51862/viewspace-180634/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-21

  • 博文量
    180
  • 访问量
    842275