Schema Synonyme

Es gibt nicht viele Dinge, die in anderen RDBMS existieren und die mir bei Oracle ernsthaft fehlen - aber wenn ich mir ein solches fehlendes Feature wünschen dürfte, dann wäre die Möglichkeit, ein Schema umzubenennen, ein guter Kandidat. Möglicherweise wird mir dieser Wunsch in absehbarer Zeit erfüllt, denn Franck Pachot hat einen Underscore-Parameter entdeckt, mit dessen Hilfe sich die Erzeugung eines Befehls CREATE SCHEMA SYNONYM aktivieren lässt. Nun ja: eine Möglichkeit der Umbenennung wäre mir lieber gewesen als ein Synonym, aber ich will die zuständige gute Fee nicht überfordern. Und natürlich sollte man das neue Feature noch nicht benutzen, da es zum einen noch nicht offiziell freigegeben ist und zum anderen noch nicht richtig funktioniert.

SDSQL

In der Vergangenheit habe ich schon häufiger erwähnt, dass ich den SQL Developer für ein sehr brauchbares Werkzeug halte, das mit erstaunlichem Tempo weiterentwickelt wird - insbesondere, wenn man bedenkt, dass es ganz und gar kostenlos ist. Fast noch interessanter finde ich aber das vor wenigen Tagen vorgestellte zugehörige Tool SDSQL, das die sqlplus engine des SQL Developers als command line interface verfügbar macht - und zahlreiche Features hinzufügt, die im klassischen sqlplus fehlen. Die beste Informationsquelle zum Thema ist zur Zeit der Blog von Kris Rice (der auch auf Twitter sehr aktiv ist), in dem sich unter anderem der einführende Artikel What is SDSQL? findet, in dem nicht nur die titelgebende Frage beantwortet wird, sondern auch download-Quelle und Installations-Verfahren erwähnt werden - wobei die Installation unter Umständen nicht ganz reibungslos abläuft, wie man auch in einigen OTN-Threads erfährt. Auf meinem Rechner hatte ich Probleme mit der TNS-Namensauflösung und musste host naming verwenden. Außerdem ist es derzeit noch recht einfach, die Beta-Version 4.1 zum Absturz zu bringen. Aber einige der in weiteren Artikeln vorgestellten Features haben großen Potential, so etwa:

• die History-Funktion, die über den Buchstaben h aufgerufen wird und eine Liste der letzten (bis zu 100) Queries liefert. Mit "history full" kann man die Historie in der ursprünglichen Formatierung abrufen und "history usage" informiert über die Häufigkeit der Verwendung von Befehlen.
• das ddl-Kommando, das einen Shortcut auf die Objekt-Informationen aus dbms_metadata darstellt. Im Artikel wird auch gezeigt, wie sich die dbms_metadata-Ausgabe mit Hilfe von set_transform_param-Aufrufen an die eigenen Vorstellungen anpassen lässt (um etwa die Storage-Informationen zu unterdrücken).
• die Alias-Funktion, mit der man ein Makro erstellen kann - also etwa den Alias "Sessions" für ein Select auf v$session. Außerdem zeigt der Artikel eine erschütternd simple Create Table as Select (=CTAS)-Syntax zum Kopieren von Tabellen, nämlich: ctas emp emp_new.
• die Definition von precommand und postcommand Kommandos zur Ausführung rahmender SQL-Aufrufe - etwa zur Erfassung von Ausführungsstatistiken für eine Query (aus v$mystat).

Die Liste wird sicherlich noch wachsen. Und wenn die Stabilitätsprobleme behoben sind - bzw. wenn ich gelernt habe, welche Operationen ich mir sparen sollte -, wird das sicher ein ausgesprochen wertvolles Werkzeug; vielleicht wird es bei mir gar die Stelle von sqlplus einnehmen, auch wenn der Gedanke mich beinahe erschreckt...

Ob ich die weiteren Artikel von Kris Rice regelmäßig ergänzen werde, weiß ich noch nicht - der Mann ist erschütternd produktiv.

Gerade sehe ich, dass Jeff Smith gestern auch noch einen Artikel zur Installation veröffentlicht hat, in dem erklärt wird, wie man sich auch mit TNS erfolgreich verbinden kann.

Blockgröße und Multi-Row-DML

