老熊的三分地-Oracle及数据恢复

本文源起小荷博客中的文章“一次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语句调用函数时，传入的参数如果有绑定变量，应考虑对函数调用次数的影响。