本文源起小荷博客中的文章“一次cpu的user比例过高的调优”,文章中提到的占用CPU比较高的SQL语句,消耗的CPU比逻辑读还要高的SQL还要多。这两条SQL是:

SELECT NETWORKTYPEID FROM PROBLEM_TAB WHERE PREFIXNUM = SUBSTR(:B1 , 1, 7)
select provinceid into :b0 from PROBLEM_TAB where PREFIXNUM=substr(:b1,1,7)

我关注这个的原因是,为啥这两个SQL比较消耗CPU,比逻辑读更高的SQL消耗得还多。这里让我感兴趣的是,这里使用了函数,函数的参数使用了绑定变量。那么,有一个问题,那就是这个函数会被调用多少次?1次还是与表中所有行相同的次数?我的推测是对于结果确定性(deterministic)的函数,如果传入参数有绑定变量,那么会是被引用(如值的比较)的次数,如果传入的参数是确定的值,那么应该是1次。这个推测是不是正确的呢,让我们来做一些测试。

首先创建测试用的函数:

create or replace package pkg_test is

g_cnt number;

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic;

end pkg_test;

create or replace package body pkg_test is

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic is
begin
g_cnt:=g_cnt+1;
return substr(iv_str, iv_pos, iv_len);
end;
end pkg_test;


创建一个测试表:

SQL> create table t1 as select * from dba_objects where rownum< =1000; Table created.

下面首先测试函数调用时传入的参数全部是常量时的情况:

SQL> set serverout on
SQL> exec pkg_test.g_cnt:=0;

PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where object_name = pkg_test.f_substr('T1AAADBAADFA',1,2);

COUNT(*)
----------
0
SQL> exec dbms_output.put_line(pkg_test.g_cnt);
1

可以看到f_substr函数只调用了1次。
再看看函数调用时传入的参数含有绑定变量时的情况:

SQL> var str varchar2(50);
SQL> exec :str:='T1AAADBAADFA';

PL/SQL procedure successfully completed.
SQL> exec pkg_test.g_cnt:=0;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_name = pkg_test.f_substr(:str,1,2);

COUNT(*)
----------
0
SQL> exec dbms_output.put_line(pkg_test.g_cnt);
1000
PL/SQL procedure successfully completed.

可以看到这一次,f_substr被调用了1000次。

不过,如果我们调用f_substr时,传入的参数第1个(类型为varchar2)为常量,而第2个和第3个参数(类型为number),重新进行上述测试,则f_substr被调用的次数始终为1次。看起来,在参数使用绑定变量时,函数被调用的次数与绑定变量的参数类型有关系

如果把函数调用放在select后的字段列表(即做为选择结果),测试结果也一样。
但也有例外情况,比如:

select count(*) from t1 where pkg_test.f_substr(:str,:cnt,2)=pkg_test.f_substr(:str,:cnt,2);

函数就只调用了2次。不过这种情况非常少见,谁会这样写SQL啊?
上面的测试都是对“确定性”的函数进行的测试,如果函数不是确定性的呢?把f_substr函数定义中的deterministic关键字去掉,再进行测试:

SQL> exec pkg_test.g_cnt:=0;
SQL> select count(*) from t1 where object_name=pkg_test.f_substr('T1AAADAF',1,2);

COUNT(*)
----------
0
SQL> exec dbms_output.put_line(pkg_test.g_cnt);
1000

PL/SQL procedure successfully completed.
SQL> exec pkg_test.g_cnt:=0;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_name=pkg_test.f_substr(:str,1,2);

COUNT(*)
----------
0

SQL> exec dbms_output.put_line(pkg_test.g_cnt);
1000

PL/SQL procedure successfully completed.
SQL> exec pkg_test.g_cnt:=0;

PL/SQL procedure successfully completed.
SQL> select count(pkg_test.f_substr('T1AAADAF',1,2)) from t1;

COUNT(PKG_TEST.F_SUBSTR('T1AAADAF',1,2))
----------------------------------------
1000

SQL> exec dbms_output.put_line(pkg_test.g_cnt);
1000

PL/SQL procedure successfully completed.

这次我们可以看到,对于不确定性的函数,即使传入的参数都是字面值(literal)常量,ORACLE也没有对其优化。在这一轮测试中,只有像

select count(*) from t1 where pkg_test.f_substr(:str,1,2)=pkg_test.f_substr(:str,1,2);

这样的SQL,函数仍然只调用了两次。

还有很多复杂的情况,没有测试。比如表连接,分析函数等。但从上面的测试中,我们还是可以得出下面的结论:

  • 在SQL语句中,函数的调用次数,跟函数是否为确定性函数有关。只有在函数为确定性时,ORACLE才能进行函数调用次数的优化。
  • 在SQL语句中,函数的调用次数,如果向函数传入的参数是绑定变量时,调用次数跟那个参数的类型有关。有的类型如varchar2类型会导致不能对调用次数做优化。
  • 因此,如果用户自定义的函数,尽量使用deterministic关键字。以便让ORACLE能够进行优化。

  • SQL语句调用函数时,传入的参数如果有绑定变量,应考虑对函数调用次数的影响。
Trackback

4 comments untill now

  1. 大森林 @ 2009-05-27 16:43

    老熊,我也做了这个实验,但是结果怎么跟你的不一样啊。当我传入第一个参数是确定值时,调用的次数还是1000次,而不是1次。我是9208的版本,是不是要设什么参数,还是什么别的,请指教。

    [回复]

  2. 大森林 @ 2009-05-27 16:45

    SQL> set serverout on
    SQL> exec pkg_test.g_cnt:=0;

    PL/SQL procedure successfully completed

    SQL> select count(*) from t1 where object_name = pkg_test.f_substr(‘T1AAADBAADFA’,1,2);

    COUNT(*)
    ———-
    0

    SQL> exec dbms_output.put_line(pkg_test.g_cnt);

    1000

    PL/SQL procedure successfully completed

    [回复]

    老熊 回复:

    @大森林, 不知道你的测试中,建的函数是“确定性”的还是“非确定性”的。

    [回复]

  3. junhua zhang @ 2010-10-13 15:30

    10.2开始生效

    [回复]

Add your comment now