Nikolay Savvinov hat vor einiger Zeit einige Artikel veröffentlicht, die sich damit beschäftigen, dass DML-Operationen für Blocks einer Größe von 4K eine deutlich schlechtere Performance liefern als entsprechende Operationen für Blocks der Standardgröße (8K). Hier eine Liste der Artikel:

• Block size and multirow DML peformance
• Followup on the 4k DML bug
• “4k bug” is not a bug

Ich spare mir eine detaillierte Zusammenfassung der Artikel und springe gleich zur Pointe, die der Titel des letzten Artikels bereits andeutet - das Verhalten folgt einer inneren Logik, die aber offenbar nicht dokumentiert ist: wenn die potentielle Satzlänge (also die Addition der definierten Spaltenlängen) die Blockgröße überschreitet, dann schaltet Oracle automatisch in einen row-by-row Modus um und verzichtet auf die Bulk-Operation, was dann (unter anderem) zu größerer Redo-Generierung und schlechterer Performance führt. Ich konnte das Verhalten mit einem angepassten Test auch für 8K-Blöcke nachvollziehen - es scheint sich also um ein allgemeines Phänomen zu handeln. Dieses Verhalten kann als Argument gegen die Verwendung unnötig breiter Spalten "auf Verdacht" für den Fall unerwartet großer Werte betrachtet werden.

Nachtrag 23.12.2014: eine verwandte Frage wurde kurz darauf auch in einem Oracle-L-Thread aufgeworfen und ich habe dort auf den letzten Artikel der Serie verwiesen - was Jonathan Lewis dann auch in seinem Blog getan hat.

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.

Analyse von Parsing-Effekten mit ASH

In seinem jüngsten Artikel stellt Jonathan Lewis einen ziemlich extremen Fall von Parsing in einem System mit zahlreichen sehr ähnlichen - und recht komplexen - Queries vor. Eine Query, die im Testsystem in 7 Sekunden optimiert werden kann, benötigt im Produktivsystem 415 Sekunden für das Parsen. Dafür verantwortlich ist offenbar die Tatsache, dass der Server unter extremer Last steht und mehr CPU verwenden will als verfügbar, und dass zahlreiche sehr ähnliche Queries gleichzeitig optimiert werden sollen, was eine massive Auseinandersetzung um die knappen Ressourcen (CPU, Latches, Mutexes) hervorruft. Die Analyse hat dabei einen vorläufigen Charakter, weil die vermutete (und plausible) Lösung der Verwendung von Bindewerten für die ähnlichen Queries zur Vermeidung der konkurrierenden Parse-Operationen noch nicht umgesetzt ist - sie würde während der inhaltlich erforderlichen 7 Sekunden zum Parsen der komplexen Query in anderen Sessions Waits des Typs "cursor: pin S wait on X" hervorrufen, die sich bekanntlich ergeben, wenn eine Session einen Plan benötigt, der gerade von einer anderen Session erzeugt wird.

Im Rahmen der Analyse erscheinen noch die folgenden erinnerungswürdigen Hinweise:

• eine Analyse-Query auf Basis von v$active_session_history (sprich ASH), die die I/O und CPU Events zu einer sql_id (und sql_exec_id) aufführt und zusätzlich die Laufzeit des Parsens anzeigt (Attribute: in_parse, in_hard_parse)
• die in ASH aufgeführten CPU-Angaben entsprechen nicht exakt den Angaben in v$sql, was der Autor auf die hohe Last im System zurückführt: "when we compare v$sql with ASH the difference in CPU is (statistically speaking) time spent in the run queue. So of our 447 seconds of CPU recorded by ASH, we spent 161 seconds in the CPU run queue waiting for CPU." In ASH ist demnach in entsprechenden Fällen mit größeren CPU-Laufzeitangaben zu rechnen.

Das Ergebnis ist - wie gesagt - anscheinend noch ein vorläufiges. Ich gehe aber davon aus, dass Jonathan Lewis - wie üblich - die abschließende Pointe noch nachliefern wird.

PowerQuery mit SSAS

Viel habe ich nicht mehr zu SSAS zu sagen, aber der Hinweis von Chris Webb, dass PowerQuery in der aktuellen Version jetzt auch SSAS Multidimensional und Tabular Quellen als Datenbasis verwenden kann, sollte nicht unerwähnt bleiben. Dabei wird intern MDX erzeugt - "while it’s a bit strange in places it’s basically good and should perform well."

