ORACLE当中自定义函数性优化浅析

 

为什么函数影响性能

 

在SQL语句中，如果不合理的使用函数（Function）就会严重影响性能，其实这里想说的是PL/SQL中的自定义函数，反而对于一些内置函数而言，影响性能的可能性较小。那么为什么SQL语句当中，不合理的使用函数会影响性能呢？

 

    在SELECT语句中调用函数时，那么查询返回的结果集中的每一行都会调用该函数。如果该函数需要执行1秒，返回的结果集是10行，那么此时SQL语句就需要10秒，如果该函数执行时间需要3秒，返回的结果集是10000条记录，那么这个时间就是30000秒~= 500分钟。是否很恐怖！因为生产环境中自定义函数有时候会出现复杂的业务逻辑，导致自定义函数性能开销较高，如果出现不合理调用，那么很容易就会出现性能问题。 下面我们简单来演示一个例子。

 

 

CREATE TABLE TEST

(

   ID  NUMBER

);

DECLARE RowIndex NUMBER;

BEGIN

RowIndex :=1;

WHILE RowIndex <= 8 LOOP

    INSERT INTO TEST

    SELECT RowIndex  FROM DUAL;

     RowIndex := RowIndex +1;

END LOOP;

COMMIT;

END;

/

 

--创建函数SLOW_FUNCTION，使用DBMS_LOCK.SLEEP休眠2秒，模拟这个函数较慢。

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)

RETURN NUMBER

AS

BEGIN

    DBMS_LOCK.SLEEP(2);

    RETURN p_value+10;

END;

/

 

 

SQL> SET TIMING ON; 

SQL> SELECT * FROM TEST;

        ID

----------

         1

         2

         3

         4

         5

         6

         7

         8

8 rows selected.

Elapsed: 00:00:00.00

SQL> SELECT SLOW_FUNCTION(ID) FROM TEST;

SLOW_FUNCTION(ID)

-----------------

               11

               12

               13

               14

               15

               16

               17

               18

8 rows selected.

Elapsed: 00:00:16.01

 

 

造成CPU使用率差异的原因是什么？除了标量子查询缓存之外，此处讨论的所有其他缓存方法仍需要调用PL/SQL函数，这会导致SQL和PL/SQL之间的上下文切换。这些上下文切换需要额外的CPU负载。

 

因此，即使在使用可选的缓存功能来提高多次执行之间或会话之间的函数调用的性能，仍应该使用标量子查询缓存来减少上下文切换。

 

 

 

WHERE子句中的函数

 

 

前面讨论的缓存方法也适用于WHERE子句，特别是用于减少上下文切换的标量子查询缓存。

 

在查询的WHERE子句中的列上应用函数可能会导致性能较差，因为它会阻止优化程序在该列上使用常规索引。假设查询不能被重写以消除对函数调用的需要，则一种选择是使用基于函数的索引（function based index ）。

 

您还应该考虑在Oracle 11g中引入的虚拟列。

 

 

函数调用中的读一致性问题

 

　　

这个可以直接参考Efficient Function Calls From SQL，此处不做翻译或解说。

 

 

 

参考资料：

 

 

https://oracle-base.com/articles/misc/efficient-function-calls-from-sql