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.

Privilege Analysis mit 12c

Eine kleine persönliche Beichte, die regelmäßige Leser vielleicht nicht allzu sehr überrascht: Performance-Fragen interessieren mich deutlich mehr als solche der Security. Und noch ein Geständnis: ich habe gelegentlich Applikationen ausgeliefert, deren Berechtigungen größer waren als erforderlich - weil es mir zu mühsam und zu öde war, mich bis ins Detail mit der Privilegienvergabe zu beschäftigen. Für Entwickler, die unter ähnlichen Charakterschwächen leiden, gibt es in 12c jetzt ein großartiges neues Feature: die Privilege Analysis, bei der man einem User initial DBA-Rechte gibt, dann die Applikation laufen lässt und anschließend feststellen kann, welche Rechte tatsächlich benötigt werden. Ein kompaktes Beispiel für die Verwendung findet man bei Franck Pachot,

Instanz-Crash bei Latch-Analyse

Stefan Koehler analysiert bei SAP on Oracle eine Situation, in der das manuelle Setzen und Freigeben von Latches über Oradebug zu einem Instanz-Crash führt. Relevanter als das Ergebnis ist für mich die methodische Vorgehensweise bei der Analyse - sollte ich mich mal wieder mit Latches beschäftigen wollen, dann wäre es nützlich, diesen Artikel noch mal intensiver anzuschauen.

Zone Maps in 12c

Nur eine knappe Sammlung von Links zum Thema: Zone Maps sind eine neue Errungenschaft in 12c und stellen eine Index-artige Struktur dar, die es erlaubt, eine Filterung von Blöcken anhand der Definition von Wertebereichen durchzuführen. Dazu sind mir zuletzt mehrere Artikel von Richard Foote und ein Beitrag im Data Warehouse Insider Blog begegnet:

• 12.1.0.2 Introduction to Zone Maps Part I (Map Of The Problematique): Richard Footes grundsätzliche Beschreibung des Features mit dem einleitenden Hinweis, dass die ZoneMaps eine Lizensierung der Partition Option und von Exadata bzw. SuperCluster erfordert - und unter diesen Umständen vermutlich erst einmal keine allzu breite Nutzung erfahren wird. Anhand eines Beispiels wird das grundsätzliche Verhalten erläutert: ZoneMaps speichern die Minimal- und Maximalwerte einer Spalte (oder Spaltenkombination) innerhalb einer Zone - also eines Bereichs von Blocks (im Umfang von 8 mb). Ausgehend von dieser Information kann der Optimizer ein Pruning irrelevanter Zonen durchführen. Dabei stellen die ZoneMaps selbst ein sehr kleines Segment dar - was angesichts der Definition nicht überrascht.
• 12.1.0.2 Introduction to Zone Maps Part II (Changes): erklärt das Verhalten der ZoneMaps bei DML-Operationen auf der zugehörigen Tabelle. Der große Unterschied zu Indizes ist dabei, dass ZoneMaps nicht automatisch aktualisiert werden, sofern nicht ein REFRESH ON COMMIT definiert ist - in dieser Hinsicht entspricht ihr Verhalten also dem von Materialized Views. Nach DML-Operationen werden betroffene ZoneMaps als stale markiert, aber nicht betroffene Elemente können weiterhin gefiltert werden. Besonders effektiv sind ZoneMaps erwartungsgemäß bei stark geclusterten Daten.
• 12.1.0.2 Introduction to Zone Maps Part III (Little By Little): untersucht die Auswirkungen der Daten-Clusterung genauer und anhand von Beispielen - aber am Ergebnis ändert sich nichts.
• Optimizing Table Scans with Zone Maps: ein Überblick zum Thema vom Oracle Product Management Team, der ähnliche Aussagen enthält wie die Artikel des Herrn Foote (mit weniger Tests, dafür aber mehr Visualisierung).

Insgesamt scheint das Feature durchaus interessant und ähnlichen Konzepten verwandt zu sein, die mir anderswo begegnet sind - etwa den Data-Ids im SSAS-Kontext oder den neuen BRIN Indizes (Block Range Index) in postgres 9.5. Aber die Lizenzierungsvorraussetzungen werden es wohl zumindest vorläufig zu einer exotischen Randerscheinung machen.

Baselines und Parsing

Jonathan Lewis weist darauf hin, dass Baselines keine große Hilfe sind, wenn es darum geht, den Aufwand beim Parsen komplexer Join-Operationen zu verringern. In seinem Beispiel führt ein Join von acht Tabellen zur Prüfung von 5040 Join-Reihenfolgen - wobei die Einstellung von _optimizer_max_permutations=2000 offenbar ignoriert wird - und das ändert sich auch dann nicht, wenn eine SQL Plan Baseline für die Query erzeugt wird, weil der Optimizer zunächst versucht, einen besseren Plan zu erzeugen, ehe er die hinterlegte Baseline berücksichtigt. In entsprechenden Fällen kann es sinnvoll sein, die Hints der Baseline in ein SQL Profile zu übertragen (bzw. die Hints aus dem library cache zu extrahieren, wozu es ein Skript von Kerry Osborne und Randolf Geist gibt, das im Artikel verlinkt ist) - denn die Hints des Profiles reduzieren den Suchraum des Optimizers unmittelbar. Abschließend weist der Autor allerdings darauf hin, dass dieser Trick der Ersetzung der Baseline durch ein Profile nicht in jedem Fall funktioniert, so dass hier im Einzelfall genauere Analyse erforderlich wäre.

ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke war, dass die zweite Variante syntaktisch falsch sein sollte, und mein erstes Beispiel schien diese Annahme zu bestätigen, aber offenbar hängen der Erfolg oder Misserfolg - und auch das Ergebnis - von der Reihenfolge der Bedingungen ab. Dazu ein Beispiel:

-- 12.1.0.2
drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum id
     , 'AAA' col1
  from dual
connect by level <= 15;
 
create table t2
as
select rownum id
     , 'BBB' col1
  from dual
connect by level <= 15;
 
create table t3
as
select rownum id
     , 'CCC' col1
  from dual
connect by level <= 10;

select /* normal ANSI syntax */
        count(*)
  from t1
  left outer join t3
    on (t3.id = t1.id)
 inner join t2
    on (t3.id = t2.id);
 
  COUNT(*)
----------
        10
 
select /* strange ANSI syntax: predicate order 1, 2 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t1.id)
    on (t3.id = t2.id);

    on (t3.id = t1.id)
                *
FEHLER in Zeile 6:
ORA-00904: "T1"."ID": ungültiger Bezeichner
 
select /* strange ANSI syntax: predicate order 2, 1 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t2.id)
    on (t3.id = t1.id);
 
  COUNT(*)
----------
        15

Demnach ergibt sich:

• die (mehr oder minder) normale Anordnung der Elemente liefert ein Ergebnis mit 10 rows.
• die erste Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t1, t3-t2) liefert einen Fehler.
• die zweite Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t2, t3-t1) liefert 15 rows.

Wenn man dazu ein CBO-Trace erzeugt (Event 10053) wird deutlich, dass der Optimizer im Rahmen seiner Transformationen den Outer Join aus der ersten Query ausschließt, während er in der zweiten (lauffähigen) Query erhalten bleibt, woraus sich die 10 bzw. 15 Ergebnissätze ergeben:

-- Final query after transformations (reformatted)
/* normal ANSI syntax */
SELECT COUNT(*) "COUNT(*)"
   FROM "C##TEST"."T1" "T1"
      , "C##TEST"."T3" "T3"
      , "C##TEST"."T2" "T2"
  WHERE "T3"."ID" = "T2"."ID"
    AND "T3"."ID" = "T1"."ID"

/* strange ANSI syntax: predicate order 2, 1 */
SELECT COUNT(*) "COUNT(*)"
  FROM "C##TEST"."T1" "T1"
     , (SELECT "T3"."ID" "ID"
          FROM "C##TEST"."T3" "T3"
             , "C##TEST"."T2" "T2"
         WHERE "T3"."ID" = "T2"."ID") "from$_subquery$_004"
