Frage an oracle, oracle11g, clob, sql, plsql – Leistung von SUBSTR auf CLOB

25

Ich habe eine PL / SQL-Prozedur, die viel leistetSUBSTRs auf aVARCHAR2 Parameter. Ich möchte das Längenlimit entfernen und habe versucht, es in zu ändernCLOB.

Funktioniert gut, aber die Leistung leidet, also habe ich einige Tests durchgeführt (basierend aufdiese Tests ab 2005).

AKTUALISIEREN: Ich kann dies auf verschiedenen Instanzen mit verschiedenen Oracle-Versionen und unterschiedlicher Hardware reproduzieren,dbms_lob.substr fällt immer langsamer auf alssubstr(CLOB)und viel langsamer alsSUBSTR(VARCHAR2).

Bobs Ergebnisse und die Tests im obigen Link erzählen eine andere Geschichte.

Kann jemand dies erklären oder zumindest die Ergebnisse von Bob oder mir reproduzieren? Vielen Dank!

Testergebnisse:

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

Testcode:

<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>
AIX 6 (ich kenne den Nebenfach ... 2 oder 3 nicht). Es ist eine IBM-Maschine mit 128 Kernen. Welches Betriebssystem und welche Maschine haben Sie? Florin Ghita
@Dems: Danke für deine Eingabe! Das14,1 und1,14 ist richtig (danke Oracle für die konsistenten APIs). Ich versuche das zu brechen32767 Das Byte-Limit (PL / SQL, nicht SQL) und die Ergebnisse sind mehr oder weniger gleich, wenn Text mit dieser Länge verwendet wird(LPAD('X', 32767, 'X')). Ich habe über diese Lösung mit mehreren Variablen nachgedacht, aber ich würde sie gerne vermeiden :) Und das ist auch wichtig, da die Prozedur wirklich oft aufgerufen wird, aber vor allem bin ich gespannt, ob es Alternativen gibt ... Peter Lang
Auf meinen Rechnern ist DBMS_LOB.SUBSTR etwas langsamer als CLOB_SUBSTR (20%). Und beide langsamer als varchar2 (70 mal langsamer). Ich laufe auf 11gR2. Florin Ghita
Hinweis: Test drei ist14,1 wo die anderen sind1,14. Ich würde auch sowas testen10000, 5000 Der springende Punkt ist, dass Sie versuchen, das 4k-Limit von VARCHAR zu überschreiten. Da die Ergebnisse für Nicht-VARCHAR etwa 75x langsamer sind, können Sie sich einen Algorithmus ansehen, der sich mit mehreren VARCHARs befasst?[Zum Beispiel eine normalisierte Tabelle, in der ein Feld die 'sequence_id' ist, die die relative Position dieser Zeichenfolge anzeigt, und das andere die VARCHAR]. Schließlich, obwohl es groß istrelativ Unterschied, derabsolut Unterschied ist gering. Also ist es wichtig?[Voroptimierung] MatBailie
@FlorinGhita: Danke! Welches Betriebssystem verwenden Sie, falls dies einen Unterschied macht? Peter Lang

Deine Antwort

4   die antwort
2

dass dies sehr alt ist, aber für Leute auf älteren Systemen immer noch relevant sein kann. Dies scheint ein Problem bei der Datentypkonvertierung zu sein. Basierend auf etwas, das ich beim Betrachten des Effekts bei bernhard.weingartner bemerkt habe, scheint der Datentyp der Offset- und Betragsargumente einen großen Unterschied zu machen.

Dies wird unter Linux (OEL 5.6) unter 11.2.0.3 ausgeführt und auf eine Million Iterationen erhöht, um die Unterschiede noch deutlicher zu machen:

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

Das11gR2 docs Zeigen Sie die Formalparameter als Typ INTEGER an, aber die tatsächliche Übergabe einer Ganzzahl (oder pls_integer oder binary_double) ist langsam, während die explizite Übergabe einer Zahl schnell ist.