Keine adaptiven Pläne für Queries mit LOB-Elementen

Zu den interessanten neuen Errungenschaften des Optimizers in 12c gehören die adaptive plans, die es möglich machen, die endgültige Entscheidung darüber, ob ein Join als Nested Loops oder als Hash Join erfolgen kann bis zum Abschluss der ersten Ausführung aufzuschieben und dann in Abhängigkeit von der ermittelten Cardinality zu treffen (Stichwort: inflection point). Allerdings gibt es eine Reihe von Rahmenbedingungen, die erfüllt sein müssen, damit ein adaptive plan verwendet werden kann. Eine Einschränkung, die mir bisher nicht bekannt war, nennt Christian Antognini in seinem Blog: das Vorliegen von LOBs in einer Query führt zur Deaktivierung des Features und im CBO-Trace erscheint ein Hinweis "AP: Adaptive joins bypassed for table ... due to LOB-producing table under statistics collector". Dazu liefert der Blog-Artikel ein entsprechendes Beispiel.

Join Cardinality und Sanity Checks

In seinem heutigen Blog-Artikel Upgrades - bisweilen denke ich, der Autor könnte sich wiedererkennbarere Titel ausdenken - schreibt Jonathan Lewis über die Veränderungen der Join-Cardinality für unterschiedliche Oracle-Realases seit Version 9.2. In seinem Beispiel verbindet er zwei identische Tabellen, die jeweils 1.000.000 Datensätze enthalten, über unterschiedlich häufig auftretende Spaltenwerte und zeigt dabei, dass es mindestens drei unterschiedliche Ergebnisse für die Cardinality-Schätzungen gibt. Ich wiederhole hier nicht den Versuchsaufbau des Artikels, sondern ergänze nur meine Erklärungen/Interpretationen, denn die fehlen beim Herrn Lewis derzeit noch - obwohl er versprochen hat, sie in den nächsten Wochen nachzuliefern:

• in 9.2.0.8 ist anscheinend die alte Standardformel für die Berechnung der Join-Cardinality am Werk, also:
  

     -- für einen Join der Tabellen t1 und t2 über die Spalte c1 bzw. c2 gilt:
     Join Selectivity
        = ((num_rows(t1) - num_nulls(t1.c1))/num_rows(t1)) *
          ((num_rows(t2) - num_nulls(t2.c2))/num_rows(t2)) /
          greater (num_distinct(t1.c1),  num_distinct(t2.c2))

     Join Cardinality
         = Join Selectivity * filtered cardinality (t1) * filtered cardinality (t2)
     
     -- im Beispiel bei Jonathan Lewis:
     1/90 * 1/750 * 2500 * 2500 = 92,59
     -- das ist nicht der Wert 96, der im Plan des Scratchpad-Beispiels erscheint,
     -- aber eine ordentliche Annäherung

• in 10.2.0.4 wird die Formel offenbar insofern korrigiert, dass die beiden Selektivitäten der Join-Spalten von einer Seite des Joins genommen werden, um ein konsistentes Ergebnis zu erhalten. Für das Beispiel des Artikels ergibt sich:

     1/72 * 1/750 * 2500 * 2500 = 115,74
     -- das ist exakt das Ergebnis im Ausführungsplan des Artikels
     -- und relativ nah am Ergebnis aus 9.2.0.8

• in 11.2.0.4 und späteren Releases ergibt sich dann ein ganz anderes Resultat, nämlich 2554. Ein (in 12.1.0.2) über dbms_sqldiag.dump_trace (p_component => 'Compiler') erzeugtes CBO-Trace enthält folgende Angaben:

     ColGroup cardinality sanity check: ndv for  T1[T1] = 54000.000000  T2[T2] = 54000.000000
     Join selectivity using 1 ColGroups: 4.0866e-04 (sel1 = 0.000000, sel2 = 0.000000)
     Join Card:  2554.147936 = outer (2500.000000) * inner (2500.000000) * sel (4.0866e-04)
     Join Card - Rounded: 2554 Computed: 2554.147936

Vor einigen Jahren hat Randolf Geist zwei Artikel zum Thema "Multi-Column Joins" geschrieben und dabei erklärt:

It can be seen from a 10053 optimizer trace file that Oracle uses a "Multi-column cardinality sanity check" by default in cases where the calculated multi-column selectivity falls below a certain limit, obviously using the smaller selectivity available from the different 1/num_rows of the tables/row sources involved in the join, arriving at an estimate 30,000 rows in this particular case.

