ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 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命令可以替换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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-588757/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 字符串连接超长的解决（二）
下一篇： Solaris下安装VNC
请登录后发表评论 登录
全部评论
yangtingkun
暂无介绍

注册时间：2007-12-29

  • 博文量
    1964
  • 访问量
    11076238

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us 编辑准则

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593