SQL Plan Directive in 12c

Mir ist gerade aufgefallen, dass ich bisher nichts über die in 12c eingeführten SQL Plan Directives geschrieben habe, die mir erstmals im White Paper Optimizer with Oracle Database 12c von Maria Colgan begegnet sind. In der Dokumentation wird die SQL Plan Directive folgendermaßen definiert:

A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to record a missing extension.

Grundlage für die Anlage einer Direktive sind die Informationen, die über die Automatic Reoptimization Features ermittelt werden. Wenn das Cardinality Feedback (das sich in 12c Statistics Feedback nennt) für eine rowsource Operation eine Abweichung zwischen erwarteter und tatsächlicher Satzanzahl mitteilt, dann wird eine SQL Plan Directive erzeugt, die dem Optimizer mitteilt, bei der nächsten Optimierung der Query zusätzliche dynamic sampling Informationen für Tabellen oder Spaltengruppen zu erfassen - wobei die extended statistics für die column group im Erfolgsfall auch persistent angelegt werden, was die Verwendung der Direktive dann überflüssig machen kann. Die Inhalte der Direktiven sind in den Dictionary Views DBA_SQL_PLAN_DIRECTIVES und DBA_SQL_PLAN_DIR_OBJECTS dargestellt. Direktiven sind nicht an eine SQL_ID gebunden, sondern können für ähnliche Queries herangezogen werden (etwa, wenn die Unterschiede auf der Ebene unterschiedlicher Spalten in der Select-Liste liegen).

Einige interessante Hinweise zu den Änderungen, die sich für die Direktiven in 12.1.0.2 ergeben haben, findet man bei Franck Pachot. Insbesondere erläutert er die Status-Angaben der STATE-Spalte in DBA_SQL_PLAN_DIRECTIVES.

In 12.1.0.1 gab es folgende Status-Angaben:

• NEW: der Status nach der Anlage der Direktive: zu diesem Zeitpunkt ist nur bekannt, dass es eine Abweichung zwischen erwarteten und tatsächlichen Cardinalities gab.
• MISSING_STATS: ergibt sich, wenn nach einer folgenden Optimierung (der gleichen oder einer ähnlichen Query) fehlende Statistiken als Ursache der Abweichung bestimmt wurden.
• HAS_STATS: gibt an, dass die zusätzlichen Statistiken genügen, um die Ausführung zu optimieren.
• PERMANENT: gibt an, dass die zusätzlichen Statistiken die Fehler in den Schätzungen nicht vermeiden - und deshalb ignoriert werden können.

Für 12.1.0.2 gibt es nur noch zwei Status-Angaben:

• USABLE: fasst die bisher unter NEW, MISSING_STATS und PERMANENT aufgeschlüsselten Angaben zusammen. Die Semantik ist etwa: die Direktive muss vom Optimizer untersucht werden, aber das Problem ist noch nicht behoben.
• SUPERSEDED: gibt an, dass die erforderlichen Statistiken erzeugt wurden (HAS_STATS) oder dass die Direktive durch eine andere Direktive überflüssig gemacht wurde.

Demnach ist die Detaillierung der Angaben in 12.1.0.2 geringer, aber die Status-Angaben der älteren Version sind in der (XML-) Spalte NOTES weiterhin verfügbar.

Mein Eindruck ist dabei, dass es sich um ein interessantes Feature mit großem Entwicklungspotential handelt - aber auch um eines, dessen Dokumentation noch eine ganze Menge offener Fragen lässt.