Oracle11新特性——PLSQL函数缓存结果（三）

上两篇介绍了11g的PLSQL函数缓存结果，以及RESULT_CACHE和DETERMINISTIC的区别。

这里简单介绍一下利用函数缓冲结果来提高性能的具体例子。前面的例子中都是读取表的数据，如果一些复杂的函数是被重复调用的，即使这些函数并没有访问数据库中的表，也是可以通过RESULT_CAHCE来获取性能的提升的。

前一段时间实现过一个利用PL/SQL进行大数据量的运算的功能。由于要计算100的阶乘，最终的结果超过了Oracle的number类型的上限。为了解决这个问题，只能将NUMBER转为为字符串，于是写了两个函数，分别以字符串的形式来实现数值的“加”和“乘”。由于计算量比较大，且包含了大量的递归调用和重复调用，将函数设置为RESULT_CACHE效果比较明显。

关于阶乘和算法的详细描述可以参考这篇文章：数值超过NUMBER最大表示范围的问题（四）：http://yangtingkun.itpub.net/post/468/241044

SQL> CREATE OR REPLACE FUNCTION F_ADD_STR(P_ADD1 IN VARCHAR2, P_ADD2 IN VARCHAR2) RETURN VARCHAR2 AS
2 V_LENGTH1 NUMBER DEFAULT LENGTH(P_ADD1);
3 V_LENGTH2 NUMBER DEFAULT LENGTH(P_ADD2);
4 BEGIN
5 IF V_LENGTH1 > 37 THEN
6 RETURN
7 F_ADD_STR
8 (
9 SUBSTR(P_ADD1, 1, V_LENGTH1 - 37),
10 NVL
11 (
12 SUBSTR
13 (
14 F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2),
15 1,
16 LENGTH(F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2)) - 37
17 ),
18 '0'
19 )
20 )
21 || SUBSTR(F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2), - 37);
22 ELSIF V_LENGTH2 > 37 THEN
23 RETURN
24 F_ADD_STR
25 (
26 NVL
27 (
28 SUBSTR
29 (
30 F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)),
31 1,
32 LENGTH(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36))) - 37
33 ),
34 '0'
35 ),
36 SUBSTR(P_ADD2, 1, V_LENGTH2 - 37)
37 )
38 || SUBSTR(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)), - 37);
39 ELSE
40 RETURN
41 LTRIM
42 (
43 TO_CHAR
44 (
45 TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2),
46 RPAD
47 (
48 '0',
49 GREATEST(V_LENGTH1, V_LENGTH2, LENGTH(TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2))),
50 '9'
51 )
52 )
53 );
54 END IF;
55 END;
56 /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION F_MULTI_STR(P_MUL1 IN VARCHAR2, P_MUL2 IN VARCHAR2) RETURN VARCHAR2 AS
2 V_LENGTH1 NUMBER DEFAULT LENGTH(P_MUL1);
3 V_LENGTH2 NUMBER DEFAULT LENGTH(P_MUL2);
4 BEGIN
5 IF V_LENGTH1 > 19 THEN
6 RETURN F_ADD_STR(F_MULTI_STR(SUBSTR(P_MUL1, 1, V_LENGTH1 - 19), P_MUL2) || LPAD('0', 19, '0'),
7 F_MULTI_STR(SUBSTR(P_MUL1, V_LENGTH1 - 18), P_MUL2));
8 ELSIF V_LENGTH2 > 19 THEN
9 RETURN F_ADD_STR(F_MULTI_STR(P_MUL1, SUBSTR(P_MUL2, 1, V_LENGTH2 - 19)) || LPAD('0', 19, '0'),
10 F_MULTI_STR(P_MUL1, SUBSTR(P_MUL2, V_LENGTH2 - 18)));
11 ELSE
12 RETURN TO_NUMBER(P_MUL1) * TO_NUMBER(P_MUL2);
13 END IF;
14 END;
15 /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION F_MULTI_SUM_PLSQL(P_IN IN NUMBER) RETURN VARCHAR2 IS
2 V_RESULT_MULTI VARCHAR2(32767) DEFAULT '1';
3 V_RESULT VARCHAR2(32767) DEFAULT '0';
4 BEGIN
5 FOR I IN 1..P_IN LOOP
6 V_RESULT_MULTI := F_MULTI_STR(V_RESULT_MULTI, I);
7 V_RESULT := F_ADD_STR(V_RESULT, V_RESULT_MULTI);
8 END LOOP;
9 RETURN V_RESULT;
10 END;
11 /

函数已创建。

SQL> SET TIMING ON
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 02.31
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 02.31

第一个函数计算两个字符串表示的数值的“加”。第二个函数计算两个字符串表示的数值的“乘”。第三个函数通过调用前面两个函数计算任意数值的阶乘。

计算300的阶乘，第一次执行和第二次执行时间完全相同。

下面将前两个函数设置为RESULT_CACHE：

