ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次PLSQL处理LOB字段的优化

一次PLSQL处理LOB字段的优化

原创 Linux操作系统 作者:yangtingkun 时间:2009-04-12 23:01:39 0 删除 编辑

前面一篇文章介绍了利用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命令可以替换APPENDTRIM过程,这样可以减少一次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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Solaris下安装VNC
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10455280