Pytanie w sprawie sql, clob, oracle, oracle11g, plsql – Wydajność SUBSTR na CLOB

25

Mam procedurę PL / SQL, która robi dużoSUBSTRs na aVARCHAR2 parametr. Chciałbym usunąć limit długości, więc próbowałem go zmienić naCLOB.

Działa dobrze, ale wydajność cierpi, więc zrobiłem kilka testów (na podstawiete testy od 2005).

AKTUALIZACJA: Mogę to odtworzyć w kilku różnych przypadkach z różnymi wersjami Oracle i innym sprzętem,dbms_lob.substr jest zawsze zauważalnie wolniejszy niżsubstr(CLOB)i dużo wolniej niżSUBSTR(VARCHAR2).

Wyniki Boba i testy w powyższym linku przedstawiają inną historię.

Czy ktoś może to wyjaśnić, a przynajmniej odtworzyć wyniki Boba lub moje? Dzięki!

Wyniki testów:

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

Kod testowy:

<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>
@Dems: Dzięki za Twój wkład! The14,1 i1,14 jest poprawne (dzięki Oracle za spójne API). Próbuję złamać32767 Limit bajtów (PL / SQL, a nie SQL), a wyniki są mniej więcej takie same podczas używania tekstu o tej długości(LPAD('X', 32767, 'X')). Pomyślałem o tym rozwiązaniu z wieloma tabelami, ale chciałbym tego uniknąć :) I to ma znaczenie, ponieważ procedura jest często wywoływana, ale przede wszystkim jestem ciekawy, czy istnieją alternatywy ... Peter Lang
Uwaga: Test trzeci to14,1 gdzie są inni1,14. Testowałbym też coś takiego10000, 5000 ponieważ chodzi o to, że chcesz przekroczyć limit 4k VARCHAR. Ponadto, ponieważ wyniki są o około 75 razy wolniejsze dla non VARCHAR, czy jesteś w stanie przyjrzeć się algorytmowi, który zajmuje się wieloma VARCHAR?[Takich jak znormalizowana tabela, w której jedno pole to „sekwencja_id” pokazująca względną pozycję tego ciągu, a druga to VARCHAR]. Wreszcie, choć jest dużakrewny różnica,absolutny różnica jest niska. Czy to ma znaczenie?[Optymalizacja wstępna] MatBailie
AIX 6 (nie znam mniejszego ... 2 lub 3). To 128-rdzeniowa maszyna IBM. jaki masz system operacyjny i komputer? Florin Ghita
@ FlorinGhita: Dzięki! Który system operacyjny używasz, na wypadek, gdyby to miało znaczenie? Peter Lang
Na moich komputerach DBMS_LOB.SUBSTR jest trochę wolniejszy niż CLOB_SUBSTR (20%). I oba mooore wolniej niż varchar2 (70 razy wolniej). Biegam na 11gR2. Florin Ghita

Twoja odpowiedź

4   odpowiedź
2

Wiem, że jest to bardzo stary, ale wciąż może być odpowiedni dla ludzi na starszych systemach. Wygląda to na problem z konwersją typu danych. Na podstawie czegoś, co zauważyłem, patrząc na efekt @ bernhard.weingartner zobaczyłem, typ danych argumentów przesunięcia i ilości wydaje się mieć ogromną różnicę.

Jest to uruchamiane w wersji 11.2.0.3 w systemie Linux (OEL 5.6) i zwiększone do miliona iteracji, aby różnice były jeszcze bardziej oczywiste:

<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..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;
/
</code>
<code>+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)
</code>

TheDokumenty 11gR2 pokaż formalne parametry jako typ INTEGER, ale faktycznie przekazanie liczby całkowitej (lub pls_integer lub binary_double) jest powolne, podczas gdy jawne podanie liczby jest szybkie.

Z twojego pierwotnego pytania i wyników Boba wygląda coś, co zmieniło się między 11.1 a 11.2. Nie mam instancji 12c do przetestowania, więc nie wiem, czy została zmieniona ponownie. Czy to z powodu zmianydbms_lob lub szersza zmiana domyślnego sposobu, w jaki PL / SQL obsługuje wartości numeryczne, nie jest jasna. Nie znalazłem niczego w MOS, który wygląda na odpowiedni.

2

Uruchomiłeś skrypt trzy razy w następującym systemie:

Oracle Database 11g Enterprise Edition wersja 11.1.0.7.0 - 64-bitowa produkcja

Oto wyniki:

<code>+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)
</code>
@PeterLang Sure rzecz. Redhat Enterprise Linux: 2.6.9-67.ELsmp tp9
Dzięki! Czy chciałbyś podzielić się tym, którego systemu operacyjnego używasz, na wypadek gdyby miało to jakieś znaczenie? Peter Lang
17