In den einfacheren Beispielen bei Randolf Geist entspricht die korrigierte Join-Cardinality dabei, so weit ich sehe, immer der Cardinality der kleineren Input-Menge (wobei Randolfs Artikel noch diverse komplexere Fälle untersuchen). Wie aber erklärt sich die 2554 in Jonathan Lewis' Fall, die von den 2500 Datensätzen beider Input-Mengen abweichen? Dazu ein - nicht übermäßig systematischer - Tests. Ausgangspunkt ist dabei die Query aus dem Scratchpad-Artikel, bei der ich unterschiedliche Join-Spalten einsetze:

select t1.*, t2.*
  from t1, t2
 where t1.n_400 = 0
   and t2.n_72 = t1.n_90
   and t2.n_750 = t1.n_600
   and t2.n_400 = 1;

Wenn ich an Stelle der 72, 90, 600, 750 andere Werte einsetze, ergeben sich folgende Muster:

• die Join-Cardinality sinkt bei veränderten Werten nicht unter 2500.
• sie werden offenbar wie schon in 10.2 nur von einer Seite des Joins bestimmt: statt 90 und 600 kann auf t1-Seite auch jede andere (weniger selektive) Kombination erscheinen, ohne dass sich die 2554 für die Kombination 750 + 72 ändern.
• ich vermute, dass die Selektivität der relevanten Seite des Joins die Differenz zu 2500 erklärt, denn für folgende Kombinationen erhalte ich folgende Werte:
• 1000 + 1000 => card: 2500
• 1000 + 750 => card: 2501
• 1000 + 600 => card: 2502
• 1000 + 90 => card: 2533
• 1000 + 72 => card: 2540
• 1000 + 40 => card: 2575
• 1000 + 3 => card: 3681
• 750 + 750 => card: 2502
• 750 + 600 => card: 2503
• 600 + 600 => card: 2505
• etc.
• Der Zusammenhang zwischen der im CBO-Trace erscheinenden NDV-Angabe (im Beispiel 54000) und der zusätzlichen cardinality oberhalb von 2500 (im Beispiel 54) ist rechnerisch relativ gut zu fassen, aber ich habe keine Ahnung, was er bedeutet:
• NDV: 54000 => card: 2554.147936 <=> (1/54000) * 2923988
• NDV: 66168 => card: 2543.752544 <=> (1/66168) * 2895018
• NDV: 72000 => card: 2539.618041 <=> (1/72000) * 2852498
• NDV: 82710 => card: 2534.468775 <=> (1/82710) * 2850912
• NDV: 90000 => card: 2531.389226 <=> (1/90000) * 2825030

Woher der Multiplikator von ca. 2.900.000 kommen könnte, weiß ich nicht - aber ich vermute, dass folgende Artikel bei Jonathan Lewis zeigen werden, was an den hier aufgestellten Behauptungen und Vermutungen dran ist.

Nachtrag 04.12.2014: Stefan Koehler hat in einem Kommentar zum Scratchpad-Artikel vermutlich die Antwort auf meine Frage geliefert. Die Basis dafür liefert ein Artikel von Alberto Dell'Era auf den mich Jonathan Lewis verwiesen hatte: dort wird eine (in verlinkten Artikeln genauer beschriebene) Funktion SWRU (select without replacement uniform) verwendet, mit deren Hilfe man die Wahrscheinlichkeit des Auftretens einer bestimmten Anzahl unterschiedlicher Werte beim Zugriff auf eine Menge mit einer bestimmten Anzahl von Elementen (unter der Annahme der Gleichverteilung) bestimmen kann. Stefan Koehler verwendet in seiner Rechnung folgende vereinfachte Version der Formel:

(input_num_of_distinct_values) * (1 - power(1 - sample_size/total_size, total_size/input_num_of_distinct_values))

-- im Beispiel:
54000 * (1 - power(1 - 2500/1000000, 1000000/54000)) = 2446,00097
-- die cardinality errechnet sich dann als:
(2500 * 2500)/2446,00097 = 2555,19114

Das Ergebnis entspricht in diesem und den anderen Testfällen ziemlich akkurat den Erwartungen. An dieser Stelle wird mir mal wieder klar, dass eine solide mathematische Grundlage manchmal ganz hilfreich wäre.