Langsame Leistung für tief verschachteltes Unterabfrage-Factoring (CTE)

Q1: Es scheint, dass es nichts über die Berechnungszeit gibt, sondern nur einen Fehler im Optimierungsalgorithmus, der es bei der Berechnung eines Best Execution Plans verrückt macht.

F2: Es gibt eine Reihe von bekannten und behobenen Fehlern in Oracle 11.X.0.X, die sich auf die Optimierung verschachtelter Abfragen und das Abfragen von Factoring beziehen. Aber es ist sehr schwierig, ein konkretes Problem zu finden.

F3: Es gibt zwei undokumentierte Hinweise: materialize und inline , aber keiner von ihnen funktioniert für mich, während ich dein Beispiel ausprobierte. Es ist möglich, dass einige Änderungen in der Serverkonfiguration oder das Upgrade auf 11.2.0.3 die Anzahl verschachtelter with -Klauseln erhöhen: Für mich (auf 11.2.0.3 Win7 / x86) funktioniert Ihr Beispiel gut, aber die steigende Anzahl verschachtelter Tabellen auf 30 hängt Sitzung.

Workaround kann wie folgt aussehen:

Zumindest funktioniert es bei der Verschachtelung von 30 und erzeugt einen völlig anderen Ausführungsplan mit WINDOW BUFFER und VIEW anstelle von LOAD TABLE AS SELECT , SORT AGGREGATE und TABLE ACCESS FULL .

Aktualisieren

1. Gerade installiert 11.2.0.4 (Win7 / 32bit) und testen Sie es gegen die erste Abfrage. Im Verhalten des Optimierers hat sich nichts geändert.

2. Es gibt keine Möglichkeit, direkt auf ein CBO-Verhalten einzuwirken, selbst bei Verwendung von inline (undokumentiert) oder RULE (veraltet). Kann sein, dass einige Guru eine Variante kennen, aber es ist ein Top Secret für mich (und Google auch :-).

3. Dinge in einer select-Anweisung in angemessener Zeit zu tun ist möglich, wenn eine main-SELECT-Anweisung in Teile aufgeteilt und in die Funktion platziert wird, die eine Reihe von Zeilen zurückgibt (Funktion sys_refcursor oder stark typisierter Cursor) keine Wahl, wenn eine Abfrage zur Laufzeit erstellt wird.

4. Workaround mit der Verwendung von XML ist möglich, aber diese Variante sieht aus wie eine Tonsille durch das Arschloch zu entfernen (Entschuldigung):

.

Eine andere Sache über einen seltsamen Code oben ist, dass diese Variante nur anwendbar ist, wenn with Datensätze keine große Anzahl von Zeilen haben.

(Dies ist keine vollständige Antwort. Hoffentlich hilft die Information hier jemand anderem, eine bessere Antwort zu erzeugen.)

Q1: Der Optimierer schreibt die Abfrage neu, indem er alles einfügt. Die interne Anweisung verdoppelt sich mit jedem neuen Ausdruck der allgemeinen Tabelle und die Anweisungen werden schnell ginormous. Zum Beispiel erzeugt T15 eine Abfrage mit 3.162.172 Zeichen.

Code zum Verfolgen der Anweisungen:

Wenn Sie die beiden Trace-Dateien vergleichen, gibt es viele Unterschiede, aber die meisten von ihnen sehen sehr klein aus. Der wirkliche Unterschied besteht nur in einer einzigen Zeile, die direkt nach der Zeichenfolge steht: Stmt: ******* UNPARSED QUERY IS ******* . Seien Sie vorsichtig beim Öffnen der Trace-Dateien, wenn Sie größere Abfragen verfolgen. Nicht alle Redakteure können so große Zeilen verarbeiten. Und die T20-Datei war 250 MB!

SQL aus erster Spur, nach Formatierung:

SQL aus der zweiten Ablaufverfolgung, nach der Formatierung:

F2: Ich würde nicht sagen, dass sich jeder "komplexe" Ausdruck einer gemeinsamen Tabelle genauso verhalten würde. Ich habe CTEs gesehen, die viel größer waren. Es ist nur die extreme Verschachtelung, die das Problem zu sein scheint. Ich konnte keine offensichtlichen Fehler im Oracle Support finden.

ThinkJets Code sieht nach einer guten Arbeit aus. Das Verschachteln von Inline-Ansichten ist häufiger als das Verschachteln allgemeiner Tabellenausdrücke.

Q3: Es gibt wahrscheinlich einen Hinweis, um dieses Verhalten zu verhindern, aber ich bin mir nicht sicher, was es ist. Hoffentlich kann jemand, der die transformierte Version der Abfrage anzeigt, erraten, wie er es beheben kann.