Performance einer Oracle-Query

[Mr. Lolman]

Wer hätte eine Idee, woran das liegen könnte (irgendwelche Caches, Memory Target?). Es wird eine Verkaufsauswertung über unterschiedliche Zeiträume durchgeführt. Ab einem gewisser Zeitspanne wird die Abfrage aber um x Faktoren langsamer:

Monatlich:
01.09.2021 - 30.09.2021 -> 7,4 Sek.
01.10.2021 - 31.10.2021 -> 7,3 Sek.
01.11.2021 - 30.11.2021 -> 7,5 Sek.
01.12.2021 - 31.12.2021 -> 7,5 Sek.


Erweiterung um jeweils 1 Monat
01.09.2021 - 30.09.2021 -> 7,4 Sek.
01.09.2021 - 31.10.2021 -> 7,4 Sek.
01.09.2021 - 30.11.2021 -> 7,5 Sek.
01.09.2021 - 31.12.2021 -> 1186,1 Sek.


Erweiterung um einen Tag:
01.09.2021 - 09.12.2021 -> 8,0 Sek.
01.09.2021 - 10.12.2021 -> 1108,6 Sek.
Verringerung um einen Tag - allerdings diesmal am Anfang und nicht am Ende:
02.09.2021 - 10.12.2021 -> 8,1 Sek.

[#44]

Schau die Ausführungspläne der Queries an und vergleiche die mal.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231

[Shink]

Meine Vermutung:
Oracle sagt: In der Tabelle sind ja nicht so viele Daten, wenn ich eh so viel abfrag, brauch ich den Index nicht zu verwenden - da kann ich ja gleich die ganze Tabelle lesen. -> Full-Table-Scan statt Zugriff über Index.

Meine Vermutung: Statistiken nicht up-to-date. Sieh dir die Ausführungspläne des langsamen und des schnellen Queries an; vermutlich verwendet er bei einem einen Index nicht. Das kann man ihm per Hint "nahelegen". Vielleicht ist es aber auch erledigt, wenn die Statistiken upgedatet sind.

[Mr. Lolman]

Ja, ihr habt recht. Oracle meint, auf den Index verzichten zu können. Allerdings diskutier ich noch mit der Entwicklung, ob wir den Index wirklich mittels Optimizer Hint forcieren wollen. Die sind aber noch nicht ganz überzeugt von meinem Plan ;D

Gibts eigtl. ein Optimizer Hint, mit dem alle Indizes pauschal verwendet werden sollen, oder muss man die zu verwendenten Indizes einzeln angeben?

[#44]

Allerdings diskutier ich noch mit der Entwicklung, ob wir den Index wirklich mittels Optimizer Hint forcieren wollen. Die sind aber noch nicht ganz überzeugt von meinem Plan ;D
Das sollte auch eher die letzte, denn die erste Option sein :tongue:

Ist schon ewig her, dass ich mit Oracle Queries optimiert habe...
Aber im Plan sollte auch ersichtlich sein, was der Planner an Daten erwartet und was dann tatsächlich zurück kommt. Wenn da eine starke Diskrepanz herrscht schau, ob die Statistiken aktuell sind, wie Shink sagte.

[Rooter]

Meine Vermutung:
Oracle sagt: In der Tabelle sind ja nicht so viele Daten, wenn ich eh so viel abfrag, brauch ich den Index nicht zu verwenden - da kann ich ja gleich die ganze Tabelle lesen. -> Full-Table-Scan statt Zugriff über Index.

Meine Vermutung: Statistiken nicht up-to-date. Sieh dir die Ausführungspläne des langsamen und des schnellen Queries an; vermutlich verwendet er bei einem einen Index nicht. Das kann man ihm per Hint "nahelegen". Vielleicht ist es aber auch erledigt, wenn die Statistiken upgedatet sind.THIS!
Auch mein erster Gedanke. Unterschätze nie, wie BESCHISSEN eine SQL-DB laufen kann, wenn keine Statistiken generiert wurden bzw. sie uralt sind!

@TS:
Schau dir mal die Tabellen DBA_TABLES und DBA_INDEXES an. Dort vor allem die Spalten:
Global-Stats
User-Stats
Last-Analyzed

MfG
Rooter

[Exxtreme]

Gibts eigtl. ein Optimizer Hint, mit dem alle Indizes pauschal verwendet werden sollen, oder muss man die zu verwendenten Indizes einzeln angeben?
Optimizer-Hints sind keine gute Idee. Das Allererste was man macht ist die Statistiken aktuell zu halten. :) Das sollte man nach Möglichkeit jeden Tag machen.

[Rooter]

Optimizer-Hints sind eine Notlösung. Das Allererste was man macht ist die Statistiken aktuell zu halten. :) Das sollte man nach Möglichkeit jeden Tag machen.Fify ;)

@TS:
Wenn du die Statistiken nicht täglich aktualisieren kannst, dann wenigstens jeden Sonntag. Oder auch nur 1x im Monat. Ist alles besser als NIE. :ugly:

MfG
Rooter

[Sephiroth]

Stats aktuell halten, dafür gibt es ein internen Job der eigentlich an sein sollte.

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';

....

BEGIN
DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
END;
/


Sich die Stats einer Tabelle anzuzeigen geht mit dem SQL Developer kinderleicht, denn dafür gibt es einen extra Reiter wenn man die Tabelle aufmacht. Dort kann man sie auch on-demand aktualisieren.

p.s.
obiges select ist veraltet. für neuere versionen siehe z.b. https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-statistics-concepts.html#GUID-3ED2DA31-F61B-4E8F-9166-79FB01392B1D und die darauf folgenden kapitel