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

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

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

這篇介紹一下PLSQL的SEQUENCE的改進。

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

以前取SEQUENCE的NEXTVAL和CURRVAL只能透過SQL語句，現在可以在PL/SQL中透過賦值語句獲取：

SQL> CREATE SEQUENCE S_ID;

序列已建立。

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_ID NUMBER;
3 BEGIN
4 V_ID := S_ID.NEXTVAL;
5 DBMS_OUTPUT.PUT_LINE(V_ID);
6 END;
7 /
1

PL/SQL 過程已成功完成。

這種方法比使用SELECT INTO要方便，更重要的是，由於不執行SQL語句，這種方式的效率更高。

SQL> CREATE SEQUENCE S_1;

序列已建立。

SQL> CREATE SEQUENCE S_2;

序列已建立。

SQL> CREATE OR REPLACE PROCEDURE P1 AS
2 V_SEQ NUMBER;
3 BEGIN
4 FOR I IN 1..100000 LOOP
5 SELECT S_1.NEXTVAL INTO V_SEQ FROM DUAL;
6 END LOOP;
7 END;
8 /

過程已建立。

SQL> CREATE OR REPLACE PROCEDURE P2 AS
2 V_SEQ NUMBER;
3 BEGIN
4 FOR I IN 1..100000 LOOP
5 V_SEQ := S_2.NEXTVAL;
6 END LOOP;
7 END;
8 /

過程已建立。

SQL> SET TIMING ON
SQL> EXEC P1

PL/SQL 過程已成功完成。

已用時間: 00: 00: 06.81
SQL> EXEC P2

PL/SQL 過程已成功完成。

已用時間: 00: 00: 06.35
SQL> EXEC P1

PL/SQL 過程已成功完成。

從SQL的執行時間上看，二者的差別不大，使用賦值的方法似乎略快一些。不過採用賦值的方法最大的好處是可以減少LATCH，在多使用者併發訪問的情況下效率更高。

下面透過查詢V$SESSTAT檢視和V$LATCH檢視，分佈記錄兩個儲存過程的執行統計資訊，並將二者的差異顯示出來。

在執行之前，需要先建立一張臨時表：

SQL> SET TIMING OFF
SQL> CREATE GLOBAL TEMPORARY TABLE T_SESSION_STAT
2 (ID NUMBER, NAME VARCHAR2(100), VALUE NUMBER);

表已建立。

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

PL/SQL 過程已成功完成。

二者的執行時間仍然相差不多，上面的差異中redo和undo都不是關注的內容，採用SELECT INTO的方式會導致共享池的爭用，而採用賦值的方式會佔用更多的PGA記憶體。

LATCH是序列操作，一遍情況下記憶體不是問題，因此應該儘量選擇來提高併發效能。不過根據測試也可以看到，二者的效能差異很小，選擇哪種方法都不會帶來明顯的效能變化。