OPTIMIZER_DYNAMIC_SAMPLING Level 11 und Ergebnisspeicherung

Der Optimizer ist ein komplexes Stück Software und wird im Lauf der Zeit immer komplexer. Mit Oracle 12c und insbesondere mit 12.1.0.2 sind weitere interne Komponenten ins Spiel gekommen, die einen Einfluss auf seine Entscheidungen nehmen. Seit grauer Vorzeit gibt es Stored Outlines, auch schon seit längerer Zeit SQL Profiles, daneben dann SQL Pan Baselines (und die SPM Infrastruktur) und SQL Patches. In 12c wurden einige Komponenten umbenannt und funktional erweitert: aus dynamic sampling wurden dynamic statistics, aus cardinality feedback wurde statistics feedback und neu dazu kamen SQL Plan Directives. Stefan Koehler hat gelegentlich versucht, ein wenig Ordnung in diese Sammlung unterschiedlicher Instrumentarien zu bringen und dabei neben umfassenden Auszügen aus der Dokumentation und entsprechenden Links auch ein ziemlich umfangreiches Beispiel zum Zusammenspiel der Komponenten ergänzt und darin insbesondere die Rolle der SQL Plan Directives beleuchtet (die den Optimizer anweisen, dynamic sampling durchzuführen und unter Umständen extended statistics anzulegen, was die Direktive dann überflüssig machen kann - wie hier ausgeführt). Ich vermute, dass das Zusammenspiel dieser Komponenten sehr viele seltsame Effekte mit sich bringen kann; der ein oder andere Bug würde mich auch nicht überraschen - schon die Erstellung einer Matrix zum Vorrang bzw. zur kombinierten Verwendung der Hilfsmittel dürfte eine komplexe Aufgabe sein...

Aber genug der allgemeinen Vorrede: im Rahmen eines OTN-Threads wurde mir zuletzt klar, dass ich keine klare Vorstellung davon habe, wie sich dynamic sampling (oder dynamic statistics) mit dem in 12.1.0.2 eingeführten neuen Level 11 bezüglich ihrer Persistierung verhalten. Dass es eine Persistierung gibt behauptet dabei die Dokumentation in ihrer Beschreibung der Parameterangaben: "Level 11: use dynamic statistics automatically when the optimizer deems it necessary. The resulting statistics are persistent in the statistics repository, making them available to other queries." Da ich keine Informationen dazu finden konnte, was ich mir unter diesem "statistics repository" vorstellen sollte, habe ich dazu einen kleinen Test durchgeführt:

-- 12.1.0.2
drop table t;

create table t
as
select rownum id
     , case when rownum <= 100 then 1 else 0 end col1
  , lpad('*', 50, '*') padding
  from dual  
connect by level <= 100000;  

create index t_idx on t(col1);

alter session set OPTIMIZER_DYNAMIC_SAMPLING = 11;

exec dbms_monitor.session_trace_enable();

-- query execution
select count(*) from t where col1 = 1;

alter system flush shared_pool;

-- repeated query execution
select count(*) from t where col1 = 1;

exec dbms_monitor.session_trace_disable()

Im erzeugten Trace-File erscheinen dabei vor beiden Ausführungen dynamic sampling Queries, die am Kommentar /* DS_SVC */ zu erkennen sind. Zum Teil können die Ergebnisse aus dem result cache genommen werden, in anderen Fällen sind "echte" Leseoperationen erforderlich - aber in jedem Fall müssen die Daten erneut ermittelt werden und im Trace finden sich keine Hinweise auf eine Persistierung der Daten oder ein erneutes Lesen persistierter Daten. An dieser Stelle habe ich dann eine entsprechende Anfrage an Oracle-L gestellt und mehrere hilfreiche Antworten bekommen. Den aus meiner Sicht entscheidenden Punkt hat dann wiederum Stefan Koehler beigesteuert: zwar behauptet die Dokumentation, dass die Daten persistiert werden, aber in Maria Colgans Whitepaper zum Verhalten des Optimizers in 12c liest man: "In order to minimize the performance impact, the results of the dynamic sampling queries will be persisted in the cache, as dynamic statistics, allowing other SQL statements to share these statistics." Persistierung im Cache ist nicht unbedingt das, was ich unter Persistierung verstehe, aber zumindest erklärt der Satz das Verhalten. Stefan Koehler wies weiterhin darauf hin, dass die Statistikerfassung unter Umständen durch eine Plan Direktive festgeschrieben werden kann, aber das macht den gesamten Vorgang aus meiner Sicht nicht weniger undurchsichtig: offenbar kann es in 12c sehr schwierig werden, die Kalkulationen des Optimizers nachzuvollziehen, weil man keinen direkten Zugriff auf seine Entscheidungsgrundlage hat. Wichtige statistische Informationen werden ad-hoc beschafft, temporär vorgehalten und unter Umständen später wieder neu beschafft - was dann unter Umständen Änderungen der (gesampelten) Basisdaten mit sich bringen kann. Da die Statistikerfassung von der Ausführung komplett getrennt sein kann, ist es über das Dictionary nicht möglich, die notwendigen Informationen zusammenzustellen - und selbst ein SQL-Trace kann die Angaben verpassen (wenn sie bereits zu einem früheren Zeitpunkt in den Cache gebracht wurden). Dass dynamic sampling ein großartiges Hilfsmittel sein kann, steht dabei für mich außer Frage - aber eine weniger intransparente Behandlung der Ergebnisse würde mir besser gefallen.