(Kłamie,cholerny kłamstwa i wzorce ...)

Ponownie uruchomiłem test 10 razy, rozszerzając ciąg, aby był pełny 30 znaków, i otrzymałem następujące uśrednione wyniki:

<code>+000000000 00:00:00.011694200 (VARCHAR2)
+000000000 00:00:00.901000600 (CLOB SUBSTR)
+000000000 00:00:00.013169200 (DBMS_LOB.SUBSTR)
</code>

Następnie zmieniłem zakres podciągów na 5,14 (14,5 dla DBMS_LOB.SUBSTR) i dostałem:

<code>+000000000 00:00:00.011731000 (VARCHAR2)
+000000000 00:00:01.010840000 (CLOB SUBSTR)
+000000000 00:00:00.011427000 (DBMS_LOB.SUBSTR)
</code>

Następnie zmieniłem zakres na 17,14 (14,17 dla DBMS_LOB.SUBSTR) i dostałem

<code>+000000000 00:00:00.013578900 (VARCHAR2)
+000000000 00:00:00.964527400 (CLOB SUBSTR)
+000000000 00:00:00.011416800 (DBMS_LOB.SUBSTR)
</code>

W końcu zmieniłem zakres na 25,14 (14,25 dla DBMS_LOB.SUBSTR) i dostałem

<code>+000000000 00:00:00.011210200 (VARCHAR2)
+000000000 00:00:00.916439800 (CLOB SUBSTR)
+000000000 00:00:00.013781300 (DBMS_LOB.SUBSTR)
</code>

Moja konkluzja jest taka, że ​​podczas pracy przeciwko CLOB najlepiej jest użyć DBMS_LOB.SUBSTR, ponieważ wydaje się, że nie ma w rzeczywistości żadnego ograniczenia wydajności w porównaniu z użyciem SUBSTR-a w stosunku do „normalnego” VARCHAR2. SUBSTR przeciwko CLOB wydaje się ponosić znaczną karę za wydajność. Dla przypomnienia - OS = HP / UX (wariant Unix), wersja Oracle = 11.1, procesor = HP Itanium 2-plex. YMMV.

Podziel się i ciesz.

A ponieważ warto to zrobić, to warto to robić, oto kilka wyników z ciągami rozszerzonymi do 32767 znaków. Zakresy podciągów podane dla każdego zestawu wyników:

<code>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)
</code>

Ten sam dzień, ten sam wniosek.

Cthulhu fhtagn.

(Jeszcze raz do naruszenia, drodzy przyjaciele, jeszcze raz ...)

Ponownie uruchomiłem testy porównawcze, zmieniając rozmiar CLOB na 3276700, a biorąc podciąg z połowy zaczynając od 2475000 na długość 25000 otrzymuję:

<code>+000000000 00:00:00.176883200 (VARCHAR2)
+000000000 00:00:02.069482600 (CLOB SUBSTR)
+000000000 00:00:00.175341500 (DBMS_LOB.SUBSTR)
</code>

(Zauważ, że zmiany dotyczą tylko dwóch ostatnich testów).

I ... te same wyniki, inny dzień.

YMMV.

@PeterLang - dodano testy dla dużego (3 meg) CLOB. Takie same wyniki. Bob Jarvis
@ BobJarvis: Byłbym zaskoczony, gdyby to miało tak dużą różnicę, ale kto wie ... Czy masz referencje do tych ustaleń? Widzę to tylkoOCILobCopy jest przestarzałe i toOCILobCopy2 musi być używany dla obiektów LOB o rozmiarze większym niż 4 GB. Peter Lang
Dzięki jeszcze raz. Twójdbms_lob.substr wydaje się działać inaczej niż moja. Chciałbym wiedzieć, dlaczego ... Peter Lang
Moje komentarze na temat możliwych różnic między OCILobCopy i OCILobCopy2 były z mojej strony tylko przypuszczeniami, a nie faktami. Widzę, że @ tp9 opublikował kilka wyników, które są mniej więcej takie same jak te, które znalazłem. Użył także 11.1. Chciałbym mieć 11.2 DB do przetestowania. Bob Jarvis
2

Widzę, że na testach 11gR1 przebiegało gładko dla DBMS_LOB.substr, ale dla 11gR2 funkcja jest wolna.

Poniżej mojego testuOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production w AIX6.

<code>+000000000 00:00:00.009440000 (VARCHAR2)
+000000000 00:00:00.749113000 (CLOB SUBSTR)
+000000000 00:00:01.177685000 (DBMS_LOB.SUBSTR)
</code>

Powiązane pytania