WHERE "from$_subquery$_004"."ID"(+) = "T1"."ID"

Ich muss gestehen, dass ich mir nicht völlig sicher bin, welche der beiden Interpretationen der Join-Bedingungen korrekt ist - grundsätzlich handelt es sich offenbar um eine Frage der Reihenfolgen:

• wenn zunächst der Outer Join von t1 und t3 erfolgt, der 15 rows liefert, und dann ein Inner Join dieses Ergebnisses an t2 durchgeführt wird und die Verknüpfung über die t3.id erfolgt, die im ersten Zwischenergebnis NULL ist, dann ist das Ergebnis 10 korrekt.
• wenn zunächst der Inner Join von t2 und t3 erfolgt und dann der Outer Join dieses Zwischenergebnisses an t1, dann sollte sich die 15 ergeben.

Persönlich würde ich in einem solchen Fall wahrscheinlich eine explizite Klammerung über Inline-Views (oder CTEs) durchführen (und hoffen, dass der Optimizer diese Klammerung nicht wieder durch Transformationen beseitigt). Ein Bug scheint jedenfalls zu sein, dass die Reihenfolge der Prädikate darüber entscheidet, ob ein Fehler oder ein Ergebnis zurückgeliefert wird. Ich vermute, dass tatsächlich die Reihenfolge der Prädikate entscheidet, welche Verknüpfung Vorrang besitzt - und möglicherweise sagt die ANSI-Spezifikation mehr darüber aus, wie die Verarbeitungsreihenfolge der Join Operationen gedacht ist - wenn ich dazu noch Hinweise finde, ergänze ich sie. Ein Indiz dafür, dass tatsächlich der Inner Join Vorrang haben sollte, liefert postgres: dort liefern beide Varianten 15 Datensätze.

Korrektur: die abschließende Aussage zu postgres war falsch (basierend auf einem copy&paste-Fehler) - tatsächlich verhält sich postgres exakt genauso wie Oracle und liefert 10 rows, einen Fehler ("ERROR:  invalid reference to FROM-clause entry for table t1. TIP:  There is an entry for table t1, but it cannot be referenced from this part of the query.") bzw. 15 rows.

Nachtrag 21.11.2014: Patrick Barel hat mich in seinem Kommentar davon überzeugt, dass es sich nicht um einen Bug handelt (worauf bereits das postgres-Verhalten hindeutete), sondern um ein definiertes Verhalten. Seltsam sieht es trotzdem aus...

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.

Materialized Views für postgres

Mit Materialized Views hat sich mein erster Blog-Eintrag zu postgres im Jahr 2005 beschäftigt. Erst seit Version 9.3 gibt es reguläre MViews in postgres, aber Craig Kerstiens urteilt knapp: 

The problem with them is they were largely unusable. This was because they 1. Didn’t auto-refresh and 2. When you did refresh them it would lock the table while it ran the refresh making it unreadable during that time.

In 9.4 ist zumindest das zweite Problem behandelt: mit Hilfe des Befehls REFRESH MATERIALIZED VIEW CONCURRENTLY kann man den Neuaufbau der MView durchführen, ohne massive Locks zu halten - allerdings muss dazu ein unique index existieren, der sämtliche Datensätze enthält.

Import Foreign Schema für Postgres FDW

Vor ziemlich genau einem Jahr habe ich an dieser Stelle über database links und foreign data wrapper (FDW) geschrieben und mich darüber beklagt, wie unhandlich sich die Einbindung der Objekte unterschiedlicher Datenbanken eines Clusters in einer gemeinsamen Analyse-Datenbank gestaltet, weil man für jede Tabelle eine explizite Definition der Spalten angeben muss, was sich nicht ohne größeren Aufwand automatisieren lässt. In Version 9.5 soll es dafür nun eine Abhilfe geben: Michael Paquier beschreibt das FDW-Kommando IMPORT FOREIGN SCHEMA, das die Einbindung eines kompletten Schemas einer remote-Datenbank ermöglicht: "This command provides an automated way to create foreign table definitions that match remote tables, thereby reducing tedium and chances for error." Mein herzlicher Dank für diese Ergänzung an die postgres-Entwicklung.

SQL Profiles, Hints und Optimizer-Transformationen

Die Möglichkeiten des Plan-Managements sind in aktuellen Oracle-Releases bekanntlich recht umfangreich: neben den historischen Stored Outlines gibt es SQL Plan Baselines, SQL Profiles und SQL Patches. Jede dieser Techniken hat ihre Anwendungsbereiche - und ihre Beschränkungen. Franck Pachot beschäftigt sich in seinem jüngsten Artikel mit SQL Profiles und untersucht die Frage, wieso ein Profile für eine einzelne Tabelle mehrere OPT_ESTIMATE Hints enthalten kann (die die Cardinality-Schätzungen des Optimizers modifizieren). Zur Erinnerung noch mal eine minimale Definition für SQL Profiles: sie sind eine Sammlung von ergänzenden Hints, die der Optimizer für eine Query anlegt, wenn man ihm die Zeit lässt, seine Arbeit in Ruhe durchzuführen - statt unter dem üblichen OLTP-Zeitdruck; für Data Warehouse Systeme (oder wie man die Nicht-OLTP-Systeme sonst einordnen mag) kann das eine ziemlich gut Idee sein, wenn das Parsen gegenüber der Ausführung einer Query kaum ins Gewicht fällt. Aber zurück zum Artikel: durch einen Blick ins CBO-Trace wird klar, dass das mehrfache Erscheinen der Hints den unterschiedlichen Transformationen geschuldet ist, die der Optimizer im Rahmen seiner Optimierungsversuche durchspielt. Ausgehend von dieser Beobachtung kommt der Herr Pachot zu folgendem Schluss:

That observation brings me to the following: what happens to your profiles when you upgrade to a version that brings new optimizer transformations? The optimizer will compare some plans with adjusted cardinalities, compared with some plans with non-adjusted cardinalites. And that is probably not a good idea.

In my opinion, SQL Profiles are just like hints: a short term workaround that must be documented and re-evaluated at each upgrade.

Und das würde ich grundsätzlich unterschreiben - wobei ich mir vorstellen könnte, dass es Umgebungen gibt, in denen es sinnvoll wäre, wenn man dem Optimizer grundsätzlich mehr Zeit zum Nachdenken gibt; aber das wäre wieder ein anderes Feature.

In-Memory Queries und Joins

