Oracle11新特性——PLSQL新特性（四）

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

11g中PL/SQL新增了很多特性，在效能和易用性方面做了不少的提升，還有一些功能性的增強。

這篇介紹一下PLSQL的inlining最佳化。

Oracle11新特性——PLSQL新特性（一）：http://yangtingkun.itpub.net/post/468/395965

Oracle11新特性——PLSQL新特性（二）：http://yangtingkun.itpub.net/post/468/396571

Oracle11新特性——PLSQL新特性（三）：http://yangtingkun.itpub.net/post/468/396994

Oracle在呼叫函式的時候可以將呼叫過程直接替換為子查詢的程式碼，這樣可以避免在執行過程中呼叫開銷，Oracle提供了一個新的PRAGMA INLINE來指示函式是否進行INLINE最佳化：

SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS
2 BEGIN
3 IF P_IN > 1 THEN
4 RETURN P_IN + F_TEST(P_IN - 1);
5 ELSE
6 RETURN 1;
7 END IF;
8 END;
9 /

函式已建立。

SQL> CREATE OR REPLACE FUNCTION F_TEST_INLINE (P_IN IN NUMBER) RETURN NUMBER AS
2 BEGIN
3 IF P_IN > 1 THEN
4 PRAGMA INLINE (F_TEST_INLINE, 'YES');
5 RETURN P_IN + F_TEST_INLINE(P_IN - 1);
6 ELSE
7 RETURN 1;
8 END IF;
9 END;
10 /

函式已建立。

分別建立兩個遞迴呼叫的函式，一個使用了11g提供的PRAGMA INLINE，強制Oracle對函式F_TEST_INLINE進行INLINE最佳化，看看呼叫兩個函式所需的時間：

SQL> SET TIMING ON
SQL> SELECT F_TEST_INLINE(1000000) FROM DUAL;

F_TEST_INLINE(1000000)
----------------------
5.0000E+11

已用時間: 00: 00: 01.32
SQL> SELECT F_TEST(1000000) FROM DUAL;

F_TEST(1000000)
---------------
5.0000E+11

已用時間: 00: 00: 18.68
SQL> SELECT F_TEST_INLINE(1000000) FROM DUAL;

F_TEST_INLINE(1000000)
----------------------
5.0000E+11

已用時間: 00: 00: 00.42
SQL> SELECT F_TEST(1000000) FROM DUAL;

F_TEST(1000000)
---------------
5.0000E+11

已用時間: 00: 00: 00.57

無論是第一次呼叫，還是第二次呼叫，使用了INLINE的方法都比普通呼叫有一個明顯的效能提升。

除了強制使用PRAGMA INLINE方法外，Oracle還提供了自動INLINE的方式，只需要將初始化引數PLSQL_OPTIMIZE_LEVLE設定為3，Oracle會自動選擇合適的過程進行INLINE最佳化：

SQL> SHOW PARAMETER PLSQL_OPTIMIZE_LEVEL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_optimize_level integer 2
SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

會話已更改。

SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS
2 BEGIN
3 IF P_IN > 1 THEN
4 RETURN P_IN + F_TEST(P_IN - 1);
5 ELSE
6 RETURN 1;
7 END IF;
8 END;
9 /

函式已建立。

SQL> SELECT F_TEST(1000000) FROM DUAL;

F_TEST(1000000)
---------------
5.0000E+11

已用時間: 00: 00: 00.40
SQL> SELECT F_TEST(1000000) FROM DUAL;

F_TEST(1000000)
---------------
5.0000E+11

已用時間: 00: 00: 00.46

可以看到設定了自動最佳化功能，F_TEST函式的呼叫也達到了PRAGMA INLINE函式的相同效果。需要說明的是，INLINE最佳化在大部分情況下會帶來效能的提升，但是這個效能的提升是以佔用更多的記憶體為代價的。下面看看兩種方式的統計差別：

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

會話已更改。

