oraclefunction写法_ORACLE当中自定义函数性优化浅析

为什么函数影响性能

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

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

CREATE TABLE TEST
(
   ID  NUMBER
);


DECLARE RowIndex NUMBER;
BEGIN
RowIndex :&＃61;1;
WHILE RowIndex <&＃61; 8 LOOP
 INSERT INTO TEST
 SELECT RowIndex  FROM DUAL;
 
  RowIndex :&＃61; RowIndex &＃43;1;
END LOOP;
COMMIT;
END;
/


--创建函数SLOW_FUNCTION&＃xff0c;使用DBMS_LOCK.SLEEP休眠2秒&＃xff0c;模拟这个函数较慢。
CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;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


如果在WHERE当中使用函数&＃xff0c;由于有8条记录&＃xff0c;而每次调用函数需要Sleep 2秒&＃xff0c; 总共耗费2*8&＃61;16秒。所以在WHERE条件中&＃xff0c;也要谨慎使用自定义函数。

SQL> SET AUTOTRACE OFF;
SQL> 
SQL> SELECT * FROM TEST
  2  WHERE SLOW_FUNCTION(ID)>15;

        ID
----------
         6
         7
         8

Elapsed: 00:00:16.01
SQL> 


什么情况下函数影响性能

其实自定义函数影响性能&＃xff0c;主要在于函数(Function)调用的次数或函数(Function)本身的业务逻辑是否复杂&＃xff0c;如果SELECT查询中调用次数很少&＃xff0c;影响还是非常小的。如下所示&＃xff0c;如果只调用一次的话&＃xff0c;这个影响还是非常小的。

SQL> SELECT SLOW_FUNCTION(ID) FROM TEST WHERE ID&＃61;2;

SLOW_FUNCTION(ID)
-----------------
               12

Elapsed: 00:00:02.01


其次&＃xff0c;如果函数实现的业务逻辑简单&＃xff0c;即使调用次数多&＃xff0c;对性能影响也很小。我们改写一下下面函数&＃xff0c;通过实验来验证测试一下&＃xff0c;如下所示&＃xff1a;

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
AS
BEGIN
 RETURN p_value&＃43;10;
END;
/


然后创建一个存储过程&＃xff0c;来测试一下循环次数对性能的影响。

CREATE OR REPLACE PROCEDURE TEST_SLOW_FUNCTION(ITER IN NUMBER)
AS RESULT VARCHAR2(60);
BEGIN
 FOR I IN 1 .. ITER LOOP
 SELECT SLOW_FUNCTION(I) INTO RESULT FROM DUAL;
 END LOOP;
END TEST_SLOW_FUNCTION;
/


如下所示&＃xff0c;当函数业务逻辑简单&＃xff0c;性能开销很低时&＃xff0c;循环次数对性能的影响反而很小。10次循环调用跟1000000次循环调用差别是3秒多。可见如果自定义函数的业务逻辑简单&＃xff0c;循环次数对性能影响较小。

