Oracle AUSFÜHREN, SOFORTIGE änderungen erklären, plan der Abfrage
Ich habe eine gespeicherte Prozedur, ich bin Aufruf mit EXECUTE IMMEDIATE. Das Problem, das ich bin vor ist, dass die explain-plan ist anders, wenn ich den Aufruf der Prozedur direkt vs, wenn ich EXECUTE IMMEDIATE Aufruf der Prozedur. Dadurch verursacht wird, dass die Ausführungszeit zu erhöhen 5x. Der wesentliche Unterschied zwischen den Plänen ist, dass wenn ich execute immediate der optimizer ist nicht unnesting die Unterabfrage (habe ich mit Hilfe einer not EXISTS-Bedingung). Wir sind mit Rule Based Optimizer hier bei der Arbeit für die meisten Abfragen, aber dafür hat man einen Hinweis auf die Verwendung eines index, so das CBO verwendet wird (allerdings, wir sammeln keine Statistiken für Tabellen). Wir laufen Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit-Produktion.
Beispiel:
Schnell:
begin
package.procedure;
end;
/
Langsam:
begin
execute immediate 'begin package.' || proc_name || '; end;';
end;
/
Abfrage:
SELECT /*+ INDEX(A IDX_A_1) */
a.store_cd,
b.itm_cd itm_cd,
CEIL ( (new_date - a.dt) / 7) week_num,
SUM (a.qty * b.demand_weighting * b.CONVERT) qty
FROM a
INNER JOIN
b
ON (a.itm_cd = b.old_itm_cd)
INNER JOIN
(SELECT g.store_grp_cd, g.store_cd
FROM g, h
WHERE g.store_grp_cd = h.fdo_cd AND h.fdo_type = '1') d
ON (a.store_cd = d.store_cd AND b.store_grp_cd = d.store_grp_cd)
CROSS JOIN
dow
WHERE a.dt BETWEEN dow.new_date - 91 AND dow.new_date - 1
AND a.sls_wr_cd = 'W'
AND b.demand_type = 'S'
AND b.old_itm_cd IS NOT NULL
AND NOT EXISTS
(SELECT
NULL
FROM f
WHERE f.store_grp_cd = a.store_cd
AND b.old_itm_cd = f.old_itm_cd)
GROUP BY a.store_cd, b.itm_cd, CEIL ( (dow.new_date - a.dt) / 7)
Gut Erklären, Plan:
OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE ID PARENT_ID SELECT-ANWEISUNG 0 SORTIEREN GRUPPE VON 1 0 NESTED LOOPS-2 1 HASH JOIN ANTI 3 2 TABLE ACCESS BY INDEX ROWID H 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS 7 6 TABLE ACCESS FULL B 8 7 TABLE ACCESS BY INDEX ROWID 9 7 INDEX-RANGE-SCAN IDX_A_1 EINZIGARTIGE 10 9 INDEX UNIQUE SCAN G UNIQUE 11 6 INDEX-RANGE-SCAN H_UK EINZIGARTIGEN 12 5 TABLE ACCESS FULL F 13 3 TABLE ACCESS FULL DOW 14 2
Schlecht Erklären, Plan:
OPERATION OPTIONS OBJECT_NAME OBJECT_TYPE ID PARENT_ID SELECT-ANWEISUNG 0 SORTIEREN GRUPPE VON 1 0 NESTED LOOPS-2 1 NESTED LOOPS 3 2 GESCHACHTELTE SCHLEIFEN 4 3 NESTED LOOPS 5 4 TABLE ACCESS FULL B 6 5 TABLE ACCESS BY INDEX ROWID 7 5 INDEX-RANGE-SCAN IDX_A_1 EINZIGARTIGE 8 7 TABLE ACCESS FULL F 9 8 INDEX UNIQUE SCAN G EINZIGARTIGE 10 4 TABLE ACCESS BY INDEX ROWID H 11 3 INDEX-RANGE-SCAN H_UK EINZIGARTIGEN 12 11 TABLE ACCESS FULL DOW 13 2
In das schlecht erklären, planen die Unterabfrage nicht unnested. Ich war in der Lage zu reproduzieren, die schlecht planen, indem ein no_unnest Hinweis auf die Unterabfrage; allerdings konnte ich nicht reproduzieren guten plan mithilfe der unnest-Hinweis (beim ausführen der Prozedur mit execute immediate). Andere Hinweise werden vom Optimierer berücksichtigt bei der Verwendung der execute immediate-nur nicht die unnest-Hinweis.
Dieses Problem tritt nur auf, wenn ich mit execute immediate Aufruf der Prozedur. Wenn ich execute immediate auf die Abfrage selbst verwendet es den guten plan.
- ich würde denken, die Regel-basierte oprimizer sollte konsistent sein. vielleicht noch ein Hinweis und sehen, ob das auch änderungen der plan - um zu überprüfen, dass die RBO ist eigentlich in Kraft...
- Wir haben einen Hinweis auf die Verwendung eines index in der Abfrage schon so macht, dass die Ursache der CBO verwendet werden, statt für die Abfrage? Auch so, ich versucht, indem die unnest-Hinweis auf die Unterabfrage, aber es scheint nicht zu Ehren den Hinweis.
- Ein index-Hinweis nicht dazu, dass die CBO verwendet werden. Sie können die FIRST_ROWS oder ALL_ROWS Hinweise zu erzwingen CBO-Nutzung, oder die REGEL Hinweis zu zwingen, RBO-Nutzung. Vielleicht sollten Sie post von der eigentlichen SQL-Abfrage und die zwei verschiedenen Pläne, die Sie sehen, für Sie.
- Poste bitte die tatsächlichen Abfragen und die Pläne generiert-und dann können wir vielleicht Anregungen bieten. Eine Sache: wenn man keine stats auf Ihre Tabellen meine Erfahrung ist, dass die CBO wird mit ziemlicher Sicherheit geben, die Sie für ein mieser plan. Welche version von Oracle sind Sie?
- Ich habe die Abfrage und die beiden erklären, Pläne zu meinem post.
Du musst angemeldet sein, um einen Kommentar abzugeben.
Hast du verwendet, der ANSI-join-syntax wird die Kraft von der Verwendung des CBO
(siehe http://jonathanlewis.wordpress.com/2008/03/20/ansi-sql/)
"Wenn du einmal läufst auf der Basis von Kosten mit keine Statistiken, es gibt alle Arten von kleinen Dinge, die möglicherweise genug, um ein unerwartetes Verhalten in der Ausführung plan."
Gibt es ein paar Schritte, die Sie ergreifen können. die erste ist eine 10046 trace.
Idealerweise würde ich starten Sie eine Ablaufverfolgung, die auf einer einzigen Sitzung, die ausgeführt wird, sowohl das "gute" und "schlechte" Abfragen. Die trace-Datei enthalten beide Abfragen mit einem harten parse. Mich würde interessieren, WARUM der zweite hat eine harte geparst, wenn es hat die gleiche SQL-Struktur und die gleichen Analyse user, es gibt nicht viel Grund für die zweite Festplatte analysieren. Der gleichen Sitzung sollte das bedeuten, es gibt keine Merkwürdigkeiten aus verschiedenen memory-Einstellungen etc.
SQL zeigt keinen Einsatz von Variablen, so sollte es keine Datentyp-Probleme. Alle Spalten sind "gebunden", um eine alias-Tabelle, so scheint es keinen Spielraum für verwirrende Variablen mit den Spalten.
Desto extremer Schritt ist ein 10053 trace. Es gibt eine viewer gepostet auf Jonathan Lewis' Website. Das kann Ihnen erlauben, sich in die Grundlagen der Optimierung, um herauszufinden, der Grund für die unterschiedlichen Pläne.
In der weiteren Ansicht, 9i ist so ziemlich tot, und der RBO ist so ziemlich tot. Ich würde ernsthaft die Evaluierung eines Projekts zu bewegen, die app zu CBO. Es gibt features, die die Kraft der CBO verwendet werden und ohne stats diese Art von problem wird halten tauchten.
Es stellt sich heraus, dass dies ein bekannter Fehler in Oracle 9i. Unten ist der text von einem bug-report.
Execute Immediate Gibt Schlechte Abfrage-Plan [ID 398605.1]
Diesem Dokument geliefert wird, an Sie über den Support von Oracle ist die Schnelle Sichtbarkeit (RaV) - Prozess und wurde daher nicht Gegenstand einer unabhängigen technischen überprüfung.
Gilt:
Oracle-Server - Enterprise-Edition - Version: 9.2.0.6
Dieses problem kann auftreten, auf jeder Plattform.
Symptome
Wenn eine Prozedur ausgeführt wird durch execute immediate plan produziert, ist eine andere, als wenn die Prozedur ausgeführt wird, direkt.
Ursache
Die Ursache des Problems wurde identifiziert und verifiziert in einem unveröffentlichten Bug 2906307.
Es wird durch die Tatsache verursacht, dass SQL-Anweisungen herausgegeben von PLSQL in einem rekursiven
Tiefe, die größer als 1 erhalten möglicherweise unterschiedliche Ausführungspläne auf diese, die direkt aus SQL.
Es gibt mehrere optimizer-features von diesem Fehler betroffen (zum Beispiel _unnest_subquery,_pred_move_around=true)
HINWEISE mit Bezug auf die features kann auch ignoriert werden.
Dieser Fehler deckt die gleichen grundlegenden Problem als Bug 2871645 Complex view merging nicht auftreten
rekursive SQL - > Tiefe 1, aber für die anderen Funktionen als Komplex-Ansicht Zusammenführen.
Bug 2906307 geschlossen ist, als ein Duplikat von Bug 3182582 SQL-ANWEISUNG AUSFÜHREN LANGSAMER IN DBMS_JOB ALS IN SQL*PLUS.
Es ist fest in 10.2
Lösung
Für insert-Anweisungen verwenden Hinweis BYPASS_RECURSIVE_CHECK:
INSERT /*+ BYPASS_RECURSIVE_CHECK */IN den Tisch
Referenzen
FEHLER:2871645 - COMPLEX VIEW MERGING tritt NICHT FÜR REKURSIVE SQL - > TIEFE: 1
FEHLER:3182582 - SQL-ANWEISUNG AUSFÜHREN LANGSAMER IN DBMS_JOB ALS IN SQL*PLUS
Es stellt sich heraus, dass dies ein bekannter Fehler in Oracle 9i. Unten ist der text von einem bug-report.
Execute Immediate Gibt Schlechte Abfrage-Plan [ID 398605.1]
Geändert 09-NOV-2006 Typ PROBLEM-Status MODERIERT
Diesem Dokument geliefert wird, an Sie über den Support von Oracle ist die Schnelle Sichtbarkeit (RaV) - Prozess und wurde daher nicht Gegenstand einer unabhängigen technischen überprüfung.
Gilt: Oracle Server - Enterprise Edition - Version: 9.2.0.6 Dieses problem kann auftreten, auf jeder Plattform.
Symptome, Wenn eine Prozedur ausgeführt wird durch execute immediate plan produziert, ist eine andere, als wenn die Prozedur ausgeführt wird, direkt.
Ursache Die Ursache des Problems wurde identifiziert und verifiziert in einem unveröffentlichten Bug 2906307. Es wird durch die Tatsache verursacht, dass SQL-Anweisungen herausgegeben von PLSQL bei einer rekursiven Tiefe, die größer als 1 erhalten möglicherweise unterschiedliche Ausführungspläne auf diese, die direkt aus SQL. Es gibt mehrere optimizer-features von diesem Fehler betroffen (zum Beispiel _unnest_subquery,_pred_move_around=true) HINWEISE mit Bezug auf die features kann auch ignoriert werden.
Dieser Fehler deckt die gleichen grundlegenden Problem als Bug 2871645 Complex view merging tritt nicht für rekursive SQL - > Tiefe 1, aber für die anderen Funktionen als Komplex-Ansicht Zusammenführen.
Bug 2906307 geschlossen ist, als ein Duplikat von Bug 3182582 SQL-ANWEISUNG AUSFÜHREN LANGSAMER IN DBMS_JOB ALS IN SQL*PLUS. Es ist fest in 10.2
Lösung Für insert-Anweisungen verwenden Hinweis BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */IN den Tisch
Verweise FEHLER:2871645 - COMPLEX VIEW MERGING tritt NICHT FÜR REKURSIVE SQL - > TIEFE: 1 FEHLER:3182582 - SQL-ANWEISUNG AUSFÜHREN LANGSAMER IN DBMS_JOB ALS IN SQL*PLUS