NVL und Count Stopkey

Ein kleiner - aber wichtiger - Hinweis von Dom Brooks: für Queries mit einer rownum-Einschränkung ist normalerweise die COUNT STOPKEY Optimierung im Spiel, die die Ausführung der Operation abbricht, wenn die gewünschte Satzanzahl erreicht ist. Wenn man allerdings eine solche Einschränkung mit einer NVL-Funktion kombiniert, also etwa:

where  rownum <= nvl(:rn,rownum)

dann wird die Optimierung deaktiviert und die Ausführung bis zum bitteren Ende fortgesetzt.

Limit für die Verwendung von "colored SQL" im AWR

Das Automatic Workload Repository (AWR) gehört meiner Meinung nach zu den großartigsten Errungenschaften der jüngeren (oder auch nicht mehr ganz so jüngeren) Oracle-Geschichte, da es eine Historie von Abfragen und ihrer Performance bereitstellt. Allerdings ist diese Historie nicht vollständig, da sie auf Sampling basiert - es kann also vorkommen, dass interessante Queries nicht erfasst werden. Um dieses Problem zu lösen, wurde in 11g die Routine dbms_workload_repository.add_colored_sql eingeführt, die es ermöglicht Abfragen "einzufärben", was nichts anderes bedeutet, als dass ihre Protokollierung erzwungen wird - ein knappes Beispiel zum Thema findet man bei Marcin Przepiorowski.

Allerdings scheint es, dass diese Möglichkeit in der Praxis nicht allzu intensiv genutzt wird, denn in einem Thread im OTN-Forum wurde jetzt ein offenbar bisher nicht allgemein bekanntes Limit angesprochen: man kann nur 100 Queries kolorieren, ehe man den Fehler "ORA-13534: Current SQL count(100) reached maximum allowed (100)" erreicht. Jonathan Lewis hat via SQL Trace beobachtet, dass hier offenbar ein hart kodiertes "selects count(*) from the wrm$_colored_sql" im Spiel ist, das bei einem Ergebnis größer 100 zum Abbruch führt. Andererseits ist das Feature auch alles andere als gut dokumentiert - und in der Folge nicht allgemein bekannt.

Row Pieces in Tabellen mit mehr als 255 Spalten

Jonathan Lewis listet einige der Gründe, die gegen die Verwendung von Tabellen mit mehr als 255 Spalten sprechen - bekanntlich führt diese Spaltenanzahl zur Aufteilung einer row in mehrere row pieces, und damit zu inter- oder intra-row-chaining und sonstigem Ärger. Zu den unerfreulichen Effekten gehören:

• in 10g führt die Statistikerfassung bereits bei weniger als 255 Spalten - nämlich bei etwa 165 Spalten - dazu, dass mehrere FTS erforderlich werden, um die Daten einzulesen.
• das Splitting in row pieces startet am Ende der Spaltenliste: eine Tabelle mit 256 Spalten zerfällt also in ein erstes row piece mit einer Spalte und ein zweites row piece mit 255 Spalten. Da der Zugriff auf das zweite row piece kostspieliger ist als der auf das erste, ist das eine unglückliche Design-Entscheidung.
• im Fall von direct path tablescans führt inter-row-chaning zu einem db file sequential read-Zugriff, was sich sehr massiv auf die Performance auswirken kann.
• row chaining bringt außerdem hohe CPU-Kosten mist sich.

Vermutlich könnte man die Liste noch um weitere Punkte ergänzen.

"In-Memory" Postgres?