SQL> SET TIMING OFF
SQL> CREATE OR REPLACE FUNCTION F_ADD_STR(P_ADD1 IN VARCHAR2, P_ADD2 IN VARCHAR2) RETURN VARCHAR2
2 RESULT_CACHE AS
3 V_LENGTH1 NUMBER DEFAULT LENGTH(P_ADD1);
4 V_LENGTH2 NUMBER DEFAULT LENGTH(P_ADD2);
5 BEGIN
6 IF V_LENGTH1 > 37 THEN
7 RETURN
8 F_ADD_STR
9 (
10 SUBSTR(P_ADD1, 1, V_LENGTH1 - 37),
11 NVL
12 (
13 SUBSTR
14 (
15 F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2),
16 1,
17 LENGTH(F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2)) - 37
18 ),
19 '0'
20 )
21 )
22 || SUBSTR(F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2), - 37);
23 ELSIF V_LENGTH2 > 37 THEN
24 RETURN
25 F_ADD_STR
26 (
27 NVL
28 (
29 SUBSTR
30 (
31 F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)),
32 1,
33 LENGTH(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36))) - 37
34 ),
35 '0'
36 ),
37 SUBSTR(P_ADD2, 1, V_LENGTH2 - 37)
38 )
39 || SUBSTR(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)), - 37);
40 ELSE
41 RETURN
42 LTRIM
43 (
44 TO_CHAR
45 (
46 TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2),
47 RPAD
48 (
49 '0',
50 GREATEST(V_LENGTH1, V_LENGTH2, LENGTH(TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2))),
51 '9'
52 )
53 )
54 );
55 END IF;
56 END;
57 /

函数已创建。

SQL> CREATE OR REPLACE FUNCTION F_MULTI_STR(P_MUL1 IN VARCHAR2, P_MUL2 IN VARCHAR2) RETURN VARCHAR2

2 RESULT_CACHE AS
3 V_LENGTH1 NUMBER DEFAULT LENGTH(P_MUL1);
4 V_LENGTH2 NUMBER DEFAULT LENGTH(P_MUL2);
5 BEGIN
6 IF V_LENGTH1 > 19 THEN
7 RETURN F_ADD_STR(F_MULTI_STR(SUBSTR(P_MUL1, 1, V_LENGTH1 - 19), P_MUL2) || LPAD('0', 19, '0'),
8 F_MULTI_STR(SUBSTR(P_MUL1, V_LENGTH1 - 18), P_MUL2));
9 ELSIF V_LENGTH2 > 19 THEN
10 RETURN F_ADD_STR(F_MULTI_STR(P_MUL1, SUBSTR(P_MUL2, 1, V_LENGTH2 - 19)) || LPAD('0', 19, '0'),
11 F_MULTI_STR(P_MUL1, SUBSTR(P_MUL2, V_LENGTH2 - 18)));
12 ELSE
13 RETURN TO_NUMBER(P_MUL1) * TO_NUMBER(P_MUL2);
14 END IF;
15 END;
16 /

函数已创建。

SQL> SET TIMING ON
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 02.09
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 02.06

将两个函数设置为RESULT_CACHE后，第一次调用性能就提高了20%左右。第二次调用还有少量的性能提示。

需要注意的是，RESULT_CACHE内存区域并非越大越好，如果设置过大，Oracle可能会保留很多只调用一次的结果，而增加了在内存中查询结果的代价。

SQL> SHOW PARAMETER RESULT_CACHE

NAME TYPE VALUE
------------------------------------ ----------- --------------------
client_result_cache_lag big integer 3000
client_result_cache_size big integer 0
result_cache_max_result integer 5
result_cache_max_size big integer 1312K
result_cache_mode string MANUAL
result_cache_remote_expiration integer 0

SQL> ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 10M;

系统已更改。

已用时间: 00: 00: 00.45
SQL> EXEC DBMS_RESULT_CACHE.FLUSH

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.03
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 12.45
SQL> SELECT F_MULTI_SUM_PLSQL(300) FROM DUAL;

F_MULTI_SUM_PLSQL(300)
----------------------------------------------------------------------------------------------------
3070811274246159831197867634735326011064421523237815764258614271944425390329825533018837351951336353
2235567428200830358057739028888300339437961308469410854940060024340706056649310321571207197011748410
0968282994169648643749490257480706100673731447522894095305459932231841943597483955828501619780513476
3376663859224945830611300526564703815407485636159087424745165046880274902036249095824669917834211373
5325115691474092861090708825215256177119827415812344886262517033400922474794786847386211079948043235
528920420940313

已用时间: 00: 00: 02.81

而且需要注意的是，对于新功能来说，一般总会伴随着很多的bug，RESULT CACHE也不例外。我在测试过程中就碰到过几次，当时重建基表甚至重建函数且改变了函数的逻辑，都无法INVALIDATE结果集，查询得到的都是以前的结果。不过这个bug现在已经无法重现了。