Aus Ihrer ursprünglichen Frage und den Ergebnissen von Bob geht hervor, dass sich etwas zwischen 11.1 und 11.2 geändert hat. Ich habe keine 12c-Instanz zum Testen, daher weiß ich nicht, ob sie erneut geändert wurde. Ob es an einer Veränderung liegtdbms_lob oder eine umfassendere Änderung, wie PL / SQL standardmäßig mit numerischen Werten umgeht, ist nicht klar. Ich habe auf MOS nichts gefunden, das relevant aussieht.

2

dass auf 11gR1 Tests für DBMS_LOB.substr glatt liefen, aber für 11gR2 ist die Funktion langsam.

Unten mein Test amOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production unter AIX6.

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

Verdammt Lügen und Benchmarks ...)

Ich habe Ihren Test zehnmal wiederholt und die Zeichenfolge auf 30 Zeichen erweitert. Dabei wurden die folgenden gemittelten Ergebnisse erzielt:

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

Ich habe dann den Teilstring-Bereich auf 5,14 geändert (14,5 für DBMS_LOB.SUBSTR) und Folgendes erhalten:

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

Ich habe dann den Bereich auf 17,14 geändert (14,17 für DBMS_LOB.SUBSTR) und bekam

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

Schließlich habe ich den Bereich auf 25,14 geändert (14,25 für DBMS_LOB.SUBSTR) und bekam

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

Meine Schlussfolgerung ist, dass es bei der Arbeit mit CLOBs am besten ist, DBMS_LOB.SUBSTR zu verwenden, da es im Vergleich zur Verwendung von SUBSTR mit einem "normalen" VARCHAR2 praktisch keine Leistungseinbußen zu haben scheint. SUBSTR gegen ein CLOB scheint unter einer erheblichen Leistungsbeeinträchtigung zu leiden. Für den Datensatz - Betriebssystem = HP / UX (Unix-Variante), Oracle-Version = 11.1, Prozessor = HP Itanium 2-Plex. YMMV.

Teile und genieße.

Und weil es sich lohnt, zu viel zu tun, gibt es hier weitere Ergebnisse mit den Zeichenfolgen, die auf 32767 Zeichen erweitert wurden. Teilbereiche, die mit jedem Satz von Ergebnissen angegeben werden:

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

Gleicher Tag, gleiche Schlussfolgerung.

Cthulhu fhtagn.

(Noch einmal bis zum Bruch, liebe Freunde, noch einmal ...)

Führen Sie die Benchmarks erneut aus, ändern Sie die Größe des CLOB in 3276700 und nehmen Sie die Teilzeichenfolge aus der Mitte, beginnend bei 2475000 für die Länge 25000. Ich erhalte:

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

(Beachten Sie, dass Änderungen nur die letzten beiden Tests betreffen.)

UND ... gleiche Ergebnisse, anderer Tag.

YMMV.

Meine Kommentare zu möglichen Unterschieden zwischen OCILobCopy und OCILobCopy2 waren nur Vermutungen meinerseits, keine Fakten. Ich sehe, dass @ tp9 einige Ergebnisse veröffentlicht hat, die in etwa denen entsprechen, die ich gefunden habe. Er verwendete auch 11.1. Ich wünschte, ich hätte eine 11.2 DB zum Testen. Bob Jarvis
"Itanium ... YMMV" - kann Ihr Geld verschwinden? Jon Heller
Das ist komisch. Ich habe meine Tests auf drei verschiedenen Instanzen getestet (11.2 unter Windows, 11.2 unter Linux und 10.2 unter Linux), und die Ergebnisse sind mehr oder weniger dieselben wie in meinem Beitrag. Wäre toll, wenn noch mehr Ergebnisse von anderen Leuten kommen würden ... Peter Lang
@PeterLang - Es wurden Tests für große (3-Mega-) CLOBs hinzugefügt. Gleiche Ergebnisse. Bob Jarvis
2

Oracle Database 11g Enterprise Edition Version 11.1.0.7.0 - 64-Bit-Produktion

Hier sind die Ergebnisse:

<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 Sicher, was. Redhat Enterprise Linux: 2.6.9-67.ELsmp tp9
Vielen Dank! Möchten Sie mitteilen, welches Betriebssystem Sie verwenden, falls dies einen Unterschied macht? Peter Lang

Verwandte Fragen