SQL> EXEC TEST_SLOW_FUNCTION(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> EXEC TEST_SLOW_FUNCTION(10000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.40
SQL> EXEC TEST_SLOW_FUNCTION(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.64


如何优化解决问题

对SQL中调用的自定义函数&＃xff0c;可以通过等价改写成多表关联语句。避免产生大量的递归调用&＃xff0c;另外就是想法设法减少函数被调用次数。SQL中尽量避免使用自定义函数(不是不能用&＃xff0c;而是要看场合&＃xff0c;尽量避免使用的原因&＃xff1a;因为你写的函数&＃xff0c;可能会被其它人滥用&＃xff0c;会偏离当初你写这个函数的初衷)&＃xff0c;或者尽量避免函数中实现复杂业务逻辑。

另外&＃xff0c;如果实在不能避免的话&＃xff0c;就尽量减少调用次数。另外&＃xff0c;也有一些技巧可以优化自定义函数性能&＃xff0c;下面内容基本参考或翻译Efficient Function Calls From SQL这篇文章。

首先我们还是准备测试环境&＃xff0c;使用最初的那个模拟函数。

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;10;
END;
/


标量子查询缓存(scalar subquery caching)

标量子查询缓存(scalar subquery caching)会通过缓存结果减少SQL对函数(Function)的调用次数&＃xff0c; ORACLE会在内存中构建一个哈希表来缓存标量子查询的结果。当然前提是有重复值的情况下。如果没有重复值&＃xff0c;其实这种方法是没有任何效果的。如下测试所示

然后我们删除源数据&＃xff0c;构造重复数据&＃xff0c;然后我们测试对比看一下实验结果吧&＃xff0c;不同的写法差别10秒&＃xff0c;如果在复杂的实际环境中&＃xff0c;这种性能差别还会被放大。

TRUNCATE TABLE TEST;
INSERT INTO TEST
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;
COMMIT;


通俗来将&＃xff0c;当使用标量子查询的时候&＃xff0c;ORACLE会将子查询结果缓存在哈希表中&＃xff0c; 如果后续的记录出现同样的值&＃xff0c;优化器通过缓存在哈希表中的值&＃xff0c;判断重复值不用重复调用函数&＃xff0c;直接使用上次计算结果即可。从而减少调用函数的次数&＃xff0c;从而达到优化性能的效果。另外在ORACLE 10和11中&＃xff0c; 哈希表只包含了255个Buckets&＃xff0c;也就是说它能存储255个不同值&＃xff0c;如果超过这个范围&＃xff0c;就会出现散列冲突&＃xff0c;那些出现散列冲突的值就会重复调用函数&＃xff0c;即便如此&＃xff0c;依然能达到大幅改善性能的效果。

DETERMINISTIC关键字

ORACLE通过关键字DETERMINISTIC来表明一个函数(Function)是确定性的&＃xff0c;确定性函数可以用于创建基于函数的索引。这个仅仅能在ORACLE 10g以后的版本中使用。它会影响函数如何缓存在SQL中。如下测试所示&＃xff1a;

新建带有DETERMINISTIC关键字的函数

CREATE OR REPLACE FUNCTION SLOW_FUNCTION_DERM(p_value  IN NUMBER)
RETURN NUMBER
DETERMINISTIC
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;10;
END;
/

SQL> SELECT SLOW_FUNCTION(ID) FROM TEST ;

SLOW_FUNCTION(ID)
-----------------
               11
               11
               11
               12
               12
               12
               13
               13

8 rows selected.

Elapsed: 00:00:16.01
SQL> SELECT SLOW_FUNCTION_DERM(ID) FROM TEST ;

SLOW_FUNCTION_DERM(ID)
----------------------
                    11
                    11
                    11
                    12
                    12
                    12
                    13
                    13

8 rows selected.

Elapsed: 00:00:08.00


将函数标记为确定性确实可以提高性能&＃xff0c;但DETERMINISTIC缓存受限于每次从服务器fetch多少数据&＃xff0c;缓存仅在当前fetch的生命周期内有效(缓存仅适用于获取的整个生命周期。后续查询(或提取)无法访问先前运行的缓存值)&＃xff0c;而标量子查询是当前查询内有效。因此受到数组大小的影响. 数组大小(array size)的差异产生了截然不同的性能。如下测试所示&＃xff1a;

The difference in array size produced drastically different performance, showing that caching is only available for the lifetime of the fetch. Subsequent queries (or fetches) have no access to the cached values of previous runs.

SQL> show arraysize;
arraysize 15
SQL> SELECT SLOW_FUNCTION_DERM(ID) FROM TEST ;

SLOW_FUNCTION_DERM(ID)
----------------------
                    11
                    11
                    11
                    12
                    12
                    12
                    13
                    13

8 rows selected.

Elapsed: 00:00:08.01
SQL> set arraysize 1;
SQL> SELECT SLOW_FUNCTION_DERM(ID) FROM TEST ;

SLOW_FUNCTION_DERM(ID)
----------------------
                    11
                    11
                    11
                    12
                    12
                    12
                    13
                    13

8 rows selected.

Elapsed: 00:00:12.01


关于arraysize摘抄“Oracle arraysize 和 fetch size参数与性能优化说明”博客中这一段资料&＃xff1a;

arraysize定义了一次返回到客户端的行数&＃xff0c;当扫描了arraysize行后&＃xff0c;停止扫描&＃xff0c;返回数据&＃xff0c;然后继续扫描。这个过程就是统计信息中的SQL*Net round trips to/from client。因为arraysize 默认是15行&＃xff0c;那么就有一个问题&＃xff0c;因为我们一个block 中的记录数一般都会超过15行&＃xff0c;所以如果按照15行扫描一次&＃xff0c;那么每次扫描要多扫描一个数据块&＃xff0c;一个数据块也可能就会重复扫描多次。

重复的扫描会增加consistent gets 和 physical reads。增加physical reads&＃xff0c;这个很好理解&＃xff0c;扫描的越多&＃xff0c;物理的可能性就越大。consistent gets&＃xff0c;这个是从undo里读的数量&＃xff0c;ORACLE为了保证数据的一致性&＃xff0c;当一个查询很长&＃xff0c;在查询之后&＃xff0c;数据块被修改&＃xff0c;还未提交&＃xff0c; 再次查询时候&＃xff0c;ORACLE会根据Undo来构建CR块&＃xff0c;这个CR块&＃xff0c;可以理解成数据块在之前某个时间的状态。这样通过查询出来的数据就是一致的。那么如果重复扫描的块越多&＃xff0c;需要构建的CR块就会越多&＃xff0c;这样读Undo的机会就会越多&＃xff0c;consistent gets 就会越多。

Cross-Session PL/SQL Function Result Cache

ORACLE 11g 中引入了两个新的缓存机制&＃xff1a;

跨会话PL/SQL函数结果缓存&＃xff1a;用于缓存函数调用的结果。Cross-Session PL/SQL Function Result Cache : Used for caching the results of function calls. 查询结果缓存&＃xff1a;用于缓存查询产生的整个结果集。Query Result Cache : Used for caching the whole result set produced by a query.

ORACLE 11G提供的PL/SQL函数的缓存机制(对于不同的会话之间可以共用)&＃xff0c;下面我们使用第一种机制进行查询结果的缓存&＃xff0c;如下所&＃xff0c;指定关键词RESULT_CACHE来启用Cross-Session PL/SQL Function Result Cache

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
RESULT_CACHE
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;10;
END;
/


然后开启一个会话ID为10的会话&＃xff0c;测试SQL性能

SQL> SET TIMING ON;
SQL> SELECT * FROM V$MYSTAT WHERE ROWNUM &＃61;1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        10          0          0

Elapsed: 00:00:00.01
SQL> SELECT slow_function(id)
  2  FROM TEST;

SLOW_FUNCTION(ID)
-----------------
               11
               12
               13
               14
               15
               16
               17
               18

8 rows selected.

Elapsed: 00:00:16.02


另外开启一个会话&＃xff0c;对比测试SQL性能

SQL> SET TIMING ON;
SQL> SELECT * FROM V$MYSTAT WHERE ROWNUM &＃61;1; 

       SID STATISTIC#      VALUE
---------- ---------- ----------
        73          0          0

Elapsed: 00:00:00.01
SQL> SELECT slow_function(id)
  2  FROM TEST;

SLOW_FUNCTION(ID)
-----------------
               11
               12
               13
               14
               15
               16
               17
               18

8 rows selected.

Elapsed: 00:00:00.01


从上述实验可以看出&＃xff0c;使用上述方式的缓存信息可以被其他的会话使用&＃xff0c;它依赖的对象是自动被管理的。关于这个技术的细节特性是另外一个比较大的话题&＃xff0c;此处不做展开&＃xff0c;我们需要知道的就是&＃xff0c;数据库会帮我们自动缓存&＃xff0c;从而当其他会话调用时&＃xff0c;使用相关缓存结果来减少函数调用次数。从而达到提高性能效果。

使用PL/SQL的集合进行手动的管理缓存信息

在ORACLE 11g之前的版本&＃xff0c;我们可以手动将函数调用的值缓存在PL/ SQL集合中,如下所示&＃xff0c;我们在对函数调用前&＃xff0c;我们构建一个缓存层(caching layer)

CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;10;
END;
/

CREATE OR REPLACE PACKAGE cached_lookup_api AS
 
FUNCTION get_cached_value (p_id  IN  NUMBER)
  RETURN NUMBER;

PROCEDURE clear_cache;
  
END cached_lookup_api;
/
 
 
CREATE OR REPLACE PACKAGE BODY cached_lookup_api AS
 
TYPE t_tab IS TABLE OF NUMBER
  INDEX BY BINARY_INTEGER;

g_tab           t_tab;
g_last_use      DATE   :&＃61; SYSDATE;
g_max_cache_age NUMBER :&＃61; 10/(24*60); -- 10 minutes

-- -----------------------------------------------------------------
FUNCTION get_cached_value (p_id  IN  NUMBER)
  RETURN NUMBER AS
  l_value NUMBER;
BEGIN
  IF (SYSDATE - g_last_use) > g_max_cache_age THEN
    -- Older than 10 minutes. Delete cache.
    g_last_use :&＃61; SYSDATE;
    clear_cache;
  END IF;
  
  BEGIN
    l_value :&＃61; g_tab(p_id);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Call function and cache data.
      l_value :&＃61; slow_function(p_id);
      g_tab(p_id) :&＃61; l_value;
  END;
  
  RETURN l_value;
END get_cached_value;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE clear_cache AS
BEGIN
  g_tab.delete;
END;
-- -----------------------------------------------------------------

END cached_lookup_api;
/


测试如下所示&＃xff0c;不过此方法只能对于当前会话&＃xff0c;缓存不在数据库会话之间共享

这个方法有几个问题&＃xff1a;

缓存中没有依赖关系管理。只是删除了超过十分钟的缓存数据&＃xff0c;你可以提高间隔粒度&＃xff0c;但是它可能需要额外的工作。如果会话是连接池的一部分&＃xff0c;那么通过多次调用可能会泄漏信息( potential for information bleeding)。这可以通过package reset来解决。这个方法没有自动管理缓存大小的机制。缓存不在数据库会话之间共享

手动的使用上下文控制缓存(Manual Caching Using Contexts)

创建上下文(contexts)作为ACCESSED GLOBALLY允许在会话之间共享高速缓存&＃xff0c;如下脚本所示&＃xff1a;

CREATE OR REPLACE CONTEXT cache_context USING cached_lookup_api ACCESSED GLOBALLY;

CREATE OR REPLACE PACKAGE cached_lookup_api AS
 
FUNCTION get_cached_value (p_id  IN  NUMBER)
  RETURN NUMBER;

PROCEDURE clear_cache;
  
END cached_lookup_api;
/
 
 
CREATE OR REPLACE PACKAGE BODY cached_lookup_api AS
 
g_last_use      DATE         :&＃61; SYSDATE;
g_max_cache_age NUMBER       :&＃61; 10/(24*60); -- 10 minutes
g_context_name  VARCHAR2(20) :&＃61; &＃39;cache_context&＃39;;

-- -----------------------------------------------------------------
FUNCTION get_cached_value (p_id  IN  NUMBER)
  RETURN NUMBER AS
  l_value NUMBER;
BEGIN
  IF (SYSDATE - g_last_use) > g_max_cache_age THEN
    -- Older than 10 minutes. Delete cache.
    g_last_use :&＃61; SYSDATE;
    clear_cache;
  END IF;
  
  l_value :&＃61; SYS_CONTEXT(g_context_name, p_id);
  IF l_value IS NULL THEN
    l_value :&＃61; slow_function(p_id);
    DBMS_SESSION.set_context(g_context_name, p_id, l_value);
  END IF;
  
  RETURN l_value;
END get_cached_value;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE clear_cache AS
BEGIN
  DBMS_SESSION.clear_all_context(g_context_name);
END;
-- -----------------------------------------------------------------

END cached_lookup_api;
/


我们在不同会话测试测试可以发现&＃xff0c;这个可以实现ORACLE 11g中的Cross-Session PL/SQL Function Result Cache

SQL> SET TIMING ON;
SQL> SELECT * FROM v$mystat WHERE ROWNUM&＃61;1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        10          0          0

Elapsed: 00:00:00.01
SQL> SELECT cached_lookup_api.get_cached_value(id)
  2  FROM   test;

CACHED_LOOKUP_API.GET_CACHED_VALUE(ID)
--------------------------------------
                                    11
                                    12
                                    13
                                    14
                                    15
                                    16
                                    17
                                    18

8 rows selected.

Elapsed: 00:00:16.03
SQL> 




SQL> SET TIMING ON;
SQL> SELECT * FROM v$mystat WHERE ROWNUM&＃61;1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
        73          0          0

Elapsed: 00:00:00.01
SQL> SELECT cached_lookup_api.get_cached_value(id)
  2  FROM   test;

CACHED_LOOKUP_API.GET_CACHED_VALUE(ID)
--------------------------------------
                                    11
                                    12
                                    13
                                    14
                                    15
                                    16
                                    17
                                    18

8 rows selected.

Elapsed: 00:00:00.01
SQL> 


Scalar Subquery Caching (Revisited)

除了标量子查询缓存之外&＃xff0c;我们还讨论了许多缓存机制&＃xff0c;但是这些缓存方法是否可以替代对标量子查询缓存的需求&＃xff1f;答案是否定的&＃xff0c;因为标量子查询缓存是有效减少SQL和PL/SQL之间上下文切换次数的唯一机制。为了说明这一点&＃xff0c;我们将建立一个具有相同值的100,000行的新测试表。

DROP TABLE t2;

CREATE TABLE t2 (
  id NUMBER
);

INSERT /*&＃43; APPEND */ INTO t2
SELECT 1
FROM   dual
CONNECT BY level <&＃61; 100000;
COMMIT;


CREATE OR REPLACE FUNCTION SLOW_FUNCTION(p_value  IN NUMBER)
RETURN NUMBER
RESULT_CACHE
AS
BEGIN
 DBMS_LOCK.SLEEP(2);
 RETURN p_value&＃43;10;
END;
/


SET SERVEROUTPUT ON
DECLARE
  l_start NUMBER;
BEGIN
  l_start :&＃61; DBMS_UTILITY.get_cpu_time; 
  FOR cur_rec IN (SELECT slow_function(id)
                  FROM   t2)
  LOOP
    NULL;
  END LOOP;
  DBMS_OUTPUT.put_line(&＃39;Regular Query   (SELECT List): &＃39; ||
                       (DBMS_UTILITY.get_cpu_time - l_start) || &＃39; hsecs CPU Time&＃39;);
  l_start :&＃61; DBMS_UTILITY.get_cpu_time; 
  FOR cur_rec IN (SELECT (SELECT slow_function(id) FROM dual)
                  FROM   t2)
  LOOP
    NULL;
  END LOOP;
  DBMS_OUTPUT.put_line(&＃39;Scalar Subquery (SELECT List): &＃39; ||
                       (DBMS_UTILITY.get_cpu_time - l_start) || &＃39; hsecs CPU Time&＃39;);
END;
/

在WHERE子句中使用标量子查询时&＃xff0c;这种CPU使用率的差异也是可见的。

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

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

WHERE子句中的函数

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

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

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

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

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

参考资料&＃xff1a;

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