Выполнение SUBSTR на CLOB

У меня есть процедура PL/SQL, которая делает много SUBSTR для параметра VARCHAR2. Я хотел бы удалить ограничение длины, поэтому я попытался изменить его на CLOB.

Работает нормально, но производительность страдает, поэтому я провел несколько тестов (на основе этих тестов с 2005 года).

UPDATE. Я могу воспроизвести это в нескольких разных экземплярах с разными версиями Oracle и различным оборудованием, dbms_lob.substr всегда заметнее медленнее, чем substr(CLOB), и намного медленнее, чем SUBSTR(VARCHAR2).

Результаты Боба и тесты в приведенной выше ссылке рассказывают другую историю.

Может кто-нибудь объяснить это или хотя бы воспроизвести либо Боб, либо мои результаты? Спасибо!

Результаты тестирования:

+000000000 00:00:00. 004000000 (VARCHAR2)
+000000000 00:00:00. 298000000 (CLOB SUBSTR)
+000000000 00:00:00. 356000000 (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;

Ответ 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. SUBSTR против CLOB, похоже, страдает от значительного нарушения производительности. Для записи - OS = HP/UX (вариант Unix), версия Oracle = 11.1, процессор = HP Itanium 2-plex. YMMV.

Поделитесь и наслаждайтесь.

И потому, что, если это стоит того, чтобы это стоило перепроизводства, вот еще несколько результатов с строками, расширенными до 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)

В тот же день, такое же заключение.

Cthulhu 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)

(Обратите внимание, что изменения влияют только на последние два теста).

И... такие же результаты, другой день.

YMMV.

Ответ 2

Разверните свой script три раза в следующей системе:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Вот результаты:

+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)

Ответ 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)

Ответ 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 docs отображают формальные параметры как тип INTEGER, но фактически передача целого числа (или pls_integer или binary_double) происходит медленно, а явно прохождение номера происходит быстро.

Из вашего исходного вопроса и результатов Боба это выглядит как нечто, что изменилось между 11.1 и 11.2. У меня нет экземпляра 12c для тестирования, поэтому не знаю, изменилось ли оно снова. Из-за изменения в dbms_lob или более широкого изменения того, как PL/SQL обрабатывает числовые значения по умолчанию, неясно. Я ничего не нашел в MOS, который выглядит релевантным.