[ORACLE] Statistik & Executionplan

[moo_kuh]

Hallo,

ich habe mal eine Frage bezüglich der Gültigkeit von Statistiken und den Ausführungsplänen.

Nehmen wir an ich habe eine Tabelle TAB_CENT01 in der vor ca. 5 Wochen 5 Millionen Datensätze waren. Diese Tabelle hat auch 2 Indizes über verschiedene Felder. Vor ca. 5 Wochen habe ich das letzte mal eine Statistik für die Tabelle + deren Indizes erzeugt.

Die Datenbank läuft seit ca. 4 Wochen ohne Neustart.

Es gibt nun "Standard-Queries" über die die User auf die Tabelle TAB_CENT01 abfragen machen. D.h. das geparste SQL-Statement liegt im Library Cache von Oracle.

Nun fällt mir auf das die Tabelle TAB_CENT01 einen enormen Zuwachs auf ca. 11 Millionen Datensätze hat. D.h. die alte Statistik ist nicht mehr zu gebrauchen. Ich erzeuge eine neue Statistik im laufenden Betrieb um eine aktuelle zu bekommen und evtl. einen besseren Zugriffspfad zu erreichen.

Nun meine Fragen:

1) Wie lange bleiben die geparsten SQL Statements im Library Cache? Gibt es hierfür eine "Verfallsdauer" oder werden diese nach folgendem Prinzip gehandelt: Erst wenn nicht mehr genügend Memory für den Library Cache zur Verfügung steht, fällt das älteste geparste Statement heraus?

2) Wenn das Statement schon geparsed im Library Cache liegt und ich neue Statistiken erzeuge, wird dieses Statement dann neu "generiert" oder spielt das in dem Fall überhaupt keine Rolle?

3) Ab wann wird die neue Statistik gültig und somit der evtl. neue Execution Plan? Wird ein Execution Plan bei jedem ausführen eines SQL Statements bestimmt oder wird dieser mit im geparsten SQL-Statement "abgelegt"? Falls ja - ab wann zieht dann der evtl. neue Execution Plan?

Vielen Dank schonmal für Euere Antworten.

Viele Grüße

moo_kuh

[Jasper]

1) Wie lange bleiben die geparsten SQL Statements im Library Cache? Gibt es hierfür eine "Verfallsdauer" oder werden diese nach folgendem Prinzip gehandelt: Erst wenn nicht mehr genügend Memory für den Library Cache zur Verfügung steht, fällt das älteste geparste Statement heraus?

fast richtig. die ältesten und am wenigsten genutzten objekte werden gelöscht.

2) Wenn das Statement schon geparsed im Library Cache liegt und ich neue Statistiken erzeuge, wird dieses Statement dann neu "generiert" oder spielt das in dem Fall überhaupt keine Rolle?

nein, das statement wird nicht neu hard-geparst. soft-parse passiert bei jedem zugriff und dabei verwendet der CBO die neuen statistiken.

3) Ab wann wird die neue Statistik gültig und somit der evtl. neue Execution Plan? Wird ein Execution Plan bei jedem ausführen eines SQL Statements bestimmt oder wird dieser mit im geparsten SQL-Statement "abgelegt"? Falls ja - ab wann zieht dann der evtl. neue Execution Plan?

siehe 2. statistiken und geparste statements werden getrennt abgelegt.

-j

[moo_kuh]

nein, das statement wird nicht neu hard-geparst. soft-parse passiert bei jedem zugriff und dabei verwendet der CBO die neuen statistiken.

Hi Jasper,

vielen Dank für deine Antwort.

Noch eine Ergänzungsfrage:

D.h. der Execution Plan wird bei jedem absetzen eines SQL Statements (egal ob es schon geparsed wurde oder noch nicht) neu bestimmt?

Gruß

moo_kuh

[Jasper]

:

D.h. der Execution Plan wird bei jedem absetzen eines SQL Statements (egal ob es schon geparsed wurde oder noch nicht) neu bestimmt?

wenn du mit geparsed == hard-parsed meinst, ja. der executionplan wird in der soft-parse phase entwickelt.

lässt sich leicht testen. erstelle einfach eine tabelle mit einem index auf einem feld hoher selektivität. selects mit einer equal-klausel auf das feld werden den index verwenden. sobald das feld auf einen festen wert gesetzt wurde und die statistiken aktualisiert wurden, wird der CBO einen FTS verwenden. wichtig ist, dass das statement in beiden fällen absolut das gleiche ist, also nicht nochmal komplett geparst wird.

-j

[moo_kuh]

wenn du mit geparsed == hard-parsed meinst, ja. der executionplan wird in der soft-parse phase entwickelt.

Hallo Jasper,

ich meine folgendes:

Das SQL Statement wurde noch nie ausgeführt (= hard parse) - dabei wird ein Execution Plan erstellt. Danach erzeuge ich neue Statistiken auf die Tabelle, dadurch würde sich eventuell der Execution Plan ändern.

Nun verwende ich nochmals das selbe SQL Statement (= soft parse), wird nun ein neuer Execution Plan gewählt (bzw. erzeugt) oder merkt Oracle sich den "alten" Execution Plan von dem vorher "hard-geparsten" SQL Statement?