Maria Colgan setzt ihre einführende Serie zum Verhalten der In-Memory Features in 12.1.0.2 mit einem Artikel Getting started with Oracle Database In-Memory Part IV - Joins In The IM Column Store fort, in dem sie - man ahnt es vielleicht schon - das Verhalten von Joins zwischen In-Memory-Objekten erläutert. Im Beispiel wird ein Join zwischen einer Faktentabelle und einer Dimensionstabelle durchgespielt, der angesichts fehlender Indizes sinnvollerweise als Hash Join durchgeführt wird. Zur Optimierung dieser Operation wird beim Aufbau der Hash Tabelle auf der kleineren Datenmenge zusätzlich ein Bloom Filter (also ein Bit Vektor) angelegt, der als zusätzliches Prädikat an den Scan der zweiten Tabelle übergeben wird. Nach Anwendung der Prädikate der Where-Bedingung wird der Bloom-Filter überprüft und nur die ihm entsprechenden Sätze werden dann an die Hash-Prüfung im Join weitergegeben (dazu gibt's eine übersichtliche Grafik, der leichter zu folgen sein dürfte als den beiden vorangehenden Sätzen). Im Ausführungsplan erscheint der Filter - wie üblich - mit einem Namen :BF0000. Das Verfahren funktioniert auch bei paralleler Ausführung und kann die üblichen Optimierungen verwenden, die dem Optimizer bei Operationen zur Verfügung stehen, bei denen keine In-Memory-Objekte beteiligt sind - also z.B. die Veränderung der Join-Reihenfolge im Hash Join über die swap_join_inputs-Optimierung. Auch diese Erklärung deutet darauf hin, dass es den Oracle-Entwicklern tatsächlich gelungen ist, die In-Memory-Objekte tief in die Kern-Elemente des Systems einzubetten und so auf abenteuerliche Sonderfallbehandlungen verzichten zu können.

Probleme mit Cardinality-Feedback

Dieser Tage hat mir mein Bruder mitgeteilt, dass ich die Wendung "Dieser Tage" zu Einleitungszwecken etwas zu sehr strapaziere.

Dann vielleicht: gestern? Gestern also hat Jonathan Lewis in seinem Blog einen Artikel zum Cardinality-Feedback veröffentlicht, der auf einer Frage basiert, die vor einigen Tagen in Oracle-L aufgeworfen wurde - und bei der es darum ging, dass eine Query, die bei der ersten Ausführung eine angemessene Laufzeit aufwies, bei der zweiten Ausführung sehr langsam wurde, was sich auf die Wirkungen des Cardinality-Feedbacks zurückführen ließ. Nun gibt es eine ganze Reihe bekannter Bugs im Zusammenhang mit dem Cardinality-Feedback, aber im Artikel geht es nicht um einen Bug, sondern um eine grundsätzliche Beschränkung des Features.

Zur Klärung der Voraussetzungen noch mal die einführende Erklärung zum Thema aus dem Blog der CBO-Entwicklung (von Allison Lee, nehme ich an):

Cardinality feedback was introduced in Oracle Database 11gR2. The purpose of this feature is to automatically improve plans for queries that are executed repeatedly, for which the optimizer does not estimate cardinalities in the plan properly.

Das klingt zunächst wie eine gute Idee, aber der Herr Lewis konstruiert im Artikel ein relativ einfaches Beispiel dafür, wie der Optimierungsversuch ein unglückliches Ende finden kann - dann nämlich, wenn mehrere Tabellen im Spiel sind, zu denen die Schätzungen des CBOs nicht viel taugen: durch das Cardinality-Feedback wird im Beispiel der initiale Plan verworfen, weil die tatsächliche Satzanzahl für die erste Tabelle im Join um den Faktor 20 höher war als erwartet (2000 statt 100 Iterationen im NL-Join). Leider verschätzt sich der Optimizer bei der Erzeugung des neuen Plans bei der zweiten Tabelle aber um den Faktor 100 (20000 statt 200), so dass der neue Plan noch sehr viel ungünstiger ausfällt als die ursprüngliche Version. Und da Cardinality-Feedback nur einmal berücksichtigt wird, bleibt es dann bei der schlechteren Lösung. Mit einem Cardinality-Hint könnte man in 11.2 in einem solchen Fall gegensteuern und den geeigneteren Hash Join hervorrufen. In 12.1 wird für den Beispiel-Fall ein adaptive Plan erstellt und bei wiederholter Ausführung erhält man dann den gewünschten Hash Join - aber grundsätzlich gibt es das Cardinality-Feedback auch in 12.1 (unter dem Namen "Statistics Feedback"); obwohl es unter Oracles Optimierungsstrategien nicht unbedingt die beliebteste ist...

Twitter

Wahrscheinlich ist es den aufmerksameren Lesern bereits aufgefallen, dass ich vor kurzem damit begonnen habe, meine Twitter-Botschaften in diesem Blog einzubinden. Der Hintergrund dafür ist, dass ich nach jahrelangem Grübeln endlich dahinter gekommen bin, was ich auf 140 Zeichen unterbringen kann - nämlich minimal kommentierte Links. Früher habe ich aus so was gelegentlich eine zweizeilige Blog-Notiz gemacht, aber da ich solche Verweise ohnehin gleich wieder vergesse, wenn ich mir nicht ein paar Details dazu notiere, kann ich sie auch gleich in anderes Medium auslagern, wo ich sie genauso leicht wiederfinde - so gut wie die blogspot-Suche ist die Twitter-Suche vermutlich auch...

Bitmap-Index zur Zeilenzählung

Auf die folgende Idee hat mich Richard Foote mit dem jüngsten Artikel seiner Serie zur Index Advanced Compression gebracht, in dem er erläutert, dass Bitmap-Indizes aufgrund ihrer extremen Komprimierbarkeit weiterhin ihre Bedeutung behalten (ein paar Anmerkungen zur Serie habe ich hier notiert). In einer Tabelle, bei der die exakte Kenntnis der Satzanzahl von extremer Bedeutung ist, könnte man diese Information durch Ergänzung eines Bitmap-Index auf einer Spalte mit konstantem Wert sehr effektiv ermitteln. Dazu ein kleines Beispiel:

drop table t;
 
create table t (
    id not null
  , padding
  , count_helper
)  
as
select rownum id
     , lpad('*', 50, '*') padding
     , cast(null as number) count_helper
  from dual
connect by level <= 100000;

create bitmap index t_bix on t(count_helper);
 
create unique index t_ix on t(id);
 
set autot trace
 
select count(*) from t;

-------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |       |   100K|     3   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_BIX |       |            |          |
-------------------------------------------------------------------------------

Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          3  physical reads
          0  redo size
        365  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

alter index t_bix invisible;

select count(*) from t;

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    30  (10)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IX |   100K|    30  (10)| 00:00:01 |
----------------------------------------------------------------------

Statistiken
----------------------------------------------------------
         29  recursive calls
          0  db block gets
        263  consistent gets
        208  physical reads
          0  redo size
        365  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der Bitmap-Index auf der Spalte count_helper, die nur NULL-Werte enthält (was den Bitmap-Index - anders als einen B*Tree-Index - nicht berührt) ist winzig und erlaubt die Berechnung der Satzanzahl durch einen sehr billigen BITMAP CONVERSION COUNT: Grundlage sind die minimale und die maximale rowid und die Kenntnis der Anzahl dazwischen liegender Elemente. Wenn man den Bitmap Index als invisible markiert, wird der eindeutige B*Tree-Index auf der (NOT NULL-) Spalte id verwendet, der ebenfalls über einen FAST FULL SCAN eingelesen wird, was aber bei diesem sehr viel größeren Segment sehr viel mehr Arbeit hervorruft (263 consistent gets gegenüber 7 für den bitmap-Fall). Dass der B*Tree-Index so viel größer ist, liegt in erster Linie daran, dass er in den Leaf-Blöcken die rowids der zugehörigen Sätze explizit enthalten muss (während der bitmap-Index eine Offset-basierte Speicherung verwendet). Als NULL-Wert am Satzende bringt der count_helper auch keine deutliche Vergrößerung der Tabelle mit sich. Trotzdem ist diese Idee natürlich nur in ganz speziellen Fällen relevant - nämlich wenn die Kenntnis der genauen Satzanzahl relevant ist und wenn keine massiven konkurrierenden DML-Operationen stattfinden. Aufgrund der massiven Locking-Probleme, die bitmap-Indizes mit sich bringen, sind sie für OLTP-Systeme grundsätzlich eher ungeeignet. Und wenn nur eine ungefähre Kenntnis der Satzanzahl erforderlich ist, kann in 12.1.0.2 auch die neue APPROX_COUNT_DISTINCT verwendet werden. Eine weitere Alternative zum bitmap-Index könnte auch eine Materialized View sein, die die Aggregation in ein zusätzliches Hilfsobjekt verschiebt. Aus Gründen der Vorsicht habe ich die Idee als Kommentar in Richard Footes Blog untergebracht und mir von ihm noch mal bestätigen lassen, dass mir an dieser Stelle kein relevanter Punkt entgangen ist.

first_rows und first_rows_n

Jonathan Lewis weist in seinem Blog darauf hin, dass die Optimizer-Modi first_rows und first_rows_n deutlich weniger gemein haben, als man vielleicht annehmen könnte. Während first_rows seit langem nur noch aus Gründen der Abwärtskompatibiltät unterstützt wird und diverse Heuristiken benutzt, um die Optionen des Optimizers einzuschränken, arbeiten die first_rows_n-Varianten strikt kostenbasiert, ohne dem Optimizer massiv in die Arbeit zu pfuschen. Tatsächlich gibt es allerdings einen Fall, in dem first_rows_n sich so verhält wie first_rows, nämlich die - durchaus merkwürdige - Variante first_rows(0). In diesem Fall wechselt der Optimizer intern tatsächlich auf die Strategie first_rows. Allerdings gibt der Herr Lewis zu Recht zu bedenken: "Mind you, if you only want the first zero rows of the result set there’s a much better trick you can use to optimise the query – don’t run the query."

Für mich persönlich brachte der Artikel noch mal die Erinnerung, dass der Session-Parameter und der Hint first_rows_n eine leicht abweichende Parametrisierung besitzen (die auch explizit dokumentiert ist): während der Parameter nur die Werte 1, 10, 100 und 1000 als gültig betrachtet, kann man im Hint für n jeden beliebigen ganzzahligen Wert angeben. Nachlesen kann man die Details in meinen Kommentaren zum Artikeln und der erläuternden (man könnte auch sagen: richtig stellenden) Antwort des Autors.

Der Vollständigkeit halber hier noch ein Link auf meinen älteren Versuch, dem Costing für first_rows_n auf die Spur zu kommen, zum dem mir damals Randolf Geist die Pointe geliefert hatte (man könnte auch sagen: die Richtigstellung).

Nachtrag 02.11.2014: in einem weiteren Artikel zeigt Jonathan Lewis ein Beispiel, in dem first_rows_10 einen HASH JOIN großer Tabellen hervorruft, weil die cardinalities falsch bestimmt werden (nämlich als 10 für beide Eingangsmengen).

Temp Table Transformation

Randolf Geist erläutert in seinem Blog das Verhalten der Temp Table Transformation, die hauptsächlich (aber nicht ausschließlich) dann anzutreffen ist, wenn eine CTE im Plan mehrfach referenziert wird und mindestens ein (Filter- oder Join-) Prädikat enthält, und weist insbesondere darauf hin, dass diese Transformation nicht kostenbasiert erfolgt, sondern automatisch. In Fällen, in denen eine zusätzliche Bedingung jenseits des CTE-Kontexts eine dramatische Reduzierung der Ergebnismenge hervorrufen würde, ist diese Materialisierung demnach ausgesprochen kontraproduktiv. Darüber hinaus rufen die materialiserten CTEs in solchen Fällen seit 11.2.0.4 offenbar extrem abwegige Cardinality-Schätzungen hervor, die in komplexeren Plänen sehr unglückliche Konsequenzen hervorrufen könnten. Insgesamt scheint es gute Gründe zu geben, die Materialisierung von CTEs sehr genau zu beobachten und ggf. durch den (nach wie vor undokumentierten) Inline-Hint zu vermeiden.

Nachtrag 22.05.2015: in einem Folgeartikel zeigt Randolf, dass die temp table transformation offenbar auch dazu führt, dass alle Spalten der CTE materialisiert werden und nicht nur die, die im Rahmen der folgenden Schritte tatsächlich benötigt werden. In vielen anderen Fällen ist Oracle sehr gut darin, die projection zu optimieren (Ausnahme: MERGE), aber hier ist das offenbar nicht so.

Histogramme, Cardinality und der Optimizer Mode first_rows_100

Eine interessante Antwort auf die Frage nach unplausiblen Cardinalities, die sich trotz frequency histogram ergeben, hat Jonathan Lewis in einem OTN-Forums-Thread gegeben. Der vom Fragesteller gelieferte handliche Testfall zeigte sehr deutlich, dass in seinem System mit 11.2.0.4 für einfache Queries sehr merkwürde Cardinalities ermittelt wurden, obwohl das vorhandene Histogramm die tatsächliche Verteilung exakt abbildete. Diverse Beiträger konnten nur feststellen, dass das Verhalten in ihren Systemen (zwischen 11.2.0.1 und 12.1.0.2) nicht reproduzierbar war, aber der Herr Lewis konnte das fehlende Puzzle-Teil benennen: wenn man den Optimizer-Mode auf first_rows_100 umstellt, werden aus den plausiblen Schätzungen augenblicklich recht nutzlose Zahlen. Ich pflege die Wirkung der first_rows_n-Einstellungen regelmäßig zu unterschätzen und sollte mir endlich merken, wie invasiv dieser Modus tatsächlich ist.

Index Reorganisation

Franck Pachot beantwortet in seinem Blog eine Frage, die mir gelegentlich durch den Kopf gegangen ist, ohne dass ich mich ernsthaft um ihre Beantwortung gekümmert hätte: welche Form der Reorganisation eines Index ist unter welchen Voraussetzungen die passendste? Dazu liefert er einen einfachen Testfall, zu dem er die relevanten Statistiken zum Index und zur Ressourcen-Nutzung im Rahmen der Reorganisation beisteuert. Die Ergebnisse lauten ungefähr:

• COALESCE: eine Online-Operation, die die leaf blocks defragmentiert, aber nicht wieder frei gibt, sondern für die zukünftige Verwendung in der Index-Struktur hält.
• SHRINK SPACE COMPACT: ebenfalls eine Online-Operation, die ungefähr das gleiche leistet wie coalesce, aber etwas mehr LIOs hervorruft und die leaf Blocks etwas effektiver aufräumt (Status: FS4 statt FS2).
• SHRINK SPACE: wieder eine Online-Operation mit nahezu identischer Ressourcen-Nutzung wie das shrink space compact, aber in diesem Fall werden die geräumten Blocks tatsächlich wieder freigegeben.
• REBUILD: erfordert ein share lock, das konkurrierende DML-Operationen blockiert, benötigt aber weniger Ressourcen und führt zu einer vollständig aufgeräumten Index-Struktur: in diesem Fall wird auch die Index-Höhe auf den passenden Wert zurückgeführt - was im Fall des kompletten Neuaufbaus keine besondere Überraschung darstellt.
• REBUILD ONLINE: ein EE-Feature, das - wie der Name vermuten lässt - den Neuaufbau ohne umfangreicheres Locking ermöglicht (in der Regel auf Kosten erhöhter Ressourcen-Nutzung.

Insbesondere die Tatsache, dass das shrink space mehr leistet als das shrink space compact ist ein Aspekt, der mir schon mehrfach entfallen ist..

Advanced Index Compression in 12c

Leider schaffe ich es derzeit nicht, mir eigene Gedanken zu machen, so dass ich mich darauf beschränken muss, anderer Leute Ergebnisse zu reproduzieren. Einer der Autoren, bei denen ich das recht gerne mache, ist Richard Foote, der inzwischen wieder sehr regelmäßig neue Artikel schreibt. Zwei davon betreffen die in 12c eingeführte Advanced Index Compression:

• Index Compression Part V: 12c Advanced Index Compression (Little Wonder): erläutert die Hintergünde dieser neuen Option (die eigens lizenziert werden muss). Bisher war es bei der klassischen Index--Komprimierung, bei der Duplikate in den führenden Spalten eines Index durch Referenzen auf eine Prefix-Sektion im Block ersetzt werden, ohne Weiteres möglich, den Index durch die Komprimierung zu vergrößern - dann nämlich, wenn es zu wenig Wiederholungen gab, um den Overhead der eingeführten Symboltabelle zu rechtfertigen. Bei der Advanced Index Compression wird dieses Problem auf zwei Ebenen behandelt: zum einen automatisiert die Option die Bestimmung eines sinnvollen Komprimierungsgrads, zum anderen wählt sie die Komprimierung blockspezifisch aus, so dass einzelne Bereiche des Index, die sich gut komprimieren lassen, besser gepackt werden als andere Bereiche mit wenigen Duplikaten.
• Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl): beschäftigt sich mit den technischen Details der Implementierung und mit der Repräsentation der Komprimierung im Block-Dump. Der zentrale Punkt sind die Einträge kdxlepnro (nro = number of rows?) und kdxlepnco (nco = number of columns?) im Index-Block-Dump, von denen der erste die Anzahl der Einträge in der Prefix-Tabelle angibt und der zweite die Anzahl komprimierter Spalten. Bei kdxlepnco taucht zusätzlich in Klammern die Angabe (Adaptive) auf, die auf die Verwendung des neuen Features hinweist.
• Index Advanced Compression vs. Bitmap Indexes (Candidate): beantwortet die Frage, ob die Advanced Index Compression die Nutzung von Bitmap Indizes uninteressant macht, mit einem schallenden Nein. Während B*Tree-Indizes sich nur auf Block-Ebene komprimieren lassen, kann ein Bitmap-Index die Werte der gesamten Tabelle zusammenfassen. Bei geeigneter Werte-Clusterung kann ein Bitmap-Index extrem kompakt werden - und sehr viel kleiner als jeder noch so gut gepackte B*Tree-Index.

Weitere Artikel sind angekündigt, die ich dann voraussichtlich ebenfalls hier zusammenfasse werde. Grundsätzlich sieht das wie eine sehr sinnvolle Funktionalität aus - ich hatte mich in der Vergangenheit häufiger gefragt, wieso ich zunächst einen plausiblen Kompressionsgrad ermitteln musste (per ANALYZE INDEX), ehe ich diesem Vorschlag folgte, da ich an dieser Stelle recht wenig Spielraum für individuelle Anpassungen und vom Vorschlag abweichende Entscheidungen sah.

Detailsinformationen zu den system statistics

Franck Pachot liefert im dbi services Blog ein nettes Skript zur Analyse der Systemstatistiken, auf denen das Costing des Optimizers basiert. Dabei ergänzt er zu den Basisinformationen aus (SYS.)AUX_STATS$ diverse Angaben zu ihrer Berechnung, die man sich ohne Skript mehr oder minder mühsam zusammensuchen muss.

Optimierung für pg_dump und pg_restore

Josh Berkus erwähnt in seinem Blog einige Optionen, mit denen man die Performance der postgres-Export und Import-Tools pg_dump und pg_restore verbessern kann. Die Liste enthält unter anderem folgende Punkte:

• Parallelisierung über die -j multiprocess Option (für pg_restore und seit 9.3 auch für pg_dump).
• Komprimierung des Dumps bei der Erstellung.
• Deaktivierung der Binär-Replikation im Zielsystem während des Imports.
• Anpassungen der postgresql.conf des Ziel-Clusters zur Unterstützung einer schnellen Wiederherstellung. Die Wiedergabe der genannten Optionen erspare ich mir.

Da das Importieren von postgres-Dumps ohne Optimierung eher zäh abläuft, werde ich diese Hinweise bei nächster Gelegenheit berücksichtigen.

Read Privileg in 12.1.0.2 (ohne Select for Update)

Zu den eher unverständlichen Entscheidungen, die man bei Oracle im Zusammenhang der Definition von Privilegien gemacht hat, gehörte sicherlich die Zuordnung des Rechts zur Ausführung der Operation SELECT FOR UPDATE zum einfachen SELECT-Privileg. Dadurch konnten Benutzer allerlei Unfug anstellen, weil die Operation recht massive Sperren erzeugt. In 12.1.0.2 gibt es jetzt das neue READ-Privileg, das SELECT FOR UPDATE explizit nicht mehr enthält. Den Hinweis auf diese Ergänzung habe ich bei Gary Myers gefunden.

SQL Developer Tipps

Nachdem ich im September bei sehr vielen Artikeln der Reihe 30 Posts in 30 Days von Jeff Smith den Eindruck hatte, dass da Features erwähnt werden, die ausgesprochen nützlich wären, wenn ich sqlplus gelegentlich beiseite legen würde, scheint's mir angemessen, den Index-Artikel zur Serie zu verlinken. Der SQL Developer ist wirklich ein großartiges Tool - insbesondere in Anbetracht seines Preises.

Log File Sync, Log File Parallel Write und Durchschnittswerte

Nikolay Savvinov erläutert in seinem Blog die Probleme, die sich ergeben, wenn man die im AWR angezeigten Duchschnittswerte der Events Log File Sync (LFS) und Log File Parallel Write (LFPW) allzu ernst nimmt. Dazu noch mal eine minimale Definition der Events:

• Log File Sync: ist das Event, auf das User-Sessions warten, die ein Commit abgesetzt haben, das vom LWGR verarbeitet werden soll.
• Log File Parallel Write: ist das Event auf das der LWGR wartet, der die Inhalte des log Buffers auf die Platte schreiben will. Dies ist demnach ein I/O-Event. Da der LWGR mehrere Session-Commits in einem Gruppen- (oder Piggyback-) Commit zusammenfassen kann, können sich für LFPW verlängerte Wartezeiten ergeben, die dann zu verlängerten LFS-Waits führen.

In einem Single User System liegen LFS und LFPW recht nah beieinander, aber je mehr Sessions gleichzeitige Commits absetzen, desto mehr Gruppen-Commits ergeben sich und desto stärker werden die durchschnittlichen LFS-Waits verlängert (die LFPW-Waits natürlich auch, aber der Effekt betrifft LFS stärker, da jedes LFPW-Waits mehrere LFS-Waits hervorruft). AWR-Durchschnittswerte sind in diesem Fall deutlich weniger aussagekräftig als ASH-Daten oder Wait-Histogramme. Ich spare mir an dieser Stelle die komplette Nacherzählung der Untersuchung (und ihrer Schlussfolgerungen), ergänze aber noch den auch beim Herrn Savvinov erwähnten Link auf einen älteren Artikel von James Morle, der sich dem gleichen Thema widmet.

Tablespace verkleinern

Jonathan Lewis hat dieser Tage zwei Artikel zum Thema der Verkleinerung von Tablespaces veröffentlicht:

• http://jonathanlewis.wordpress.com/2014/09/19/shrink-tablespace-2/: weist darauf hin, dass es in aktuellen Releases sinnvoll ist, zugehörige Indizes vor einer Move-Operation einer Tabelle als unusable zu markieren, da die Segmente in diesem Fall sofort gelöscht werden (nach der Move-Operation findet diese Löschung ohnehin statt, da die Index-rowid-Referenzen durch die Bewegung der Tabelle unbrauchbar werden). Diese Löschung erlaubt dann die Tabelle "weiter vorn" im data file abzulegen, die Wahrscheinlichkeit ungenutzter Lücken nimmt ab und die HWM kann nach der Reorganisation weiter nach unten verschoben werden. Dazu gibt es - wie üblich - ein kleines Beispiel.
• http://jonathanlewis.wordpress.com/2014/10/01/shrink-tablespace-3/: zeigt, dass die Verschiebung der Tabellen "nach vorne" auch in system managed tablespaces nicht in jedem Fall ohne Probleme funktioniert - insbesondere wenn die Extents nicht mit uniform size angelegt wurden -, weil Oracle eine elaboriertere Logik der Nutzung freien Speicherbereiche verwendet und zunächst versucht, bereits teilweise gefüllte 1mb-Bereiche zu füllen.

Nachtrag 09.10.2014: Vor ein paar Wochen hatte Franck Pachot ein auf x$ktfbue zugreifendes  (und damit unhandliche Operationen auf dba_extends vermeidendes) Skript zur Generierung von Statements zur Verkleinerung von data files veröffentlicht. Bei Tom Kyte habe ich vor vielen Jahren etwas Ähnliches ausgeborgt, aber da ich nicht mehr als DBA praktiziere, brauche ich dergleichen nicht mehr oft.

Nachtrag 17.11.2014: Noch ein weiterer Artikel von Jonathan Lewis zum Thema mit einer Aufzählung pragmatischer Hinweise (zum Teil in Überschneidung mit den vorangehenden Artikeln):

• der recylebin sollte geleert werden, ehe die Reorganisation startet.
• Indizes sollten vorab als unusable markiert werden (um diese Segmente zu löschen, die ohnehin invalidiert werden).
• man sollte über die Wirkungen der minimum extent size nachdenken.
• nicht immer ist die "highest object first" Strategie die sinnvollste.
• die Verschiebung mehrerer kleinerer Objekte kann Raum für größere Objekte schaffen.
• unter Umständen ist die Verschiebung aller Objekte in einen neuen TS das effektivste Verfahren.

Analyse des Oracle Kernel Verhaltens mit SystemTab

Luca Canali beschäftigt sich in seinem Blog recht regelmäßig mit Hilfsmitteln, die es erlauben, das Verhalten des Oracle Servers im Zusammenspiel mit den OS-Komponenten zu untersuchen. Ein solches Hilfsmittel ist das tracing and probing tool SystemTab, das die Arbeitsweise des Linux-Kernels ausleuchtet. Im Artikel zeigt der Autor die Möglichkeiten der Verknüpfung von SystemTab mit dem Oracle Wait Interface und den X$-Strukturen:

These techniques aim to be useful as well as fun to learn for those keen into peeking under the hood of the technology and improve their effectiveness in troubleshooting and performance investigations.

Die Details nachzuerzählen, erspare ich mir und belasse es bei diesem Verweis, dem ich möglicherweise gelegentlich intensiver nachgehen könnte.

Attribut-Clustering mit 12.1.0.2

Richard Foote hat schon vor einigen Wochen eine kurze Einführung zum Feature Attribute Clustering geliefert, das in 12.1.0.2 verfügbar wurde. Dieses Feature ermöglicht die Angabe einer Clustering-Klausel im CREATE TABLE-Kommando, die eine (ungefähre) physikalische Clusterung der Tabellendaten nach dem Inhalt einer angegebenen Spalte (oder auch mehrerer Spalten) hervorruft. Eine Syntax-Variante dazu lautet etwa:

create table ...

clustering by linear order (col1)

Diese Klausel ruft beim Direct-Path Insert neuer Daten eine Sortierung hervor, die sicherstellt, dass die Daten am geeigneten Platz eingefügt werden - was natürlich einen Einfluss auf die Performance der Operation hat. Umgekehrt ist dafür dann der Clustering-Factor entsprechend sortierter Indizes niedriger, was sich dann wieder positiv auf entsprechende Index-Zugriffe auswirkt. Für conventional path DML wird das Feature nicht unterstützt (worauf Stefan Köhler in seinem Kommentar hinweist). Demnach scheinen hier OLTP-Systeme wohl nicht das vorgestellte Ziel zu sein.

Buffer re-visits im Rahmen von Index Range Scans

Nichts Neues, aber ein schönes Beispiel für eines der zentralen Themen bei der Optimierung von Zugriffen in relationalen Datenbanken: Tanel Poder erläutert in seinem Artikel About index range scans, disk re-reads and how your new car can go 600 miles per hour!, warum die Ergänzung von Indizes für analytische Queries (mit Zugriff auf größere Datenmengen) in vielen Fällen keine besonders gute Idee ist: der Zugriff über den (sortierten) Index führt zum wiederholten Zugriff auf die (unsortierten) Tabellendaten - der gleiche data block wird unter Umständen wieder und wieder besucht, so dass das Volumen der gelesenen Daten letztlich das Volumen der Tabelle (und des Index) massiv übersteigt. Diese re-visit-Effekte werden sichtbar in den Trace-Files, die mit Hilfe der Events 10046 (also SQL Trace) und 10298 (ksfd i/o tracing) erzuegt wurden - letzteres enthält die blkno (also Blocknummer), die im Beispiel in einigen Fällen zehn Mal erscheint. Da diese erneuten Besuche (nach Buffer Cache Maßstäben) zeitlich weit auseinander liegen können, ergeben sich für viele re-visits physikalische Leseoperationen, weil die Daten durch folgende reads bereits wieder aus dem Cache verdrängt wurden. Mit Hilfe von (range scans auf) Indizes sind Massendatenoperationen oft nicht optimierbar, was den Herrn Poder zum - ein klein wenig reißerisch klingenden - Titel seines Artikels bringt: um dramatische Beschleunigungen für große Reporting-Fragestellungen zu erreichen, benötigt man andere Verfahren - und dabei denkt er an Exadata und die In-Memory-Option: also Strategien, die die relevanten Datenmengen reduzieren und dabei die Zugriffsstrategien grundsätzlich verändern.

Set to Join Konvertierung

Noch ein interessanter Hinweis von Franck Pachot auf eine Transformation, die der Optimizer bereits seit längerer Zeit beherrscht, die aber nur aktiv wird, wenn optimizer_feature_enabled auf 12.1.0.2.1 gesetzt wird - also auf eine Version, die es bisher noch gar nicht gibt. Per Hint (SET_TO_JOIN) kann man die Verwendung des Verfahrens aber bereits seit längerer Zeit aufrufen und erreicht damit, dass Set-Operationen wie MINUS oder INTERSECT in einen Join umgewandelt werden, was für geeignete Mengenverhältnisse durchaus interessant sein kann, insbesondere weil es potentiell kostspielige SORT UNIQUE Operationen vermeiden kann.

COUNT_DISTINCT für postgres

Thomas Vondra erläutert in seinem Blog die selbst gebaute Funktion COUNT_DISTINCT, mit deren Hilfe sich COUNT(DISTINCT column) Operationen beschleunigen lassen, da sie auf eine (inhaltlich unnötige) Sortierung der Ergebnisse verzichtet. Ursprünglich verwendete er bei seiner Implementierung eine Hash Table, die inzwischen aber durch ein einfaches Array mit sortierten und unsortierten Elementen ersetzt wurde. Interessant finde ich dabei einerseits die großartigen Möglichkeiten der Erweiterbarkeit des postgres-Funktionsumfangs und andererseits die Tatsache, dass hier offenbar ein ähnlicher Lösungsweg gewählt wurde wie der, den Oracle (und andere) bei der Implementierung von HASH GROUP BY Operationen gewählt haben.

Vorteile des Zugriffs auf den In-Memory column store.

Maria Colgan erläutert im Rahmen ihrer Serie zur In-Memory-Option in Oracle 12.1.0.2 die spezifischen Vorteile, die ein TABLE ACCESS IN MEMORY FULL mit sich bringt:

• der Zugriff beschränkt sich auf die relevanten Spalten - was bei der klassischen Zeilen-orientierten Speicherung natürlich nicht möglich wäre.
• die IM-Daten liegen in komprimierter Form vor, weil sie beim Laden in den Speicher komprimiert werden.
• unnötige Daten werden beim Lesen ausgeschlossen, was durch In-Memory Storage Indexes sichergestellt wird. Diese Indizes enthalten die minimalen und maximalen Werte, die in einer In-Memory Compression Unit (IMCU) gespeichert sind.
• Optimierung des Zugriffs auf relevante IMCUs durch SIMD vector processing (Single Instruction processing Multiple Data values). Diese Strategie erlaubt (wie der Name andeutet) die gleichzeitige Verarbeitung mehrerer Spaltenwerte innerhalb einer einzigen CPU instruction.

Zur Analyse des Verhalten gibt es in v$statname allerhand neue IM scan % Statistiken, die man sich im Rahmen einer intensiveren Beschäftigung mit dem Thema genauer anschauen sollte.

Tabellenzugriff über Index-Kombinationen

In seinem Blog hat Jonathan Lewis dieser Tage die Quizfrage gestellt, wieso es möglich ist, dass eine Query mit einer Einschränkung der Form:

where col1 between 1 and 3

durch eine Umwandlung der Bedingung in die folgende Form optimiert werden kann:

where col1 = 1 or col1 > 1 and col1 <= 3

Die Antwort lautet (natürlich): durch die Kombination mehrerer Zugriffsmöglichkeiten - im Beispiel, an das der Herr Lewis gedacht hatte, geht es um die Kombination von bitmap Indizes, aber theoretisch wäre auch eine Kombination von B*Tree-Indizes möglich.

Pipelined Functions mit postgres

In Oracle dienen pipelined table functions dazu, PL/SQL collections als Tabellen zu verkleiden und darauf mit einfachem SQL zugreifen zu können. Hier ein kleines Beispiel dazu, wie man in postgres ein ähnliches Verhalten erreichen kann:

create or replace function f_pipeline_test(lines_limit int)
returns table (
    table_schema    text
  , table_name      text
) as
$func$

declare sqltext text = 'select table_schema::text, table_name::text
                          from information_schema.tables
                         where table_schema = ''pg_catalog''
                         limit ' || lines_limit;

begin

   return query
   execute sqltext;

end
$func$  language plpgsql;

select * from f_pipeline_test(5);

table_schema |  table_name
-------------+--------------
pg_catalog   | pg_statistic
pg_catalog   | pg_type
pg_catalog   | pg_roles
pg_catalog   | pg_shadow
pg_catalog   | pg_authid
(5 Zeilen)

Undokumentierter Result_Cache-Hint-Parameter in 12.1.0.2

Christian Antognini weist in seinem Blog darauf hin, dass der Hint result_cache in 12.1.0.2 einen Parameter erhalten hat, mit dessen Hilfe man festlegen kann, nach wie vielen Sekunden ein Ergebnis aus dem result cache gelöscht werden soll, auch wenn sich das zugrunde liegende Objekt nicht geändert hat. Dieser Parameter SNAPSHOT wird in der offiziellen Dokumentation nicht erwähnt.

Jonathan Lewis über In-Memory-Effekte

In den letzten Wochen hat Jonathan Lewis einige Artikel zum Verhalten der in 12.1.0.2 erschienen In-Memory Option veröffentlicht, die ich hier kurz zusammenfasse - hauptsächlich allerdings, um die Links an geeigneter Stelle zu sammeln:

• In-memory limitation: erläutert einen Fall, in dem die Kombination der In-Memory columnar storage mit der traditionellen Zeilen-basierten Ablage nicht das leistet, was man sich davon versprechen könnte - bzw. was sich der Herr Lewis hätte vorstellen können. Sein Beispiel enthält eine relativ breite Tabelle, deren Spalten zum Teil In-Memory abgelegt sind und zum Teil nicht. Ein Zugriff der sich auf die In-Memory Spalten beschränkt, wird auch über eine In-Memory-Operation abgebildet (im Plan: TABLE ACCESS INMEMORY FULL), aber so bald eine weitere "no inmemory" Spalte in der Select-Liste ergänzt wird, ergibt sich ein normaler FTS - obwohl es in diesem Fall unter Umständen effizienter wäre, die rowids der betroffenen Datensätze über den In-Memory-Zugriff zu bestimmen und darüber zuzugreifen. Anhand eines entsprechenden Beispiels mit allerlei Hints wird gezeigt, dass ein solcher Plan grundsätzlich möglich wäre, vom Optimizer aber nicht in Betracht gezogen wird. In einem Kommentar  von Franck Pachot und der Antwort von Jonathan Lewis gibt es noch ein paar Überlegungen zur Frage der technischen Umsetzung der rowid-Ablage im Rahmen der In-Memory Option, die allerdings an diser Stelle nicht definitiv beantwortet wird.
• In-memory Aggregation: erklärt eine recht komplexe Variante der Optimierung von Aggregationen: "it’s a cunning mechanism combining aspects of the star-transformation (but without the bitmap indexes), Bloom filters, and “group-by” placement to minimise the cost of aggregation over high-volume joins." Angesichts der relativ hohen Komplexität des Verfahrens spare ich mir die Nacherzählung und beschränke mich auf die Erwähnung des Stichworts vector transformation (samt zugehörigem Hint; im Plan erscheinen dazu diverse Schritte zum Aufbau und zur Verwendung der Key Vector-Elemente, die - so weit ich es verstehe - In-Memory Key-Value-Elemente darstellen und deren Verwendung Ähnlichkeiten mit der von Bloom-Filtern besitzt).
• In-memory Consistency: beschäftigt sich mit der Umsetzung von Lesekonsistenz im Rahmen der In-Memory Option und betrachtet dazu die Session-Statistiken. Die Ausführungen enthalten diverse Hinweise auf die relevanten Statistiken, aber, so weit ich sehe, keine extremen Überraschungen. 

Sollte Jonathan Lewis zeitnah weitere Artikel zum Thema liefern, so werde ich sie hier voraussichtlich ergänzen.

String-Aggregation im SQL Server

Eine Funktionalität, die in Oracle und postgres schon seit längerer Zeit verfügbar ist, aber im SQL Server noch auf der (connect) Wunschliste steht, ist die String-Aggregation - also die Möglichkeit einer Gruppierung, bei der mehrere Elemente einer Gruppe in einem Feld konkateniert werden (also z.B. alle Mitarbeiter eines Departments in Oracles klassischer EMP-Tabelle). Für den SQL Server gibt es immerhin diverse Möglichkeiten, das Verhalten nachzubilden, und Aaron Bertrand stellt diese Varianten und ihr Verhalten (und insbesondere ihre Performance) im SQLPerformance.com Blog vor. Interessant finde ich unter anderem, dass die (für mich) nahe liegende Variante der Verwendung einer rekursiven CTE im Test mit großem Abstand die langsamste Lösung darstellt. 

Commit-Korrekturen mit git

Hat nichts mit Datenbanken zu tun, sondern mit Versionsverwaltung: git benutze ich zwar schon seit einiger Zeit, habe es aber trotz ernsthafter Absichten nie geschafft, mir die Details ausreichend genau anzuschauen, um auf unerwartete Probleme angemessen reagieren zu können. Daher hat mir der Hinweis eines Kollegen auf den Artikel On undoing, fixing, or removing commits in git von Seth Robertson sehr geholfen, denn darin steht, was zu tun ist, wenn man sich in eine unglückliche Lage manövriert hat.

Nachtrag 03.11.2014: und hier noch ein paar Hinweise zur Verschönerung für git log von Filipe Kiss.

Neue Features in Postgres

Eine kleine Sammlung neuer Features, die in Postgres 9.4 eingeführt wurden bzw. in 9.5 eingeführt werden sollen - oder darin wünschenswert wären:

• Craig Kerstiens hat dieser Tage seine Wunschliste mit neuen Features, die er gerne in Version 9.5 sehen würde, veröffentlicht. Auf Position 1 steht dabei das Upsert - aka MERGE - und das scheint auch mir die erstaunlichste Lücke im gegenwärtigen postgres-Funktionsumfang zu sein. Immerhin ist es seit Version 9.1 möglich, MERGE mit Hilfe von CTEs nachzubilden - allerdings scheint das Verfahren ein paar potentielle Probleme (race conditions, lost updates) hervorzurufen.
• Über ein in 9.5 definitiv umgesetztes Feature schreibt Michael Paquier: die Möglichkeit, das WAL-Logging für eine Tabelle via ALTER TABLE zu aktivieren und zu deaktivieren. Grundsätzlich gibt es unlogged tables bereits seit Version 9.1, aber eine Umstellung des Verhaltens war bislang nicht möglich. Etwas ausführlicher ist der Artikel, den Hubert Lubaczewski zum gleichen Thema geschrieben hat.
• Josh Berkus erläutert die bereits mit 9.4 eingeführte Funktion percentile_cont, deren Verhalten anscheinend dem der gleichnamigen Funktion unter Oracle entspricht.

Abgeleitete IS NOT NULL Prädikate

Jonathan Lewis weist (im Rahmen einer Quiz-Frage, die sehr schnell ihre Antwort fand) darauf hin, dass der Optimizer implizite IS NOT NULL Prädikate schon seit vielen Releases aus der Join-Bedingung ableiten kann (im Sinne von: wenn ein Join durchgeführt wird, kann die Join-Bedingung keine NULL-Werte enthalten haben).

drop table people

Wenn es bei xkcd einen Strip mit SQL-Queries gibt, muss er hier verlinkt werden - auch wenn's vielleicht eher um ein philosophisches Problem geht.

Definition von AWR Intervallen

Doug Burns hat dieser Tage in seinem Blog zwei Artikel zum Thema der Definition von AWR Intervallen veröffentlicht. Erinnerungswürdig scheinen mir dabei vor allem die folgenden beiden Punkte:

• in einem produktiven System ist es sinnvoll, die Retention der AWR Snapshots auf einen deutlich höheren Wert als den default von 8 Tagen zu setzen (35-42 Tage oder gleich: unbegrenzt). 
• weniger sinnvoll ist es, die AWR Intervalle selbst zu verkürzen: also vom Standard-Intervall von 1h auf 15 oder 30 min zu gehen. Die Begründung dieser Empfehlung ist, dass es zum Zweck der detaillierten Analyse andere geeignetere Tools gibt (ASH, SQL Monitoring), während AWR eher zur globalen Analyse des Systemverhaltens gedacht ist, und in diesem Fall erschweren kleinere Intervalle die Auswertung.

Das halte ich für einleuchtend.

APPROX_COUNT_DISTINCT

Eigentlich stand auf meinem Erledigungszettel die Idee, eine kurze Untersuchung der in 12.1.0.2 neu eingeführten APPROX_COUNT_DISTINCT Funktion durchzuführen, mit deren Hilfe man die ungefähre Anzahl der distinkter Werte in einer Spalte effizient ermitteln können soll - aber Luca Canali hat gerade einen entsprechenden Artikel veröffentlicht und so genau wie der Herr Canali hätte ich mir den Fall ohnehin nicht angeschaut. Hingewiesen wird im Artikel unter anderem auf folgende Punkte:

• beim count(distinct column_value) hängt die Performance entscheidend von der Notwendigkeit großer Sortierungen ab - und damit von der Spaltenbreite und der Anzahl distinkter Werte: je breiter die Spalten und je näher die Anzahl unterschiedlicher Werte an die Satzanzahl heranreicht, desto teurer werden die Sortierungen (inklusive temp I/O waits).
• APPROX_COUNT_DISTINCT basiert auf einem Verfahren, dass eine geringe Memory-Nutzung erfordert und das gut skaliert. Bezahlt wird diese Optimierung mit einem Verzicht auf vollständige Genauigkeit: wie der Name der Funktion schon andeutet, wird nicht gezählt, sondern überschlagen.
• In den Tests des Herrn Canali ergibt sich eine massive Beschleunigung der Operationen bei einer recht hohen Genauigkeit. Dabei betrifft die Beschleunigung natürlich in erster Linie die Fälle, in denen das exakte Zählen kostspielig wird (also: viele distinkte Werte, breite Spalten) - für Spalten mit sehr niedriger Kardinalität nähert sich die Laufzeit beider Operationen der Zeit, die für das Lesen der Daten (über FTS oder IFFS) benötigt wird.
• der APPROX_COUNT_DISTINCT-Aufruf lässt sich sehr gut parallelisieren.
• hinter der neuen Funktion steht der HyperLogLog (HLL) Algorithmus, den Alex Fatkulin (in einem bei Luca Canali verlinkten Artikel) folgendermaßen erläutert: "in a nutshell, it relies on counting the number of trailing (or leading) zeroes in the binary stream and using that information to estimate number of unique values with a very high degree of accuracy."
• im Flame Graph der Operation (ohne den zu ergänzen der Herr Canali dieser Tage keinen Artikel beendet) findet man den Aufruf von qesandvHllAdd.
• nicht ganz klar ist mir, ob der verwendete Algorithmus mit dem des in der Statistikerzeugung verwendeten approximate NDV identisch ist - Christian Antognini scheint das zu verneinen.

Für geeignete Fragestellungen sollte die Funktion jedenfalls ziemlich nützlich sein.

Nachtrag 23.10.2014: Christian Antognini zeigt, dass die Genauigkeit der Funktion für numerische Werte recht gut ist (Abweichung höchstens 4%), bei deutlich reduzierter Ressourcennutzung und erhöhter Performance gegenüber der Standardfunktion.

ALTER INDEX ... COMPUTE STATISTICS ist wirklich deprecated...

Im OTN-Forum wurde dieser Tage die Frage gestellt, ob nach einem Index-Aufbau ein expliziter Neuaufbau von Statistiken erforderlich ist. In einem Kommentar erwähnte ein Diskussionsteilnehmer sein Standardverfahren des Aufrufs von ALTER INDEX ... COMPUTE STATISTICS nach dem Index-Aufbau. Nun hatte ich eine relativ klare Vorstellung von den Unterschieden bei der Statistikerstellung unter Verwendung des (veralteten) ANALYZE-Kommandos (für Tabellen) und dem (aktuellen) Aufruf von DBMS_STATS.GATHER_TABLE - aber keine Ahnung, wie die Statistiken aussehen, die durch ALTER INDEX ... COMPUTE STATISTICS erzeugt werden. Ein Test zeigt ein recht drastisches Ergebnis: offenbar leistet die COMPUTE STATISTICS-Option des ALTER INDEX-Kommandos in 11.2 rein gar nichts:

-- 11.2.0.1
-- drop test table
drop table t;
 
-- create test table
create table t
as
select rownum id
    , mod(rownum, 10) col1
  from dual
connect by level <= 100000;
 
-- delete a lot of rows
create index t_idx on t(id);
delete from t where col1 <= 5;
commit;
 
-- statistics after object creation and delete
select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
 
INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
----------------------------- ---------- ----------- -------------------
T_IDX                             100000         222 04.08.2014 19:59:22
 
-- table stats deleted
exec dbms_stats.delete_table_stats(user, 't')
 
select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
 
INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
----------------------------- ---------- ----------- -------------------
T_IDX
 
-- alter index compute statistics;
alter index t_idx compute statistics;
 
select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
 
INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
----------------------------- ---------- ----------- -------------------
T_IDX
 
-- dbms_stats
exec dbms_stats.gather_table_stats(user, 't', cascade=>true)
 
select index_name, num_rows, leaf_blocks, last_analyzed from user_indexes where index_name = 'T_IDX';
 
INDEX_NAME                      NUM_ROWS LEAF_BLOCKS LAST_ANALYZED
----------------------------- ---------- ----------- -------------------
T_IDX                              40000         222 04.08.2014 19:59:24

Eine ganz so große Überraschung ist dieses Verhalten allerdings insofern nicht, als bereits die Dokumentation zu 10.2 erklärte:

COMPUTE STATISTICS Clause: This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors.

In der Dokumentation zu 11g erscheint die Klausel überhaupt nicht mehr.

SQL Server ETL mit T-SQL oder SSIS

Es ist schon eine Weile her, dass ich ernsthaft mit dem SQL Server gearbeitet habe - und ich bin mir nicht sicher, ob ich ernsthaft behaupten würde, je ernsthaft mit den SQL Server Integration Services (SSIS) gearbeitet zu haben. An Projekten, in denen SSIS eingesetzt wurde, war ich allerdings beteiligt, habe aber immer dafür plädiert, die SSIS in diesem Zusammenhang nur als Container für SQL-Operationen zu verwenden - ganz im Sinne eines älteren Artikels von James Serra, der sich mit den Vorteilen von SSIS und T-SQL im ETL-Zusammenhang beschäftigt, und dort anmerkt:

If you decide T-SQL is the way to go and you just want to execute a bunch of T-SQL statements, it’s still a good idea to wrap them in SSIS Execute SQL Tasks because you can use logging, auditing and error handling that SSIS provides that T-SQL does not.  You can also easily run SSIS Execute SQL Tasks in parallel so you are able to run stored procedures in parallel.

Darüber hinaus liefert der Artikel eine ganze Reihe von Argumenten für und wider die beiden Varianten. Auf die Idee, den Artikel hier zu erwähnen, hat mich die aktuelle Notiz SQL Server Agent job steps vs SSIS gebracht, die neben einem Link auf den älteren Artikel auch noch ein paar zusätzliche Anmerkungen zum SQL Server Agent enthält - was der Titel vielleicht schon andeutet...