一次PLSQL處理LOB欄位的優化

前面一篇文章介紹了利用LOB來避免字串超長的問題。在編寫過程中，碰到了LOB處理效率比較低的問題，這裡簡單介紹了一下優化LOB處理的過程。

字串連線超長的解決（二）：http://yangtingkun.itpub.net/post/468/482122

 

 

問題源自上面給出的連結。為了實現字串的聚集合並，因此用LOB來避免字串長度超過VARCHAR2型別長度的限制。

第一次PL/SQL程式碼的實現為：

SQL> CREATE OR REPLACE TYPE T_LINK_LOB AS OBJECT (
  2  V_LOB CLOB,
  3  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_LOB) RETURN NUMBER,
  4  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER,
  5  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER,
  6  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER
  7  )
  8  /

型別已建立。

SQL> CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_LOB) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK_LOB(NULL);
  5  DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
  6  DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
  7  RETURN ODCICONST.SUCCESS;
  8  END;
  9 
 10  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS
 11  BEGIN
 12  DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
 13  RETURN ODCICONST.SUCCESS;
 14  END;
 15 
 16  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
 17  BEGIN
 18  DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
 19  DBMS_LOB.APPEND(RETURNVALUE, SELF.V_LOB);
 20  DBMS_LOB.TRIM(RETURNVALUE, DBMS_LOB.GETLENGTH(RETURNVALUE) - 1);
 21  RETURN ODCICONST.SUCCESS;
 22  END;
 23 
 24  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER IS
 25  BEGIN
 26  NULL;
 27  RETURN ODCICONST.SUCCESS;
 28  END;
 29  END;
 30  /

型別主體已建立。

SQL> CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB
  2  AGGREGATE USING T_LINK_LOB;
  3  /

函式已建立。

這時呼叫這個聚集函式的效率比較低：

SQL> SET TIMING ON
SQL> SET AUTOT TRACE STAT
SQL> SELECT F_LINK_LOB(SYNONYM_NAME) FROM ALL_SYNONYMS WHERE ROWNUM <= 10000;

已用時間:  00: 00: 39.67

Statistics
----------------------------------------------------------
        153  recursive calls
    3125947  db block gets
     211709  consistent gets
         43  physical reads
          0  redo size
    1990553  bytes sent via SQL*Net to client
    1166302  bytes received via SQL*Net from client
       6443  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed

檢查了一下DBMS_LOB包，發現用COPY命令可以替換APPEND和TRIM過程，這樣可以減少一次LOB物件的讀寫：

SQL> CONN TEST/TEST
已連線。
SQL> SET TIMING OFF
SQL> CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LINK_LOB) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK_LOB(NULL);
  5  DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
  6  DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
  7  RETURN ODCICONST.SUCCESS;
  8  END;
  9 
 10  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS
 11  BEGIN
 12  DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
 13  RETURN ODCICONST.SUCCESS;
 14  END;
 15 
 16  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
 17  BEGIN
 18  DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
 19  DBMS_LOB.COPY(RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
 20  RETURN ODCICONST.SUCCESS;
 21  END;
 22 
 23  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER IS
 24  BEGIN
 25  NULL;
 26  RETURN ODCICONST.SUCCESS;
 27  END;
 28  END;
 29  /

型別主體已建立。

SQL> CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB
  2  AGGREGATE USING T_LINK_LOB;
  3  /

函式已建立。

SQL> SET TIMING ON
SQL> SET AUTOT TRACE STAT
SQL> SELECT F_LINK_LOB(SYNONYM_NAME) FROM ALL_SYNONYMS WHERE ROWNUM <= 10000;

已用時間:  00: 00: 39.46

Statistics
----------------------------------------------------------
          7  recursive calls
    3125933  db block gets
     211660  consistent gets
          0  physical reads
          0  redo size
    1990553  bytes sent via SQL*Net to client
    1166302  bytes received via SQL*Net from client
       6443  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

雖然邏輯讀和執行時間都有所減少，但是與總執行時間相比優化效果可以忽略，再次觀察PL/SQL，發現預設情況下沒有使用NOCOPY方式處理輸出引數，再次修改程式：

SQL> CONN TEST/TEST
已連線。
SQL> SET TIMING OFF
SQL> CREATE OR REPLACE TYPE T_LINK_LOB AS OBJECT (
  2  V_LOB CLOB,
  3  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB) RETURN NUMBER,
  4  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER,
  5  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER,
  6  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER
  7  )
  8  /

型別已建立。

SQL> CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK_LOB(NULL);
  5  DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
  6  DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
  7  RETURN ODCICONST.SUCCESS;
  8  END;
  9 
 10  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS
 11  BEGIN
 12  DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
 13  RETURN ODCICONST.SUCCESS;
 14  END;
 15  
 16  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
 17  BEGIN
 18  DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
 19  DBMS_LOB.COPY(RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
 20  RETURN ODCICONST.SUCCESS;
 21  END;
 22 
 23  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER IS
 24  BEGIN
 25  NULL;
 26  RETURN ODCICONST.SUCCESS;
 27  END;
 28  END;
 29  /

型別主體已建立。

SQL> CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB
  2  AGGREGATE USING T_LINK_LOB;
  3  /

函式已建立。

SQL> SET TIMING ON
SQL> SET AUTOT TRACE STAT
SQL> SELECT F_LINK_LOB(SYNONYM_NAME) FROM ALL_SYNONYMS WHERE ROWNUM <= 10000;

已用時間:  00: 00: 35.14

Statistics
----------------------------------------------------------
          7  recursive calls
    3125933  db block gets
     211720  consistent gets
          0  physical reads
          0  redo size
    1990553  bytes sent via SQL*Net to client
    1166302  bytes received via SQL*Net from client
       6443  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

新增了NOCOPY後，執行時間提高了1/8。可以看到對於LOB欄位的處理方式對效能的影響還是很大的，如果在建立臨時LOB物件的時候不指定CACHE選項，可以看到執行時間要比CACHE的速度慢一倍以上：

SQL> CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK_LOB(NULL);
  5  DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, FALSE, DBMS_LOB.CALL);
  6  DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
  7  RETURN ODCICONST.SUCCESS;
  8  END;
  9 
 10  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS
 11  BEGIN
 12  DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
 13  RETURN ODCICONST.SUCCESS;
 14  END;
 15  
 16  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
 17  BEGIN
 18  DBMS_LOB.CREATETEMPORARY(RETURNVALUE, FALSE, DBMS_LOB.SESSION);
 19  DBMS_LOB.COPY(RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
 20  RETURN ODCICONST.SUCCESS;
 21  END;
 22 
 23  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER IS
 24  BEGIN
 25  NULL;
 26  RETURN ODCICONST.SUCCESS;
 27  END;
 28  END;
 29  /

型別主體已建立。

已用時間:  00: 00: 00.07
SQL> CONN TEST/TEST
已連線。
SQL> SET AUTOT TRACE STAT
SQL> SELECT F_LINK_LOB(SYNONYM_NAME) FROM ALL_SYNONYMS WHERE ROWNUM <= 10000;

已用時間:  00: 01: 33.90

Statistics
----------------------------------------------------------
          6  recursive calls
    2960983  db block gets
     211636  consistent gets
     181384  physical reads
          0  redo size
    1990553  bytes sent via SQL*Net to client
    1166302  bytes received via SQL*Net from client
       6443  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

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