Oracle-Tuning: Optimierer-Indexkostenanpassung und Optimierer-Index-Caching

Question

Wie kann der Parameter „Optimizer Index Cost Adj“ für Oracle richtig eingestellt werden? Als Entwickler habe ich enorme Leistungsverbesserungen beobachtet, seit dieser Parameter gesenkt wurde. Allgemeine Abfragen werden von 2 Sekunden auf 200 ms reduziert. Im Internet gibt es viele Warnungen, dass eine Senkung dieses Werts schwerwiegende Probleme mit der Datenbank verursachen wird, aber es werden keine Einzelheiten dazu gegeben, was schiefgehen wird.

Ich sehe derzeit nur einen Vorteil, eine deutlich verbesserte Anwendungsleistung und keine Nachteile. Ich muss die möglichen negativen Auswirkungen der Anpassung dieser Parameter besser verstehen.

Answer 1

Der Grund, warum es nicht empfohlen wird, diese Parameter zu ändern, besteht darin, dass sie Auswirkungen auf die gesamte Datenbank des Optimierers haben. Wenn Sie sie also ändern, um eine bestimmte Abfrage zu optimieren, hat dies wahrscheinlich Auswirkungen auf viele andere Abfragen. Daher ist es gefährlich, sie in der Produktion zu ändern, ohne die gesamte App sorgfältig zu testen.

Jedoch:

1. Es könnte akzeptabel sein, es in einer Entwicklungs-/Testumgebung einzurichten und in der Produktion denselben Wert beizubehalten (früher eine gängige Praxis in OLTP-Systemen). Können Sie jedoch sicher sein, dass Ihre App in einer dedizierten Datenbank ausgeführt wird? Und nicht jemals mit einem Standardparametersatz in eine andere Datenbank konsolidiert wird?
2. Die Parameter helfen, weil Oracle einige Heuristiken über die relativen Kosten von I/O gegenüber der CPU verwendet, und in Ihrem Fall sind die Heuristiken nicht gut genug, sodass Oracle suboptimale Ausführungspläne wählt. Die empfohlene Methode zum Beheben der Heuristiken besteht darin, Oracle sammeln zu lassenSystemstatistikenfür Ihre Datenbankmaschine – wie schnell ist die CPU, wie lange dauert es, bei normaler Systemlast einen einzelnen Block/mehrere Blöcke von Ihrem E/A-System abzurufen usw.Siehe Oracle-Dokumentation.

Wenn Sie sowohl Systemstatistiken als auch die Optimiererparameter verwenden möchten, googeln Sie es, Jonathan Lewis hat darüber geschrieben (leider kann ich auf der Site nicht mehr als einen Link posten).

Ich hoffe das hilft

Answer 2

Der Parameter sollte in einer Produktionsumgebung nicht geändert werden. Der Hauptzweck besteht darin, eine Planänderung zu erzwingen, um die Leistung mit unterschiedlichen Ausführungsplänen zu überprüfen. Im Grunde schlagen Sie dem Optimierer vor, dass die Verwendung aller Indizes in Ihrer Datenbank günstiger ist als die Verwendung anderer Zugriffspfade. Und das kann für einige SQL-Anweisungen zutreffen und für andere falsch sein.

Sobald Sie über einen guten Leistungsplan verfügen, sollten Sie verstehen, warum der Optimierer ihn nicht verwendet, und versuchen, das Problem zu beheben (d. h. es sind keine aktuellen/genauen Statistiken verfügbar -> erfassen Sie aktuelle, genauere Statistiken).

Hoffe das hilft, Stefano

Answer 3

Die Standardeinstellungen für diese beiden Parameter sind für OLTP-Systeme, den gängigsten Datenbanktyp, schrecklich. Sie führen zu mehr vollständigen Tabellenscans und fehlerhaften Abfragen. Im Allgemeinen möchten Sie diese Parameter festlegen, BEVOR Sie live gehen. Sie tun dies in der Testphase.

Wenn Sie diese nach dem Start ändern, besteht das Risiko, dass sich andere Abfragen ändern, die auf falsche Einstellungen abgestimmt waren. Es klingt, als wüssten Sie nicht viel über Datenbankoptimierung, da Sie die Antwortzeit anstelle von Abfrageplänen erwähnen. Sie sollten diese Parameter nicht ändern.

Die meisten DBAs verstehen den konzeptionellen Unterschied zwischen Fix und Design nicht. Nachdem Sie live gegangen sind, beheben Sie die Probleme und müssen dann beim Ändern dieser Parameter vorsichtig sein. Bevor Sie live gehen, befinden Sie sich in der Design- und Entwicklungsphase. Dann passen Sie Parameter wie diese an.

Übrigens sind diese Parameter ein guter Ausgangspunkt (Hinweis: BEVOR SIE IN DIE PRODUKTION GEHEN UND NUR, WENN SIE WISSEN, WAS SIE TUN!)

optimizer_index_cost_adj=10 Optimierer-Caching=90

Dies gilt für OLTPs. Für die Stapelverarbeitung sind die Einstellungen, mit denen Sie beginnen möchten, sehr unterschiedlich. Ich bastle ein wenig daran herum, aber diese Einstellungen liefern mir in 99 % der Fälle die besten Gesamtergebnisse bei einem OLTP. Ich berühre sie jedoch NICHT, nachdem wir in die Produktion gegangen sind. Wenn sie schlecht sind, lasse ich sie schlecht und optimiere die Abfragen.