Optimizer hints Oracle 12

[defo]

Hallo

ich habe ein Problem mit einer komplexen Reportabfrage. Die Abfrage lässt sich grob in 2 Mengen unterteilen, die getrennt voneinander schnell laufen. Schnell/flott bedeutet Antwort kommt unter einer Sekunde.
Joined man die beiden Teile (outer join), denkt sich der Optimizer komische Sachen aus. Die Abfrage dauert dann z.B. 12 Minuten, statt 2x 2 Sekunden für die beiden Teile.

Was ich suche ist eine Anweisung, dem Optimizer zu sagen, "mach Dir kein Kopf, sondern einen plain join der beiden Mengen, die Du zuvor so schön geliefert hast"

Experimente:
Mach ich aus einer der beiden Mengen eine Tabelle statt die Menge abzufragen und baue die Menge statt dessen in die Abfrage ein, ist alles flott.
Das Erzeugen der Tabelle ist ebenfalls flott.

Die Gesamtmengen liegen jeweils im 2 stelligen Millionenbereich (geschätzt), die Teilergebnisse der beiden Hauptmengen im kleinen 5 stelligen Bereich.

[Christian Marquardt]

Hmm, was ich kenne sind Auswirkungen, dass die Statistik nicht aktuell ist und Oracle statt einem Hash-Zugriff einen Nested Loop macht. Es sollten die Statistik und ein Index mind. aktuell sein.
Ev.
https://docs.oracle.com/cd/B12037_01...tsref.htm#7914

[defo]

Indizes sind mehrere im Spiel, diese und die Statistiken habe ich noch nicht überprüft.
Ich schaue mir das morgen mal an, danke!

[defo]

https://docs.oracle.com/cd/B12037_01...tsref.htm#7914

Also an der Stelle war ich gestern:
z.B. NO_USE_NL
Es gibt geringfügige Unterschieder im Ausführungsplan, auch mit anderen Hinweisen, die aber mehr oder weniger nur zeigen, dass die Hints überhaupt eine Auswirkung haben.
Allerdings habe ich auch Probleme mit der Plantable bzw. dem Hinweis, dass kein Plan ausgegeben werden kann. Zur Analyse / Angabe der Queryblöcke derzeit etwas unzureichend.

Ich wühle mich mal weiter und freue mich über Hinweise.

[Christian Marquardt]

https://docs.oracle.com/database/121...n.htm#TGSQL244

könnte noch helfen.

[defo]

Der Tuning Guide ist voller guter Erklärungen, aber das Problem lag/liegt woanders.
Es gibt in der Select Clause über den beiden Teilmengen ein Funktion, die sich im Prinzip wie ein Subselect verhält.

Ich werde nun versuchen, diese Funktion ins SQL aufzunehmen oder smarter zu machen.
Danke Christian!

[Wernfried]

Zwei Vorschläge von mir:

Schau dir mal den /*+ NO_MERGE */ Hint an. Damit sagst du dem Optimizer: Versuche nicht die Queryblöcke in eine Query zusammenzufügen, sondern behalte jeden Queryblock für sich.

Andere Variante: Verwende Sub-Query Factoring (auch bekannt als Common-Table-Expresssion, CTE)

with 
   q1 as (SELECT ... -- Queryblock 1),
   q2 as (SELECT ... -- Queryblock 2)
SELECT ...
FROM q1 OUTER JOIN q2 ...

Mit dem undokumentierten Hint /*+ MATERIALIZE */ erzwingst du, dass die Teil-Ergebnisse der Queryblöcke in einer temporären Tabelle abgespeichert werden. Siehe https://dbaora.com/with-clause-and-h...ze-and-inline/

Gruss

[defo]

Hallo Wernfried,

danke für die Hinweise! Ich hab das nicht sofort mitbekommen. Unabhängig von meinem Problem hier klingen die beiden Hints vielversprechend.
Mein Problem ist/war allerdings einer Funktionsaufruf innerhalb der Select Clause in einem höheren View begraben. Diese Funktion hatte ich übersehen. Die Hints helfen nicht, und können es m.E. auch nicht. Die Funktion hat pro Satz ein parametriertes Subselect mit Aggregat gemacht, übersichtlich zu lesen, aber natürlich lahm. Ich bezweifele mittlerweile, dass das wie vom Kunden behauptet, "sonst immer ganz schnell ging".
Es ist mir gelungen, die Funktion komplett aufzulösen und einen "normalen" Join daraus zu machen, was natürlich nun viel schneller geht.

[FlexGer]

Hallo defo,

eine Idee hätte ich auch dazu.
Vielleicht kannst du mal probieren die beiden Sub Selects in die WITH Clause aufzunehmen und dann damit selektieren.
Die Vermutung ist dabei, dass die beiden einzeln ausgeführt, die Ergebnisse zwischengespeichert und erst dann verglichen werden.

Gruß

Edit: Sorry für den späten Post, nicht gesehen dass er von Februar ist.