对CLOB SUBSTR的性能(Performance of SUBSTR on CLOB)

我有一个做了很多的PL / SQL程序SUBSTR S于一个VARCHAR2参数。 我想删除长度的限制，所以我试图将其更改为CLOB 。

工作正常，但性能会受到影响，所以我做了一些测试（根据这些测试从2005）。

---

更新 ：我可以重现这与不同的Oracle版本和不同的硬件几种不同的情况下， dbms_lob.substr总是比明显慢substr(CLOB)而且比慢了许多SUBSTR(VARCHAR2)

上述鲍勃的结果和链接中的测试告诉一个不同的故事。

谁能解释这一点，或至少复制或者Bob的还是我的结果？ 谢谢！

---

检测结果：

0亿00:00:00。 0.04亿 （VARCHAR2）
0亿00:00:00。 2.98亿 （CLOB SUBSTR）
0亿00:00:00。 3.56亿 （DBMS_LOB.SUBSTR）

测试代码：

DECLARE
  l_text   VARCHAR2(30) := 'This is a test record';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (VARCHAR2)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (CLOB SUBSTR)');

  t := SYSTIMESTAMP;
  FOR i IN 1..100000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR)');
END;

Answer 1:

（谎言， 该死的谎言和基准...）

我重新运行测试10次，扩大字符串，它是一个完整的30个字符，并得到了以下平均结果：

+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)

然后我改变了子范围5,14（14,5对DBMS_LOB.SUBSTR），并得到：

+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)

后来我改变至17,14（14,17用于DBMS_LOB.SUBSTR），得到了

+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)

最后，我改变了范围25,14（14,25用于DBMS_LOB.SUBSTR），得到了

+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)

我的结论是，对CLOB工作时，就是它的最好使用DBMS_LOB.SUBSTR，因为它似乎比使用SUBSTR对一个“正常”的VARCHAR2有没有有效的性能损失。 针对CLOB SUBSTR似乎从一个显著的性能损失受苦。 备案 - OS = HP / UX（Unix的变体），Oracle版本= 11.1，处理器= HP安腾2-plex上。 因人而异。

分享和享受。

---

而且，因为如果它是值得做，就值得过这样做，在这里与扩大到32767个字符字符串一些结果。 子字符串与每组结果的给定范围：

1, 25000
+000000000 00:00:00.198466400 (VARCHAR2)
+000000000 00:00:02.870958700 (CLOB SUBSTR)
+000000000 00:00:00.174490100 (DBMS_LOB.SUBSTR)

1000, 25000
+000000000 00:00:00.253447900 (VARCHAR2)
+000000000 00:00:02.491790500 (CLOB SUBSTR)
+000000000 00:00:00.193560100 (DBMS_LOB.SUBSTR)

10000, 25000
+000000000 00:00:00.217812000 (VARCHAR2)
+000000000 00:00:02.268794800 (CLOB SUBSTR)
+000000000 00:00:00.222200200 (DBMS_LOB.SUBSTR)

同一天，同样的结论。

邪神fhtagn。

---

（再一次对违反，亲爱的朋友们，再一次...）

重新跑的基准，改变CLOB的大小3276700，并开始为2475000长度25000我得到采取子从中间：

+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)

（请注意，更改仅影响最后两个测试）。

与...同样的结果，不同的日子。

因人而异。

Answer 2:

运行脚本以下系统上三次：

Oracle数据库11g企业版发布11.1.0.7.0 - 64位生产

下面是结果：

+000000000 00:00:00.007787000 (VARCHAR2)
+000000000 00:00:03.093258000 (CLOB SUBSTR)
+000000000 00:00:00.340017000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.019460000 (VARCHAR2)
+000000000 00:00:03.302425000 (CLOB SUBSTR)
+000000000 00:00:00.336915000 (DBMS_LOB.SUBSTR)

+000000000 00:00:00.007773000 (VARCHAR2)
+000000000 00:00:03.210619000 (CLOB SUBSTR)
+000000000 00:00:00.336689000 (DBMS_LOB.SUBSTR)

Answer 3:

我看到，在11gR1中的测试运行顺利进行DBMS_LOB.substr，但11gR2的功能缓慢。

下面我对测试Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production上AIX6。

+000000000 00:00:00.009440000 (VARCHAR2)
+000000000 00:00:00.749113000 (CLOB SUBSTR)
+000000000 00:00:01.177685000 (DBMS_LOB.SUBSTR)

Answer 4:

我知道这是很老，但仍可能有关的人在旧系统。 这看起来像一个数据类型转换的问题。 基于东西，我注意到在看效果@ bernhard.weingartner锯，的偏移量和参数的数据类型似乎产生巨大的变化。

这是在11.2.0.3在Linux（OEL 5.6）上运行，并增加了一百万次迭代只是为了让差异更加明显：

DECLARE
  l_text   VARCHAR2(30) := 'This is a test record';
  l_clob   CLOB := l_text;
  l_substr VARCHAR2(30);
  t TIMESTAMP;
BEGIN
  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_text,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (VARCHAR2)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := SUBSTR(l_clob,1,14);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (CLOB SUBSTR)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14,1);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with 14,1)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,14.0,1.0);
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with 14.0,1.0)');

  t := SYSTIMESTAMP;
  FOR i IN 1..1000000 LOOP
    l_substr := DBMS_LOB.SUBSTR(l_clob,cast(14 as number), cast(1 as number));
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t || ' (DBMS_LOB.SUBSTR with casts)');
END;
/

+000000000 00:00:00.043019000 (VARCHAR2)
+000000000 00:00:03.671621000 (CLOB SUBSTR)
+000000000 00:00:05.704337000 (DBMS_LOB.SUBSTR with 14,1)
+000000000 00:00:00.040097000 (DBMS_LOB.SUBSTR with 14.0,1.0)
+000000000 00:00:00.040907000 (DBMS_LOB.SUBSTR with casts)

该11gR2的文档显示形式参数类型的INTEGER，但实际上传递一个整数（或PLS_INTEGER，或BINARY_DOUBLE）是缓慢的，而明确地传递了一些快。

从你原来的问题和Bob的结果，这看起来喜欢的事，11.1和11.2之间变化。 我没有12C实例来测试，如果它再次更改，因此不知道。 无论是由于改变dbms_lob或更宽的变化，以PL / SQL默认情况下是如何处理的数值是不明确的。 我还没有发现在MOS任何看起来相关。

文章来源: Performance of SUBSTR on CLOB