Вопрос по plsql, sql, clob, oracle11g, oracle – Выступление СУБСТР на CLOB

25

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

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

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

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

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

Test results:

+000000000 00:00:00.004000000 (VARCHAR2)
+000000000 00:00:00.298000000 (CLOB SUBSTR)
+000000000 00:00:00.356000000 (DBMS_LOB.SUBSTR)

Test code:

<code>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;
</code>
На моих машинах DBMS_LOB.SUBSTR немного медленнее, чем CLOB_SUBSTR (20%). И оба более медленные, чем varchar2 (в 70 раз медленнее). Я бегу на 11gR2. Florin Ghita
AIX 6 (не знаю несовершеннолетнего ... 2 или 3). Это 128-ядерный компьютер IBM. какая у вас ОС и машина? Florin Ghita
Примечание: тест три14,1 где остальные1,14, Я также протестировал бы что-то вроде10000, 5000 Дело в том, что вы пытаетесь преодолеть лимит 4К в VARCHAR. Кроме того, поскольку результаты примерно в 75 раз медленнее для не VARCHAR, вы можете взглянуть на алгоритм, который работает с несколькими VARCHAR?[Such as a normalised table where one field is the 'sequence_id' showing the relative position of this string, and the other is the VARCHAR], Наконец, хотя есть большойrelative разница,absolute разница невелика Так имеет ли это значение?[Pre-optimisation] MatBailie
@Dems: Спасибо за ваш вклад!14,1 а также1,14 правильно (спасибо Oracle за согласованные API). Я пытаюсь сломать32767 Ограничение в байтах (PL / SQL, а не SQL) и результаты более или менее одинаковы при использовании текста с такой длиной(LPAD('X', 32767, 'X')), Я думал об этом решении с несколькими таблицами Varchar, но я хотел бы избежать его :) И это имеет значение, поскольку процедура вызывается очень часто, но больше всего мне интересно, есть ли альтернативы ... Peter Lang
@FlorinGhita: Спасибо! Какую ОС вы используете, в случае, если это имеет значение? Peter Lang

Ваш Ответ

4   ответа
2

что это очень старая версия, но, тем не менее, она может иметь отношение к людям в старых системах. Это похоже на проблему преобразования типов данных. Судя по тому, что я заметил, глядя на эффект @ bernhard.weingartner, тип данных аргументов смещения и количества, кажется, имеет огромное значение.

Это выполняется в Linux 11.2.0.3 (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) медленная, а явная передача числа - быстрая.

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

2

что на 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)
17

damn ложь и ориентиры ...)

Я перезапустил ваш тест 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)

В тот же день, тот же вывод.

Ктулху фхтагн.

(Еще раз, дорогие друзья, еще раз ...)

Перезапустил тесты, изменив размер 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.

& quot; Itanium ... YMMV & quot; - Ваши деньги могут исчезнуть?
Спасибо! Я попробовал ваш последний тест (продлениеl_text до 50 символов, так как 30 больше не имеет смысла), но результаты все равно были сопоставимы с моим первоначальным тестом (после его вызова несколько раз):0.006, 0.679, 1.064, Какую версию Oracle вы используете? Можете ли вы воспроизвести свои результаты при звонке несколько раз? Peter Lang
Какая архитектура процессора? Сколько L1 и L2 кеша? Сколько жертв было принесено в жертву пожертвованиям, принесенным Древним Богам? Маленькие вещи много значат ...: - & gt;
Это странно. Я попробовал свои тесты на трех разных экземплярах (11.2 в Windows, 11.2 в Linux и 10.2 в Linux), и результаты более или менее такие же, как в моем посте. Было бы здорово получить еще результаты от других людей ... Peter Lang
Oracle 11.1. И да, результаты тестов повторяемы. Я изменил циклы для повторения 1 миллион раз каждый, а затем разделил полученный интервал времени на 10, чтобы сделать их примерно сопоставимыми с исходными тестами. Я повторно запускал тесты несколько раз, и каждый результат был сопоставим.
2

Oracle Database 11g Enterprise Edition, выпуск 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)
@PeterLang Конечно, вещь. Redhat Enterprise Linux: 2.6.9-67.ELsmp
Спасибо! Не могли бы вы рассказать, какую ОС вы используете, если это изменит? Peter Lang

Похожие вопросы