Vor einigen Tagen hat sich Josh Berkus mit der kontroversen Aussage "In-memory" is not a feature, it's a bug zu Wort gemeldet. Darin definiert er In-Memory Datenbanken als solche, denen die Möglichkeit fehlt, Daten auf die Festplatte zu schreiben, was folglich ihre Wiederherstellbarkeit einschränkt: "So an 'in-memory' database is a database with a major limitation." Das klingt erst mal polemisch und ist sicher auch so gemeint; möglicherweise trifft es auch nicht ganz die Grundidee von In-Memory Datenbanken, aber das sei erst einmal dahin gestellt. Interessant ist jedenfalls der im Artikel enthaltene Hinweis, dass man auch Postgres als "In-Memory" Datenbank betreiben kann, indem man alle Storage Features deaktiviert. Wie das funktioniert, erläutert er in einem Folgeartikel Running with scissors mode: grundsätzlich geht es dabei darum, Disk-Zugriffe so weit wie möglich zu vermeiden, wobei ein vollständiger Verzicht darauf auf diesem Weg nicht möglich ist. Zu den anzupassenden Parametern gehören work_mem, temp_buffers und diverse Einstellungen, die die WAL-Archivierung betreffen. Über temp_file_limit=0 lässt sich erreichen, dass Queries abbrechen, statt Sortierungen auf der Platte auszuführen. Darüber hinaus ist es wichtig in der postgresql.conf einen irregulären Dummy-Parameter (z.B. DO_NOT_RESTART=true) zu ergänzen, um den Neustart nach einem Crash zu verhindern, weil ein Crash in diesem Betriebsmodus die Datenbank notwendigerweise in einen inkonsistenten Zustand bringt. Das alles klingt in der Tat etwas wackelig, aber in einem weiteren Artikel will der Autor etwas zur Performance einer solchen Datenbank sagen.

Man mag dieses Verfahren als In-Memory-Variante zu Postgres bezeichnen - aber es hat aus meiner Sicht nicht allzu viel Ähnlichkeit mit den komplexeren In-Memory-Features der Konkurrenz, die etwa bei Oracle oder im SQL Server in den jüngsten Releases eingeführt wurden.

Neue Features für Parallel Execution in 12c

Parallele Ausführungspläne lassen mich in der Regel sehr vorsichtig werden, weil ich dabei gerne entscheidende Details übersehe - aber damit bin ich vermutlich kein Einzelfall. "It’s easy to make mistakes, or overlook defects, when constructing parallel queries", schreibt Jonathan Lewis in seinem jüngsten Artikel Parallel Rownum, in dem er einen Fall vorstellt, in dem ein parallelisiertes Select - mit ergänzter Rownum-Spalte - auf eine Tabelle sowie ein im Rahmen der gleichen Query mit einem abweichenden Parallelisierungs-Degree ausgeführtes Insert in eine zweite Tabelle zu unerfreulichen Effekten führen. Einerseits bringt das Rownum Element die Notwendigkeit einer zwischenzeitlichen Serialisierung mit sich, die natürlich ein Bottleneck der Operation darstellen kann. Darüber hinaus führt der unterschiedliche Parallelisierungsgrad des Select- und des Insert-Teils dazu, dass zwei unterschiedliche DFO (= Data Flow Operation) Gruppen ins Spiel kommen und in Folge dessen mehr parallel slaves eingesetzt werden als erforderlich.

Ich habe das Beispiel in meiner 12.1.0.2-Test-Instanz durchgespielt und dabei ein anderes Verhalten beobachtet: dort trat nur ein DFO-Element auf (und auch sonst zeigte der Plan ein paar Abweichungen). Die Erklärung dazu hat Randolf Geist in seinem Kommentar zu meinem Kommentar geliefert: in 12c wurden diverse (potentielle) Verbesserungen für Parallele Operationen eingeführt und eine davon ist die "1 SLAVE" Verteilung, die dafür sorgt, dass nur ein DFO tree bei der Verarbeitung verwendet wird (und folglich die Zahl der verwendeten Slaves beschränkt bleibt). Möglicherweise hätte ich diese Antwort auch selbst finden können, wenn ich meine Blog-Listen in strengerer chronologischer Reihenfolge betrachten würde, denn zu den zum Thema der in 12c eingeführten neuen Features im Kontext der Parallel Execution hat Randolf Geist vor ein paar Tagen einen umfassenderen Artikel veröffentlicht. Dieser erläutert unter anderem folgende Punkte:

