上两篇介绍了11g的PLSQL函数缓存结果,以及RESULT_CACHE和DETERMINISTIC的区别。
这里简单介绍一下利用函数缓冲结果来提高性能的具体例子。前面的例子中都是读取表的数据,如果一些复杂的函数是被重复调用的,即使这些函数并没有访问数据库中的表,也是可以通过RESULT_CAHCE来获取性能的提升的。
前一段时间实现过一个利用PL/SQL进行大数据量的运算的功能。由于要计算100的阶乘,最终的结果超过了Oracle的number类型的上限。为了解决这个问题,只能将NUMBER转为为字符串,于是写了两个函数,分别以字符串的形式来实现数值的“加”和“乘”。由于计算量比较大,且包含了大量的递归调用和重复调用,将函数设置为RESULT_CACHE效果比较明显。
关于阶乘和算法的详细描述可以参考这篇文章:数值超过NUMBER最大表示范围的问题(四):http://yangtingkun.itpub.net/post/468/241044
[PHP]
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);
4BEGIN
5 IF V_LENGTH1 > 37 THEN
6RETURN
7 F_ADD_STR
8 (
9SUBSTR(P_ADD1, 1, V_LENGTH1 - 37),
10NVL
11(
12 SUBSTR
13 (
14F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2),
151,
16LENGTH(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
23RETURN
24 F_ADD_STR
25 (
26NVL
27(
28 SUBSTR
29 (
30F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)),
311,
32LENGTH(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36))) - 37
33 ),
34 '0'
35),
36SUBSTR(P_ADD2, 1, V_LENGTH2 - 37)
37 )
38 || SUBSTR(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)), - 37);
39 ELSE
40RETURN
41 LTRIM
42 (
43TO_CHAR
44(
45 TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2),
46 RPAD
47 (
48'0',
49GREATEST(V_LENGTH1, V_LENGTH2, LENGTH(TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2))),
50'9'
51 )
52)
53 );
54 END IF;
55END;
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);
4BEGIN
5 IF V_LENGTH1 > 19 THEN
6RETURN 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
9RETURN 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
12RETURN TO_NUMBER(P_MUL1) * TO_NUMBER(P_MUL2);
13 END IF;
14END;
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';
4BEGIN
5 FOR I IN 1..P_IN LOOP
6V_RESULT_MULTI := F_MULTI_STR(V_RESULT_MULTI, I);
7V_RESULT := F_ADD_STR(V_RESULT, V_RESULT_MULTI);
8 END LOOP;
9 RETURN V_RESULT;
10END;
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
.
[/PHP]
第一个函数计算两个字符串表示的数值的“加”。第二个函数计算两个字符串表示的数值的“乘”。第三个函数通过调用前面两个函数计算任意数值的阶乘。
计算300的阶乘,第一次执行和第二次执行时间完全相同。
下面将前两个函数设置为RESULT_CACHE:
[PHP]
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);
5BEGIN
6 IF V_LENGTH1 > 37 THEN
7RETURN
8 F_ADD_STR
9 (
10SUBSTR(P_ADD1, 1, V_LENGTH1 - 37),
11NVL
12(
13 SUBSTR
14 (
15F_ADD_STR(SUBSTR(P_ADD1, V_LENGTH1 - 36), P_ADD2),
161,
17LENGTH(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
24RETURN
25 F_ADD_STR
26 (
27NVL
28(
29 SUBSTR
30 (
31F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)),
321,
33LENGTH(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36))) - 37
34 ),
35 '0'
36),
37SUBSTR(P_ADD2, 1, V_LENGTH2 - 37)
38 )
39 || SUBSTR(F_ADD_STR(P_ADD1, SUBSTR(P_ADD2, V_LENGTH2 - 36)), - 37);
40 ELSE
41RETURN
42 LTRIM
43 (
44TO_CHAR
45(
46 TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2),
47 RPAD
48 (
49'0',
50GREATEST(V_LENGTH1, V_LENGTH2, LENGTH(TO_NUMBER(P_ADD1) + TO_NUMBER(P_ADD2))),
51'9'
52 )
53)
54 );
55 END IF;
56END;
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);
5BEGIN
6 IF V_LENGTH1 > 19 THEN
7RETURN 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
10RETURN 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
13RETURN TO_NUMBER(P_MUL1) * TO_NUMBER(P_MUL2);
14 END IF;
15END;
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
.
[/PHP]
将两个函数设置为RESULT_CACHE后,第一次调用性能就提高了20%左右。第二次调用还有少量的性能提示。
需要注意的是,RESULT_CACHE内存区域并非越大越好,如果设置过大,Oracle可能会保留很多只调用一次的结果,而增加了在内存中查询结果的代价。
[PHP]
SQL> SHOW PARAMETER RESULT_CACHE
NAME
TYPEVALUE
------------------------------------ ----------- --------------------
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
stringMANUAL
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
.
[/PHP]
而且需要注意的是,对于新功能来说,一般总会伴随着很多的bug,RESULT CACHE也不例外。我在测试过程中就碰到过几次,当时重建基表甚至重建函数且改变了函数的逻辑,都无法INVALIDATE结果集,查询得到的都是以前的结果。不过这个bug现在已经无法重现了。
原文出自:http://yangtingkun.itpub.net/post/468/394887
|