DBMS_SESSION包小議（六）

除了使用ALTER SESSION設定會話的狀態，利用V$SESSION查詢會話狀態，Oracle還提供了PL/SQL介面DBMS_SESSION來查詢和設定會話相關的狀態。

描述釋放使用者記憶體的FREE_UNUSED_USER_MEMORY過程。

DBMS_SESSION包小議（一）：http://yangtingkun.itpub.net/post/468/498365

DBMS_SESSION包小議（二）：http://yangtingkun.itpub.net/post/468/498408

DBMS_SESSION包小議（三）：http://yangtingkun.itpub.net/post/468/498451

DBMS_SESSION包小議（四）：http://yangtingkun.itpub.net/post/468/498475

DBMS_SESSION包小議（五）：http://yangtingkun.itpub.net/post/468/498559

一般來說設定會話級的狀態都是透過ALTER SESSION語句，也有個別的例外，比如角色的設定是透過SET語句實現的。Oracle除了提供SQL的方法外，還提供了PL/SQL的介面，DBMS_SESSION包，將會話狀態的設定和查詢整合在這個包中。

透過一個例子來說明這個過程的功能：

SQL> CREATE TABLE T_LOB
  2  (ID NUMBER,
  3  CONTENTS CLOB);

表已建立。

SQL> SET SERVEROUT ON
SQL> DECLARE
  2     V_STR VARCHAR2(32767) := LPAD('A', 32767, 'A');
  3     V_LOB CLOB;
  4     V_PGA NUMBER;
  5  BEGIN
  6     SELECT VALUE
  7     INTO V_PGA
  8     FROM V$MYSTAT A, V$STATNAME B
  9     WHERE A.STATISTIC# = B.STATISTIC#
 10     AND NAME = 'session pga memory';
 11 
 12     DBMS_OUTPUT.PUT_LINE('BEFORE INSERT:' || V_PGA);
 13 
 14     INSERT INTO T_LOB
 15     VALUES (1, EMPTY_CLOB())
 16     RETURN CONTENTS
 17     INTO V_LOB;
 18 
 19     FOR I IN 1..100 LOOP
 20             DBMS_LOB.WRITEAPPEND(V_LOB, LENGTH(V_STR), V_STR);
 21     END LOOP;
 22 
 23     COMMIT;
 24 
 25     SELECT VALUE
 26     INTO V_PGA
 27     FROM V$MYSTAT A, V$STATNAME B
 28     WHERE A.STATISTIC# = B.STATISTIC#
 29     AND NAME = 'session pga memory';
 30 
 31     DBMS_OUTPUT.PUT_LINE('AFTER INSERT:' || V_PGA);
 32 
 33     SELECT CONTENTS
 34     INTO V_LOB
 35     FROM T_LOB
 36     WHERE ID = 1;
 37 
 38     SELECT VALUE
 39     INTO V_PGA
 40     FROM V$MYSTAT A, V$STATNAME B
 41     WHERE A.STATISTIC# = B.STATISTIC#
 42     AND NAME = 'session pga memory';
 43 
 44     DBMS_OUTPUT.PUT_LINE('AFTER SELECT:' || V_PGA);
 45 
 46     DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
 47 
 48     SELECT VALUE
 49     INTO V_PGA
 50     FROM V$MYSTAT A, V$STATNAME B
 51     WHERE A.STATISTIC# = B.STATISTIC#
 52     AND NAME = 'session pga memory';
 53 
 54     DBMS_OUTPUT.PUT_LINE('AFTER FREE:' || V_PGA);
 55  END;
 56  /
BEFORE INSERT:2965384
AFTER INSERT:10370952
AFTER SELECT:10370952
AFTER FREE:3161992

PL/SQL 過程已成功完成。

利用這個過程，可以PL/SQL過程中釋放已經不在需要使用的記憶體。在正常情況下，使用者分配的記憶體被使用完成後，並不會立即釋放給Oracle，而是預備給同樣的操作後續使用，比如分配給排序的記憶體預備為後續的排序使用。

在這個例子中，使用了10M的PGA用於存放LOB資訊，而在執行了FREE_UNUSED_USER_MEMORY過程後，這些記憶體被釋放，只保留了3M左右的基本記憶體。

SQL> DECLARE
  2     V_STR VARCHAR2(32767) := LPAD('A', 32767, 'A');
  3     V_LOB CLOB;
  4     V_PGA NUMBER;
  5  BEGIN
  6     SELECT VALUE
  7     INTO V_PGA
  8     FROM V$MYSTAT A, V$STATNAME B
  9     WHERE A.STATISTIC# = B.STATISTIC#
 10     AND NAME = 'session pga memory';
 11 
 12     DBMS_OUTPUT.PUT_LINE('BEFORE INSERT:' || V_PGA);
 13 
 14     INSERT INTO T_LOB
 15     VALUES (2, EMPTY_CLOB())
 16     RETURN CONTENTS
 17     INTO V_LOB;
 18 
 19     FOR I IN 1..100 LOOP
 20             DBMS_LOB.WRITEAPPEND(V_LOB, LENGTH(V_STR), V_STR);
 21     END LOOP;
 22 
 23     COMMIT;
 24 
 25     SELECT VALUE
 26     INTO V_PGA
 27     FROM V$MYSTAT A, V$STATNAME B
 28     WHERE A.STATISTIC# = B.STATISTIC#
 29     AND NAME = 'session pga memory';
 30 
 31     DBMS_OUTPUT.PUT_LINE('AFTER INSERT:' || V_PGA);
 32  END;
 33  /
BEFORE INSERT:3096456
AFTER INSERT:10370952

PL/SQL 過程已成功完成。

SQL> SELECT VALUE
  2  FROM V$MYSTAT A, V$STATNAME B
  3  WHERE A.STATISTIC# = B.STATISTIC#
  4  AND NAME = 'session pga memory';

     VALUE
----------
   2834312

即使不呼叫FREE_UNUSED_USER_MEMORY過程，在PL/SQL執行完成後，記憶體也會自動被釋放，而FREE_UNUSED_USER_MEMORY過程唯一的功能，就是在PL/SQL執行過程中釋放掉不再需要的使用者記憶體。