Adaptiver Optimizer in Oracle 12.1 und 12.2

Diese Woche ist eine aktualisierte Version des Whitepapers „Optimizer with Oracle Database 12c“ bei Oracle erschienen. Aus diesem Anlass möchte ich kurz über die wichtigste – und zugleich problematischste – Neuerung im 12c-Optimizer schreiben: Die adaptiven Features.

Fangen wir mit der neuesten Version, Oracle 12.2, an: Offenbar war die Zahl der Probleme, die im Zusammenhang mit einem Teil der adaptiven Features (besonders den SQL Plan Directives) aufgetaucht sind, doch so hoch, dass Oracle sich entschlossen hat, die Steuerung dieser Features auf zwei Parameter zu verteilen und einen davon standardmäßig auf FALSE zu setzen:

Es deckt sich mit meinen Beobachtungen, dass die Adaptive Statistics bzw. die automatisch generierten SQL Plan Directives besonders in OLTP-Systemen zu massiven Verschlechterungen einiger Ausführungspläne führen können. Das war zwar nur bei einem geringen Teil der mir bekannten 12c-Umgebungen der Fall, aber dann konnte das ähnlich heftig durchschlagen wie unter 11g noch das Cardinality Feedback.

Die neuen Parameter in 12.2:

  • OPTIMIZER_ADAPTIVE_PLANS (Default TRUE)
    Erlaubt es dem Optimizer, alternative (Teil-)Pläne zu generieren. Zur Laufzeit kann dann automatisch auf die Alternativen umgeschaltet werden, falls die ursprünglich gewählten Zugriffsmethoden zu lange dauern.
  • OPTIMIZER_ADAPTIVE_STATISTICS (Default FALSE)
    Die bei vorherigen SQL-Ausführungen gewonnenen Erkenntnisse über die tatsächlichen Kardinalitäten und Laufzeiten können persistent abgespeichert und für ähnliches SQL wiederverwendet werden.

Der Parameter in 12.1:

  • OPTIMIZER_ADAPTIVE_FEATURES (Default TRUE)
    Schaltet beide o.g. Features gemeinsam an oder ab.

Bei Problemen mit den neuen Features behelfen sich viele bisher mit dem kompletten Abschalten von OPTIMIZER_ADAPTIVE_FEATURES (s. z.B. Bertrand Drouvot). Christian Antognini hat in seinem Blog etliche „_“-Parameter beschrieben, mit denen sich etwas differenzierter agieren lässt. Allen voran wäre hier „_OPTIMIZER_DSDIR_USAGE_CONTROL“ zu nennen, das die Verwendung von SQL Plan Directives abschaltet, wenn es auf „0“ gesetzt wird. Das entspräche dann in etwa dem neuen Default in Oracle 12.2.

Eine Mögliche Einstellung bei Problemen mit Adaptive Statistics in 12.1 wäre also:

alter system set optimizer_adaptive_features=TRUE;    -- Default
alter system set "_optimizer_dsdir_usage_control"=0;

-- ggf. noch das Statistics Feedback abschalten:
alter system set "_optimizer_use_feedback"=false;

Es gibt aber auch einen Backport von Oracle 12.2 nach 12.1, mit dem man die neuen Parameter verwenden kann: Dies beschreibt Mike Dietrichs Artikel „Enabling ADAPTIVE Features of Oracle 12.2 in 12.1“ sowie MOS Doc ID 2187449.1.

Eine interessante Nebensache noch zum Schluss:

Ein Kunde hatte wegen diverser Performance-Probleme nach Migration auf 12.1 kurzerhand „optimizer_features_enable=11.2.0.3″ eingestellt, um das ältere Verhalten wiederherzustellen (nicht meine bevorzugte Methode!). Interessanterweise wurde trotzdem das neue Statistics Feedback benutzt, obwohl gleichzeitig „OPTIMIZER_ADAPTIVE_FEATURES=FALSE“ gesetzt war. Ein weiterer, kleiner Beweis, dass „optimizer_features_enable“ nicht das exakt gleiche Verhalten bewirkt wie in der gewünschten, älteren Version.

Weblinks

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s