• die adaptive HYBRID HASH Verteilung, die es der runtime engine gestattet im Rahmen der Ausführung zu entscheiden, ob eine HASH (= gezielt an einzelne Slaves) oder BROADCAST (= an alle) Verteilung erfolgen soll. Diese Option erlaubt es, Probleme der data distribution skew zu reduzieren, über die Randolf gelegentlich geschrieben hatte (die Links und meine Exzerpte dazu finden sich hier).
• concurrent UNION ALL. Laut Dokumentation galt "Traditionally, set operators are processed in a sequential manner". Das ist nun offenbar anders. Zur Durchführung der parallelen Sub-Operationen dient ein PX SELECTOR.
• PQ_REPLICATE erlaubt es offenbar, einen parallelen FTS durch mehrere Slaves gleichzeitig ausführen zu lassen und die (identischen) Ergebnisse per BRODCAST weiterzuleiten. Das klingt erst mal seltsam, hat aber vermutlich seine Gründe.
• parallel FILTER: erlaubt die parallele Verarbeitung von Filter-Subqueries.
• 1 SLAVE distribution: dient, wie erwähnt, dazu, die Komplexität paralleler Verarbeitungsvorgänge überschaubar zu halten, indem nur ein DFO tree verwendet wird und die seriellen Schritte eines parallelen Plans von einem einzigen Slave eines Slave Sets durchgeführt werden.

Zu jedem dieser Punkte könnte man deutlich mehr (und wohl auch Plausibleres) sagen, aber das hat der Herr Geist ja schon getan. Irgendwann sollte ich dann auch noch dazu kommen, solche Details als aktives Wissen vorzuhalten, statt das vage Gefühl zu entwickeln, schon mal irgendwo etwas Ähnliches gelesen zu haben... - aber immerhin weiß ich, wo ich nachzuschauen habe, wenn ich mich detailliert über Parallel Execution informieren möchte.

Nachtrag 10.07.2015: in einem weiteren Artikel 12c Parallel Execution New Features: PX SELECTOR erläutert Randolf Geist den PX Selector genauer: "In pre-12c [...] serial parts get executed by the Query Coordinator itself, and the new PX SELECTOR changes that so that one of the PX slaves of a PX slave set is selected to execute that serial part. There is not much left to say about that functionality, except that it doesn't get used always - there are still plan shapes possible in 12c, depending on the SQL constructs used and combined, that show the pre-12c plan shape where the Query Coordinator executes the serial part." Darüber hinaus gibt es ein paar Detailinformationen zum Verhalten, die ich hier nicht wiedergebe (die aber - erwartungsgemäß - darauf hindeuten, dass das neue Feature grundsätzlich eine nützliche Ergänzung ist).

Material zu XPLAN_ASH

Randolf Geist hat vor kurzem eine neue Version (4.21) seines nützlichen XPLAN_ASH Tools veröffentlicht und dazu noch erläuterndes Material - insbesondere Video-Tutorials - geliefert:

• New Version Of XPLAN_ASH Utility - In-Memory Support: Informationen zur neuen Version, die insbesondere die CPU-Nutzung von In-Memory-Operationen ausweist.
• Video Tutorial: XPLAN_ASH Active Session History - Introduction: das Tutorial (18 min) stellt die zentralen Funktionen des Tools vor.
• Video Tutorial: XPLAN_ASH Active Session History - Part 2: Teil 2 (23 min) erläutert den Skript-Output detaillierter.
• Video Tutorial: XPLAN_ASH Active Session History - Part 3: Teil 3 (31 min) setzt die Erläuterung des Skript-Outputs fort.

Der aufmerksame Leser mag den Verdacht hegen, dass ich hier zwar verlinke, aber möglicherweise deshalb wenige Details liefere, weil ich die Tutorials gar nicht komplett gesehen habe. Der Verdacht wäre zur Zeit nicht unbegründet - aber ich gelobe, dieses Versäumnis bei nächster Gelegenheit nachzuholen; und hier möglicherweise auch noch Details nachzuliefern, wenn mir das sinnvoll erscheint.