SQL> CREATE OR REPLACE FUNCTION F_TEST (P_IN IN NUMBER) RETURN NUMBER AS
2 BEGIN
3 IF P_IN > 1 THEN
4 RETURN P_IN + F_TEST(P_IN - 1);
5 ELSE
6 RETURN 1;
7 END IF;
8 END;
9 /

函式已建立。

SQL> CREATE OR REPLACE FUNCTION F_TEST_INLINE (P_IN IN NUMBER) RETURN NUMBER AS
2 BEGIN
3 IF P_IN > 1 THEN
4 PRAGMA INLINE (F_TEST_INLINE, 'YES');
5 RETURN P_IN + F_TEST_INLINE(P_IN - 1);
6 ELSE
7 RETURN 1;
8 END IF;
9 END;
10 /

函式已建立。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_NUMBER1 NUMBER;
3 V_NUMBER2 NUMBER;
4 V_RES NUMBER;
5 BEGIN
6
7 INSERT INTO T_SESSION_STAT SELECT 1, 'STAT:' || NAME, VALUE
8 FROM V$SESSTAT A, V$STATNAME B
9 WHERE A.STATISTIC# = B.STATISTIC#
10 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
11 UNION ALL
12 SELECT 1, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
13
14 V_NUMBER1 := DBMS_UTILITY.GET_TIME;
15 V_RES := F_TEST_INLINE(1000000);
16 V_NUMBER1 := DBMS_UTILITY.GET_TIME - V_NUMBER1;
17
18 INSERT INTO T_SESSION_STAT SELECT 2, 'STAT:' || NAME, VALUE
19 FROM V$SESSTAT A, V$STATNAME B
20 WHERE A.STATISTIC# = B.STATISTIC#
21 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
22 UNION ALL
23 SELECT 2, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
24
25 V_NUMBER2 := DBMS_UTILITY.GET_TIME;
26 V_RES := F_TEST(1000000);
27 V_NUMBER2 := DBMS_UTILITY.GET_TIME - V_NUMBER2;
28
29 INSERT INTO T_SESSION_STAT SELECT 3, 'STAT:' || NAME, VALUE
30 FROM V$SESSTAT A, V$STATNAME B
31 WHERE A.STATISTIC# = B.STATISTIC#
32 AND A.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1)
33 UNION ALL
34 SELECT 3, 'LATCH:' || NAME, GETS VALUE FROM V$LATCH;
35
36 DBMS_OUTPUT.PUT_LINE('P1 EXECUTE ' || V_NUMBER1/100 || ' SECONDS');
37 DBMS_OUTPUT.PUT_LINE('P2 EXECUTE ' || V_NUMBER2/100 || ' SECONDS');
38
39 FOR C IN
40 (
41 SELECT *
42 FROM
43 (
44 SELECT A.NAME, C.VALUE + A.VALUE - 2 * B.VALUE VALUE
45 FROM
46 T_SESSION_STAT A,
47 T_SESSION_STAT B,
48 T_SESSION_STAT C
49 WHERE A.NAME = B.NAME
50 AND A.NAME = C.NAME
51 AND A.ID = 1
52 AND B.ID = 2
53 AND C.ID = 3
54 )
55 WHERE ABS(VALUE) > 100
56 ) LOOP
57 DBMS_OUTPUT.PUT_LINE(RPAD(C.NAME, 50, ' ') || C.VALUE);
58 END LOOP;
59
60 END;
61 /
P1 EXECUTE 1.4 SECONDS
P2 EXECUTE 2.26 SECONDS
STAT:session pga memory 89194496

PL/SQL 過程已成功完成。

Oracle的inline操作將呼叫操作直接替換為程式的程式碼，避免了呼叫的開銷，獲得了明顯的效能提升，但是這種方法消耗更多的PGA內容也是顯而易見的。

對於特別複雜的呼叫情況，或者遞迴、迴圈呼叫次數過多，可能會造成INLINE呼叫方式的記憶體佔用過大，從而導致效能下降甚至出現記憶體耗盡的情況。INLINE帶來的並非總是效能的提升，使用不當也可能造成嚴重的效能問題。

Oracle提供的PRAGMA INLINE同樣可以強制禁止過程INLINE最佳化的發生。