Oracle11新特性——PLSQL函式快取結果（三）

yangtingkun發表於2007-09-12
原文網址 : http://blog.itpub.net/4227/viewspace-69423/
OracleSQL函式快取

打算寫一系列的文章介紹11g的新特性和變化。

這篇介紹11g新增功能PLSQL函式快取結果的應用例項

Oracle11新特性——PLSQL函式快取結果（一）：http://yangtingkun.itpub.net/post/468/393972

Oracle11新特性——PLSQL函式快取結果（二）：http://yangtingkun.itpub.net/post/468/394111


上兩篇介紹了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現在已經無法重現了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69423/，如需轉載，請註明出處，否則將追究法律責任。

相關文章