Hintergrund ist dieser:

Die Anwendung die auf die Datenbank zugreift arbeitet fast nur mit Bind-Variablen, d.h. die Statements werden fast immer "soft-geparsed", wennn diese einmal ausgeführt wurden. Mich interessiert nun ob ein evtl. neuer Execution Plan gewählt wird, obwohl das Statement noch im Library Cache liegt...

Ich hoffe es war verständlich

Danke!

Gruß

moo_kuh

[moo_kuh]

Hallo,

laut diesem Link:

The Oracle Users' Co-operative FAQ - Soft vs. Hard pards

würde der alte Execution-Plan ausgeführt werden.... das ist aber in manchen Fällen nicht wirklich toll... wenn durch die Erstellung von neuen Statistiken ein besserer Ausführungsplan entstehen würde, Oracle aber weiterhin den alten aus dem Cache verwendet....

Habe ich das dann richtig verstanden?

Gruß

moo_kuh

[Jasper]

ok, ich hab nochmal meine 9i befragt und muss mich korregieren. irgendwie liegt meine OCP-prüfung zu weit zurück insofern danke für die frage, recht interessant.

ok, zu dem test, siehe test.sql.txt.

die stats und die ausführungspläne werden getrennt abgelegt, allerdings wird der ausführungsplan zusammen mit dem statement abgelegt. wird ein objekt analysiert, werden alle execution plans, die dieses objekt referenzieren, für ungültig erklärt. damit wird bei der nächsten ausführung des gespeicherten statements ein neuer execution plan erstellt.

der testoutput sieht dann so aus:

first

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

1 0 1 VALID 3844206385

changes

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

1 1 0 0

second

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

2 1 1 VALID 3918351354

stats-aktualisieren (changes) setzt parse-calls auf 0, invalidations auf 1 und löscht den ausführungsplan.

beim nächsten zugriff (second) wird das statement reloaded/reparst und ein komplett neuer, anderer ausführungsplan erstellt.

das ist IMHO aber kein kompletter hard-parse, würde keinen sinn machen, da das statement an sich unverändert ist und damit alle semantik/syntax-checks überflüssig sind.

das statement an sich scheint im library cache zu verbleiben, nur der execution plan wird komplett neu erstellt.

unter 10.2.0.3 sieht das ganze anders aus:

first

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

1 0 1 VALID 3844206385

changes

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

1 0 1 VALID 3844206385

second

LOADS INVALIDATIONS PARSE_CALLS OBJECT_STATUS PLAN_HASH_VALUE

---------- ------------- ----------- ------------------- ---------------

1 0 2 VALID 3844206385

auch wenn es nicht so aussieht, der plan von first (index range scan) ist ein anderer als der von second (table access full). keine invalidations, keine loads, lediglich ein neuer parse.

-j

test.sql.txt

[moo_kuh]

Hi Jasper,

erstmal vielen Dank für deine Mühe...

stats-aktualisieren (changes) setzt parse-calls auf 0, invalidations auf 1 und löscht den ausführungsplan.

beim nächsten zugriff (second) wird das statement reloaded/reparst und ein komplett neuer, anderer ausführungsplan erstellt.

das ist IMHO aber kein kompletter hard-parse, würde keinen sinn machen, da das statement an sich unverändert ist und damit alle semantik/syntax-checks überflüssig sind.

das statement an sich scheint im library cache zu verbleiben, nur der execution plan wird komplett neu erstellt.

Das wäre logisch und nachvollziehbar

Also wird der "alte" Execution-Plan ungültig sobald ein analyze oder dbms gather stats ausgeführt wird. Es wird somit nur ein neuer Executionplan berechnet/erstellt und dann wieder abgelegt ... das Statement verbleibt im Cache...

unter 10.2.0.3 sieht das ganze anders aus:

auch wenn es nicht so aussieht, der plan von first (index range scan) ist ein anderer als der von second (table access full). keine invalidations, keine loads, lediglich ein neuer parse.

Dies musste ich auch unter 10.2.0.2 feststellen, interessant ist es wie CBO von Oracle das dann hinbekommt

Die Kernaussage ist aber ... das Statement bleibt geparsed ... es wird "nur" ein neuer Execution Plan dazu abgelegt, wenn ein zugehöriges Objekt analysiert wird.

Vielen Dank jetzt bin ich wieder um einiges schlauer...

Gruß

moo_kuh

[Jasper]

erstmal vielen Dank für deine Mühe...

hat mich doch selbst interessiert.

Dies musste ich auch unter 10.2.0.2 feststellen, interessant ist es wie CBO von Oracle das dann hinbekommt

das war für mich ehrlich gesagt auch neu. ich frag mal bei den CBO experten nach.

Die Kernaussage ist aber ... das Statement bleibt geparsed ... es wird "nur" ein neuer Execution Plan dazu abgelegt, wenn ein zugehöriges Objekt analysiert wird.

die frage ist, wird intern trotzdem komplett neu geparst oder ist das wie vermutet eher ein light-parse? ich frag mal bei Tom Kyte nach

jetzt bin ich wieder um einiges schlauer...

ich auch.

-j