Außerdem werde ich die Link-Liste nach Möglichkeit erweitern, wenn die angekündigten Fortsetzungen erscheinen.

SQLcl

Neues von SDSQL. Die Änderung, die als erstes auffällt, ist der neue Name SQLcl, der für sql command line steht. Keine ganz schlechte Idee, finde ich, denn SDSQL kam mir eher sperrig vor.

Aber auch inhaltlich gibt es mit der neusten Version des Tools wichtige Ergänzungen, über die Jeff Smith und Kris Rice informieren:

• info Funktion: ein erweitertes DESC, das nicht nur die Spaltendefinitionen, sondern auch Informationen zu den Indizes und Constraints einer Tabelle liefert. Mit info+ bekommt man weitere Details zu den Spaltenstatistiken. Eine sehr nützliche Hilfe: Tom Kyte hat vor vielen Jahren ein entsprechendes SQL-Skript veröffentlicht, das ich in angepasst Form sehr lange Zeit intensiv verwendet habe. Darüber hinaus erinnert mich die Funktion an die Informationen die psql für postgres auf Kommandozeile zur Verfügung stellt.
• history Funktion: liefert neben einer Befehlshistorie auch noch Informationen zur Laufzeit der Ausführung der Befehle. Auch das dürfte nützlich sein.

Wenn sich SQLcl weiterhin in diesem Tempo weiterentwickelt, wird es bald für mich zu einer ernsthaften Alternative zu sqlplus - aber das habe ich, glaube ich, schon in meinem letzten Beitrag zum Thema behauptet und inzwischen trotzdem weiterhin die klassische Variante verwendet...

Nachtrag 24.02.2015: Eine einführende Präsentation zum Thema hat der Herr Smith inzwischen auch noch ergänzt.

Nachtrag 13.04.2015: Eine weitere umfangreiche Einführung gibt's von Mike Smithers.

SQL Features in unterschiedlichen RDBMS

Eine sehr interessante Präsentation hat Markus Winand offenbar gerade auf der FOSDEM-Konferenz gehalten. Dafür spricht jedenfalls der Foliensatz dazu, den man auf seiner Webseite findet. Darin zeigt er, was seit SQL-92 alles an interessanten Features für SQL verfügbar wurde und liefert vor allem auch Informationen dazu, wann diese Möglichkeiten in den verbreitetsten RDBMS eingeführt wurden. Unter den vorgestellten Features sind:

• Lateral Join
• With clause (aka "subquery factoring" aka "common table expression (CTE)"; in postgres wird die CTE immer materialisiert, ein predicate pushdown ist also nicht möglich. Dafür erlaubt postgres DML-Operationen in der CTE, deren Ergebnisse via returning clause weitergegeben werden.
• Recursive with clause: zur Umsetzung hierarchischer Abfragen (wozu Oracle lange Zeit ausschließlich die connect by Syntax anbot).
• Filter clause: die in SQL-2003 angesprochene Möglichkeit, einen Filter für Gruppenfunktionen zu definieren - die bislang in keinem RDBMS realisiert ist. Markus Winand hat mich via Twitter darauf hingewiesen, dass ich seine Slides in dem Fall falsch interpretiert habe: in Postgres 9.4 ist Filter implementiert. Ein Beispiel dazu findet man bei Hubert Lubaczewski.
• Over mit Group by
• Over mit Order by
• Within group
• Fetch first
• Offset: wovon der Herr Winand offenbar gar nichts hält.
• Without overlaps: was offenbar in der Bereich der temporal features fällt.

Zu den meisten Punkten habe ich hier gelegentlich etwas geschrieben, spare mir aber die Verknüpfung. Möglicherweise sollte ich zu SQL-Syntax-Elementen mal ein zielgenaues Tag spendieren...