Verhalten von Index Splits

Wieder mal bin ich spät dran und exzerpiere relevante Artikel, kurz bevor sie aus dem 30-Tage-Korridor meines Blog Aggregators fallen. Und wieder mal ist es der zur Zeit ungeheuer produktive Jonathan Lewis, dessen Beiträge ich zusammenfasse. In den fraglichen Artikel beschäftigt sich der Herr Lewis mit dem Verhalten von "Index Splits", die auftreten, wenn ein neuer Index-Eintrag nicht mehr in den vorgesehenen Block der Index-Struktur passt:

• Index splits: erläutert den Unterschied zwischen dem 50:50-Split (den die Statistik "leaf node splits" erfasst) und dem 90:10-Split (erfasst unter "leaf node 90-10 splits"): letzterer tritt auf, wenn der neue Eintrag oberhalb aller bisher bekannten Werte liegt - und in diesem Fall wird nur der neue Eintrag in einen eigenen Block geschrieben (weshalb der Split eher als 100:0 zu bezeichnen wäre). Theoretisch könnte der zweite Fall (also der Sonderfall) effizienter sein als er erste - er ist es aber nicht, wie der im Artikel vorgestellte Test zeigt: die Erzeugung von undo und redo sind in beiden Fällen nahezu identisch. Die Ursache dafür ist, dass beide Fälle intern gleich behandelt werden: der alte Block wird gesichert, ein neuer Block ergänzt und dann werden beide Blocks gefüllt. Die Ursache dafür ist wahrscheinlich, dass die Entwickler bei Oracle den Code nicht komplizierter machen wollten als notwendig, obwohl hier vermutlich ein gewisses Optimierungspotential existieren würde.
• Index Splits – 2: liefert einen Test-Fall, der eine Erklärung für das im ersten Artikel beobachtete Verhalten liefert: die dabei durchgeführte Änderung am Index ändert das row directory des Blocks. Demnach ändert sich zwar nicht der Inhalt, aber die Sortierung des Blocks - und lässt das vollständige Schreiben beider Blocks sinnvoll erscheinen.
• Index Splits – 3: liefert ergänzendes Material.

Column Groups und NULL Werte

Bereits vor einigen Wochen hat Jonathan Lewis auf eine Problem hingewiesen, das sich bei Verwendung von Column Groups ergeben kann: wenn der Anteil der NULL-Werte in einer Spalte sehr groß ist, dann ist das für den Optimizer über die NUM_NULLS-Information erkennbar. Da Column Groups intern über eine virtuelle Spalte abgebildet werden, die die Informationen zu den in der Gruppe enthaltenen Spalten zusammenfasst, verliert die NUM_NULLS-Information auf dieser Ebene ihre Aussagekraft, da die Spaltenkombination insgesamt nicht mehr als NULL betrachtet werden kann. Dadurch ergeben sich im Beispiel extreme Fehleinschätzungen bei der Cardinality.

Join Cardinality und Histogramme

Jonathan Lewis hat zuletzt eine Artikelserie veröffentlicht, die sich damit beschäftigen, wie sich die Existenz unterschiedlicher Histogramm-Typen auf die Bestimmung der Join-Cardinality auswirkt. Seine Überlegungen basieren dabei auf einem umfangreicheren Artikel von Chinar Aliyev aus dem Jahr 2016. Da der Herr Lewis da natürlich sehr viele Details beleuchtet, verzichte ich weitgehend auf die Zusammenfassung und beschränke mich auf die Erfassung der Links, um die Hoffnung zu erhalten, sie bei Bedarf wiederfinden zu können:

• https://jonathanlewis.wordpress.com/2018/10/03/join-cardinality/: betrachtet das Verhalten mit frequency histograms.
• https://jonathanlewis.wordpress.com/2018/10/05/join-cardinality-2/: weitere Aussagen zu vergleichsweise übersichtlichen Fällen mit frequency histograms.
• https://jonathanlewis.wordpress.com/2018/10/09/join-cardinality-3/: betrachtet Fälle aus dem Bereich frequency/top-frequency histograms.
• https://jonathanlewis.wordpress.com/2018/10/25/join-cardinality-4/: beschäftigt sich mit der Kombination frequency/hybrid histograms.
• https://jonathanlewis.wordpress.com/2018/11/01/join-cardinality-5/: behandelt das Verhalten mit height-balanced histograms.

Wahrscheinlich wird die Artikelserie noch umfangreicher - und vielleicht erweitere ich dann auch die vorliegende Liste.

DAX Studio für Microsoft BI

Zwar sind die Zeiten, in denen ich mich mit Microsofts BI-Landschaft intensiver beschäftigt habe, schon lange vorbei, aber sollte ich jemals wieder damit anfangen, dann wäre das DAX Studio sicher ein Werkzeug, das zu berücksichtigen wäre. Vincent Rainardi verweist in seinem zugehörigen Artikel auch auf eine nützliche Liste weiterer BI Tools, die unter https://www.sqlbi.com/tools/ gepflegt wird. Ich erinnere mich noch gut daran, wie ich nach der Abwanderung von Mosha Pasumansky auf eine Weiterentwicklung des ungeheuer nützlichen MDX Studios hoffte, aber leider ist daraus offenbar nie etwas geworden.

Indizierung von Foreign Keys in Oracle und Postgres

Dass nicht indizierte Foreign Keys in Oracle massive Locking-Probleme hervorrufen können, habe ich vermutlich zum ersten Mal vor mehr als 15 Jahren bei Tom Kyte gelesen (ohne dass ich dazu gerade eine passende Textstelle liefern könnte). Franck Pachot hat jetzt darüber geschrieben, wie das Verhalten unter entsprechenden Bedingungen in Postgres aussieht - eine Frage, die eigentlich auf der Hand liegt, der nachzugehen mir bisher aber noch nicht in den Sinn gekommen war. Das Ergebnis lautet: bei Postgres ist die Indizierung der Foreign Keys nicht zur Vermeidung von Locks erforderlich, sondern nur zur Optimierung der Navigation von Parent zu Child. Grundlage dieses Verhaltens ist die Verfügbarkeit von "shared row locks" in Postgres (die es in Oracle nicht gibt). Die Details der Erklärung erzähle ich hier nicht nach - mir genügt die Erinnerung, dass beide RDBMS sich in diesem Punkt unterschiedlich verhalten.

Transport von SQL Patches

Marco Mischke erläutert in seinem Blog, dass "SQL Patches" bei einer Migration mit Hilfe von "data pump" nicht transportiert werden, da sie nicht zum Schema der Applikation gehören. Das Package DBMS_SQLDIAG enthält aber Prozeduren, mit deren Hilfe man die Patches in einer Tabelle ablegen kann, die sich dann ihrerseits mit "data pump" transportieren lässt. Das ist also kein besonderes Hindernis, aber ein Punkt, den man im Rahmen einer Migration im Auge behalten muss.

Performance von lokalen und globalen Indizes partitionierter Tabellen

Richard Foote hat eine neue Artikel-Serie gestartet, in der er sich mit der Performance lokaler und globaler Indizes für partitionierte Tabellen beschäftigt. Da ich auch zu den Leute gehöre, die globale Indizes weitgehend vermeiden, finde ich Argumente, die für die globalen Indizes sprechen, grundsätzlich interessant:

• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie): liefert ein sehr einfaches initiales Beispiel: eine Tabelle enthält Daten für acht Jahre und einen Index für die Spalte TOTAL_SALES, die für den Wert 42 insgesamt 10 Datensätze liefert. Bei Verwendung einer nicht partitionierten Tabelle erfordert ein Zugriff auf diese 10 Datensätze 14 consistent gets (4 für den Durchgang durch die Indexstruktur und 10 für die Datensätze) - und die gleichen 14 LIOs sind auch erforderlich, wenn man nur einen Datensatz für ein bestimmtes Jahr auswählt, da die Datumsangabe nicht Teil des Index ist und daher erst bei der Filterung berücksichtigt wird. Für den globalen Index auf einer partitionierten Tabelle ergeben sich die gleichen 14 LIOs für Fall 1, aber für den zweiten Fall sinkt die Zahl der LIOs auf 5, da hier für den Optimizer klar ist, dass nur die Partition des fraglichen Jahres den gewünschten Datensatz enthalten kann - es erfolgt also ein "partition pruning".
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane): bis zu Oracle 8 bestand die rowid grundsätzlich aus 6 byte und enthielt "file number", "block number" und "row number". Seit der Einführung des Partitioning in Oracle 8 kann ein partitioniertes Objekt in mehreren Tablespaces abgelegt werden, was dazu führte, dass die "file number" nicht mehr eindeutig war. Daher wurde die rowid für globale Indizes um die "data object id" erweitert (und damit auf 10 byte erweitert). Diese zusätzliche Information macht das "partition pruning" möglich, da der Index in jedem einzelnen Schlüssel die dafür erforderliche Information enthält.
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet): weist darauf hin, dass ein lokaler Index durch die kürzere rowid (ohne die "data object id" und daher nur 6 byte lang) etwas kleiner ist als ein entsprechender globaler Index. Für einen Zugriff, der sich auf eine Partition beschränkt, ist der lokale Index daher etwas effizienter als der globale Index (4 vs. 5 LIOs). Beim Partitions-übergreifenden Zugriff ist der globale Index dagegen deutlich effizienter, da sich das Lesen auf eine Index-Struktur beschränkt und nicht über mehrere interne Indizes erfolgen muss.
• “Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part IV” (Hallo Spaceboy) : zeigt, dass man das BLEVEL eines globalen Index dadurch reduzieren kann, dass man ihn partitioniert (nach einer anderen Spalte als dem partition key).

Wie üblich gilt, dass ich die folgenden Artikel ergänzen werde, wenn ich nicht die Lust daran verliere.

Overhead für Extended Trace im SQL Server

Da ich ein großer Freund des Tracings von Datenbank-Operationen bin, finde ich Untersuchungen wie die von Nenad Noveljic zum Thema "SQL Server Extended Events Trace Overhead" sehr interessant. Im Artikel wird ein Testszenario vorgestellt, das eine relativ zweckfreie Schleife zunächst ohne und dann mit dem Event "sql_batch_completed" gegenüberstellt. Für diese stark CPU-hungrige Operation ergibt sich eine Verlangsamung um ca. 15% nach der Aktivierung des Events: die Laufzeit steigt von 61 auf 74 Sekunden - wobei die Erhöhung auf CPU-Last zurückzuführen ist, die nicht in den Event-Statistiken enthalten ist (sondern in sys.dm_exec_requests.cpu_time). Andere Events - wie etwa logout - haben nahezu keine Auswirkungen auf die Performance. Ein entsprechender Test mit Oracle und einem 10046er Trace zeigt keine entsprechende Wirkung - wobei allerdings anzumerken wäre, dass dort ein Trace mit rowsource statistics unter Umständen recht massive negative Effekte hervorrufen kann, wie Jonathan Lewis gerade mal wieder in Erinnerung gerufen hat.

ORA-01723 für DATE-Angaben

Nach langer Zeit mal wieder etwas Selbsterlebtes, das einen Eintrag zu verdienen scheint - wie man eine CTAS-Operation über das nls_date_format torpedieren kann:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> create table t as select to_date(null) col1 from dual;
create table t as select to_date(null) col1 from dual
                         *
ERROR at line 1:
ORA-01723: zero-length columns are not allowed

SQL> alter session set nls_date_format = 'DD-MON-RR';

SQL> create table t as select to_date(null) col1 from dual;

Table created.

Somit wird die Tabelle T mit dem einen nls_date_format problemlos angelegt, während das andere Format einen Fehler "ORA-01723: zero-length columns are not allowed" hervorruft. Ist das ein Problem? Vermutlich nicht: sinnvoller wäre hier ohnehin der Einsatz eines CAST-Aufrufs, denn die folgende Variante funktioniert in beiden Fällen:

SQL> create table t as select cast(null as date) col1 from dual;

Detailinformationen zu dbms_stats in 12c

Jonathan Lewis schreibt in seinem jüngsten Blog-Artikel über einige nützliche Funktionen, die zu dbms_stats in 12c ergänzt wurden:

• report_stats_operations: liefert Basisinformationen zu den Erfassungsläufen der letzten n Tage, etwa die Start- und End-Zeit und die Anzahl erfolgreicher und fehlgeschlagener Tasks. Leider ist eine Filterung der Angaben nicht über Parameter möglich, sondern muss durch einen Pl/SQL Wrapper erledigt werden.
• report_single_stats_operation: liefert Details zu einem der von der ersten Funktion gelieferten Tasks. Über das detail_level ALL Kann man dann auch die Ursache für eine fehlschlagende Erfassung finden.

So nützlich diese Informationen sind, würde ich mir doch eher entsprechende Dictionary Views wünschen, da die Funktionsausgaben keine weitere Bearbeitung erlauben - etwa, um die Laufzeit einer Operation aus Start- und Endzeit zu ermitteln. Aber ein nützliches Hilfsmittel sind die Funktionen in jedem Fall.

Postgres: pg_dump blockiert DDL-Kommandos

Daniel Westermann untersucht im DBI Blog das Verhalten von DDL-Operationen im Rahmen von pg_dump. Dabei zitiert er die Postgres-Dokumentation, in der zu lesen ist:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

Den Ergebnissen der Untersuchung nach ist das aber nicht uneingeschränkt zutreffend: während DML tatsächlich problemlos ablaufen kann, werden DDL-Kommandos offenbar während der Dump-Erstellung blockiert. Laut source code verwendet pg_dump das isolation level "repeatable read" und setzt ein AccessShareLock während der Sicherung. Dieses erlaubt Datenänderungen und auch die Ergänzung on Objekten (etwa Indizes), nicht aber die strukturelle Änderung bereits bestehender Objekte (create index, drop table, truncate table): diese Operationen bleiben bis zum Abschluss der Dump-Erstellung blockiert. Insofern ist die Dump-Erstellung zu Zeitpunkten, in denen keine DDL-Operationen zu erwarten sind, sicher eine sinnvolle Herangehensweise.

Ergänzung im Oracle 12c Scheduler: EXTERNAL_SCRIPTS

Sven Weller erläutert in seinem Blog die Verwendung des Job-Typs EXTERNAL_SCRIPTS, mit dem man - nun ja: externe Skripte ausführen lassen kann: also etwa cmd-Skripte unter Windows und sh-Skripte unter Linux. Das konnte man über den (weiterhin verfügbaren) Typ EXECUTABLE auch schon in älteren Versionen, aber der neue Mechanismus scheint deutlich handlicher zu sein, besitzt eine solide Fehlerbehandlung und setzt nicht voraus, dass das auszuführende Skript lokal auf dem Server vorliegt. Sollte ich mal wieder einen guten Grund finden, ein Skript vom Scheduler ausführen zu lassen, wäre das offenbar der Weg zur Umsetzung.

Semantik der Informationen im SQL Monitor

Mohamed Houri untersucht in einer kleinen Artikelserie die Semantik der Angaben des SQL Monitor:

• ASH, SQL Monitor and Extended trace: untersucht die Angaben zur Fetch-Phase durch Vergleich mit den Aussagen von ASH und SQL Trace und stellt fest, dass diese Phase in der "Elapsed Time" Angabe des "Global Stats" Abschnitts nicht berücksichtigt wird. Eine plausiblere Aussage hinsichtlich der vollständigen Laufzeit liefert "Duration" im Abschnitt "Global Information", was bereits Nenad Noveljic in einem grundlegenden Artikel erläutert hatte.
• SQL Monitor and parsing: betrachtet die Behandlung des Parsings und weist darauf hin, dass nur die Execution Phase im SQL Monitor berücksichtigt wird. Wiederum gilt, dass ein zeitaufwändiges Parsing nur über die "Duration" zu beobachten wäre.

Sollten weitere Artikel folgen, versuche ich, sie zu ergänzen - wobei meine Erfolgsquote bei der Umsetzung solcher Absichtserklärungen zuletzt deprimierend niedrig gewesen ist...

Optische Änderungen

Das Design von Webseiten ist, wie gelegentlichen Besuchern dieses Blogs klar sein dürfte, nicht meine Kernkompetenz. Da ich hier nur noch in Ausnahmefällen Code unterbringe, bin ich jetzt auf ein Blogger-Standard-Design gewechselt. Ich hoffe, die neue Variante stört das ästhetische Empfinden der Besucher zumindest nicht mehr als die vorherige.

ORDER BY in CTEs garantiert keine Sortierung

Dass der einzige Weg, eine Sortierung für Oracle-Queries zu garantieren, die Ergänzung einer ORDER BY Klausel ist, gehört zu den Dingen auf die Tom Kyte regelmäßig hinzuweisen pflegte. In einem aktuellen Artikel zeigt Franck Pachot, dass man sich auch nicht darauf verlassen kann, dass die in einer CTE definierte Sortierung eine entsprechende Sortierung der rahmenden Query bedingt. Seit 12.2 können zur Materialisierung von CTEs "In-Memory Cursor Duration Temp Tables" verwendet werden (die im Exceution Plan mit einem Eintrag "LOAD AS SELECT (CURSOR DURATION MEMORY)" dargestellt werden), und diese Funktionalität kann die Sortierung ausschalten. Vermeiden kann man dieses Feature entweder durch seine Deaktivierung (_in_memory_cdt=off) oder durch Vermeidung der Materialisierung (inline), aber letztlich ist das Verhalten durchaus nicht unschlüssig, da die Sortierung auf globaler Query-Ebene eben nur durch ein dort definiertes ORDER BY garantiert wird.

Clustering Faktor und Index Rebuild

Jonathan Lewis liefert im Scratchpad einen interessanten Hinweis: wenn man für einen Index über dbms_stats.set_table_prefs einen geeigneten table_cached_blocks Wert setzt, um den Clustering Faktor plausibler zu gestalten, dann wirkt sich die Präferenz zwar auf folgende Aufrufe von dbms_stats aus, nicht aber auf ein "alter index ... rebuild". Nach einem Neuaufbau kann der Clustering Faktor somit zunächst massiv in die Irre führen, bis er von einer folgenden Statistikerfassung wieder korrigiert wird, was zu schwer nachvollziehbaren Effekten führen könnte.

Nachtrag 17.07.2017: mit Verweis auf den Scratchpad-Artikel hat Richard Foote einen weiteren Beitrag zum Thema geliefert, der das Verhalten mit eingängigen Beispielen erläutert.

dbms_random zur Generierung (ziemlich) eindeutiger Werte

Jonathan Lewis weist in seinem jüngsten Artikel darauf hin, dass man die Länge von Strings, die man per dbms_random.string('U', n) generiert, mit Bedacht wählen sollte: bereits ein relativ niedriger Wert für liefert eine sehr große Zahl unterschiedlicher Permutationen: für 6 Zeichen sind es bereits über 300 Millionen Kombinationen, so dass sich daraus für den im Artikel (und im zugrunde liegenden OTN-Fall) für eine 100M rows Tabelle ein Wert mit sehr seltenen Wiederholungen ergeben würde. Bei 8 Zeichen ist die Wahrscheinlichkeit der Wiederholung dann schon sehr gering. Umgekehrt ist die Erstellung kürzerer dbms_random-Strings weitaus günstiger: für 20 Zeichen ergeben sich im vorgestellten Beispiel 1m 55 sec, während der String mit 6 Zeichen (und mit per rpad() angehängtem beliebigen Zeichen) in 41 sec erzeugt werden konnte. Die Ursache für diesen Unterschied ist, dass Oracle jedes einzelne Zeichen durch die Zufallsfunktion erzeugt, so dass ein um Faktor 3 längerer String auch in etwa eine um Faktor 3 erhöhte CPU-Nutzung bedeutet. Wo kürzere Strings genügen, kann man dem System demnach einige Arbeit sparen.

Neue Oracle VM Appliance

Nur damit ich es irgendwo verlinkt habe: Jeff Smith weist darauf hin, dass im OTN eine neue VM mit Oracle 12.2, SQL Developer 18.1, Oracle REST Data Services 18.1 etc. zur Verfügung steht.

Index Skip Scan bei führender Spalte mit vielen unterschiedlichen Werten

Ja, ich habe schon weniger sperrige Titel für meine Einträge verwendet. Leider ist mir nichts Griffigeres eingefallen - und vor allem nichts, was dann noch zum Sachverhalt passen würde: Jonathan Lewis zeigt, dass der INDEX SKIP SCAN manchmal an Stellen auftreten kann, an denen man ihn nicht erwarten würde. Wo würde man ihn also erwarten? Dort, wo ein mehrspaltiger Index existiert, dessen führende Spalte (oder Spalten) wenige distinkte Werte enthält, so dass es für den Optimizer sinnvoll erscheint, den Index intern quasi in viele Sub-Indizes zerfallen zu lassen. In seinem Artikel liefert der Autor jetzt ein Beispiel mit einem Index, der auf zwei Spalten angelegt ist, die jeweils unique sind - so dass der skip scan hier recht seltsam erscheint. Ursache ist eine Änderung, die mit 11.2.0.2 eingeführt wurde: die I/O-Kosten eines INDEX SKIP SCAN sind seither auf die Anzahl der Leaf Blocks des Index beschränkt, was für einen relativ kleinen Index dann den skip scan interessanter macht als den Full Table Scan. Allerdings wäre der FTS in einem solchen Fall wahrscheinlich effizienter als der skip scan, da dieser den Index in sortierter Ordnung via single block I/O lesen muss, während jener bekanntlich multi block I/O verwenden kann. Noch günstiger wäre in einem solchen Fall die Kombination aus INDEX FAST FULL SCAN und folgendem Tabellenzugriff, aber dazu ist der Optimizer bisher noch nicht in der Lage. Viel Freude hat mir der INDEX SKIP SCAN noch nie gemacht, und diese Costing-Anpassung erklärt, warum man ihn auch an unerwarteten Orten antreffen kann.

Dynamische Linesize-Einstellung für sqlplus

Die Welt hat vielleicht nicht darauf gewartet, ich aber ganz gewiß: wie Laurent Schneider erläutert, gibt es mit Oracle 18.1 eine dynamische linesize für sqlplus. Diese orientiert sich an der Größe des Shell-Fensters. Hätte ich diese Option von 15 Jahren bekommen, hätte ich dadurch in Summe mehrere Wochen einsparen können, nehme ich an.

Keine Empfehlung mehr für System Statistics

Die Einführung der "System Statistics" liegt schon einige Jahre zurück, aber die ihnen zugrunde liegende Idee einer Kalibrierung der verfügbaren CPU- und I/O-Ressourcen fand ich damals durchaus einleuchtend. Tatsächlich eingesetzt habe ich sie selten - und das scheint inzwischen kaum noch jemand zu tun. Und zukünftig wird es wohl noch seltener vorkommen, nachdem Maria Colgan und Nigel Bayliss davon abraten. Bei Frau Colgan liest man:

Don’t gather system statistics unless you are in a pure data warehouse environment, with a good IO subsystem (e.g. Exadata) and you want to encourage the Optimizer to pick more full table scans and never says never!

Und der Herr Bayliss erklärt:

if you are at a decision point and you need to choose whether to gather them or not, then in most cases you should use the defaults and not gather system statistics.

Der Artikel von Nigel Bayliss erläutert auch noch mal genauer, was System Statistics eigentlich sind, wann sie eingeführt wurden (2001 mit 9i) und welche Empfehlungen dazu zu früheren Zeitpunkten gegeben wurden. Das Fazit lautet:

You might find that it is better to free yourself from managing system statistics and, instead, use the tools that Oracle provides you with to tune the queries that are not performing as well as you want.

Insofern gehört dieses Feature inzwischen offenbar zu den historischen.

Nachtrag 18.04.2018: in einem weiteren Artikel weist Maria Colgan darauf hin, dass die "Fixed Object Statistics" der x$-Objekte seit 12.1 im Rahmen der automatischen Statistikerfassung erzeugt werden. Allerdings werden sie nicht automatisch aktualisiert, so dass sie im Fall massiver Änderungen an Datenbank und Applikation manuell neu erzeugt werden sollten.

Histogramm-Probleme mit EXP/IMP in Oracle 12

Jonathan Lewis weist darauf hin, dass die historischen Utilities exp und imp mit 12c neue Probleme bekommen haben: der Code der Tools wurde nicht dahingehend angepasst, dass sie mit den neuen Histogrammtypen "Hybrid" und "Top-N" zurecht kämen. Wenn man aus einem Quellsystem exportiert, in dem bereits ein "Hyrid Histogram" exisitiert, so wird dieses nach dem Import im Zielsystem zum historischen "Frequency Histogram" - das noch nie viel getaugt hat. Grundsätzlich sollte natürlich in diesen Tagen niemand mehr mit exp und imp hantieren, aber manchmal passiert das ja doch noch.

Potentielle Performance-Probleme bei match_recognize

Jonathan Lewis hat vor einiger Zeit zwei Artikel zum match_recognize geschrieben, die auf potentielle Probleme hinweisen, die das Feature mit sich bringt, wenn man die Match-Bedingung nicht mit ausreichender Sorgfalt definiert. Leider gehört match_recognize noch immer nicht zu meinem SQL-Repertoire (jedenfalls nicht ohne dass ich mir irgendwo ein funktionierendes Beispiel ausborge und darin dann anpasse, bis es meinen Anforderungen entspricht), aber die Hinweise finde ich hier möglicherweise besser wieder als beim Herrn Lewis:

• Match_recognize: erläutert das grundsätzliche Verhalten anhand eines Beispiel, in dem aus einer Menge die Kunden gesucht werden, die im September, nicht aber im Oktober eingekauft haben. Auf der Basis einer Menge mit 100K rows für September und einem Datensatz für Oktober und mit sehr breiten Datensätzen ergeben sich zwei Beobachtungen:
• es ergeben sich unter Umständen sehr große Sortierungen (Anzahl Input rows * Gesamtlänge des Outputs der Spalten aus "partition by" und "measures").
• wenn am Ende einer langen Sequenz von rows der letzte Datensatz nicht gematcht werden kann, geht Oracle zurück zum ersten Datensatz nach dem Start des vorangehenden Match-Versuchs. Stew Ashton weist in einem Kommentar darauf hin, dass das Problem noch größer ist, und mit diesem Hinweis beschäftigt sich der zweite Artikel zum Thema.
• Match_recognise – 2: führt Stew Ashtons Hinweis weiter aus: in der ursprünglichen Version führt die Klausel nicht zu einem einmaligen Rücksprung, sondern zu einer iterativen Wiederholung, die bald zu extremen Laufzeiten führt. Wenn man die pattern-Klausel um ein vorangestelltes caret-Symbol (also: ^) erweitert, ändert man die Anforderung so, dass das Pattern mit der ersten row der Partition beginnen muss, was die Laufzeit im Beispiel von 250 auf weniger als eine Sekunde reduziert, weil damit die Wiederholung entfällt.

Sollte der Leser an dieser Stelle den Eindruck gewonnen haben, dass ich nicht so ganz verstanden habe, was ich da zusammenfasse, dann kann ich versichern: ich habe nicht ganz verstanden, was ich da zusammenfasse. Aber ich hoffe, bei erneuter Auseinandersetzung mit dem Thema allmählich dahinter zu kommen.

Local Partitioned Indexes mit postgres 11

Daniel Westermann weist darauf hin, das postgres 11 erweiterte Optionen für die Indizierung partitionierter Tabellen liefern. Während in postgres 10 Indizes noch auf Partitionsebene erzeugt werden mussten, kann man sie jetzt für die partitionierte Tabelle definieren, was dazu führt, dass sie automatisch in den untergeordneten Partitionstabellen erzeugt werden. Vielleicht noch interessanter ist die Möglichkeit, primary keys auf partitionierten Tabellen zu erzeugen. Insgesamt ist deutlich zu erkennen, dass die Partitionierung in postgres allmählich ihre Kinderkrankheiten hinter sich lässt.

Nachtrag 26.03.2018: zu den Ergänzungen gehört auch das row movement - also die Verschiebung eines Datensatzes in eine andere Partition bei Änderung des Werts für den partition key. Auch dazu hat der Herr Westermann einen Artikel geschrieben.

Nachtrag 04.04.2018: und noch etwas, das in postgres 11 funktioniert: die postgres-Variante zum Merge-Statement - das "Insert ... on conflict".  Auch dazu hat der Herr Westermann einen Artikel geliefert.

Postgres: GIN-Indizes und Vacuum

Hans-Jüregen Schönig erläutert in seinem Blog die Rolle der "GIN Pending list" für die Performance von GIN-Indizes. Dazu erläutert er zunächst den Aufbau von GIN-Indizes: diese Indizes bestehen aus einem normalen B*Tree-Index, an den ergänzend ein "posting tree" angehängt ist. Der B*Tree-Index enthält auf Leaf-Ebene einen Eintrag für jedes indizierte Wort, aber die Zuordnung der Wörter zu den pages der zugehörigen Tabelle wird in eine weitere Struktur ausgelagert. Die Verweise in dieser zusätzlichen "posting tree" Struktur werden sortiert gespeichert, so dass eine Anpassung der Struktur potentiell teuer sein kann. Um diese Kosten zu verteilen, werden neue oder geänderte Einträge nicht unmittelbar an die richtige Stelle gebracht, sondern in eine "GIN pending list" ergänzt, die zusätzlich zur Indexstruktur sequentiell gelesen werden muss. Die Zusammenführung von "posting tree" und "pending list" ist Aufgabe des vacuum-Prozesses. Somit können massive DML-Operationen einen signifikanten Einfluss auf die Performance nachfolgender Zugriffe haben, die erfolgen, ehe der nächste Vacuum-Lauf wieder für Ordnung sorgt.

Ergänzungen zu coalesce und NVL

Vor längerer Zeit hatte ich hier gelegentlich auf Artikel verwiesen, die sich mit dem unterschiedlichen Verhalten von NVL und coalesce beschäftigten und einerseits auf die short-circuit evaluation mit coalesce und andererseits auf deren Ausklammerung im Fall von Sequencen hinwiesen. Jetzt haben die Herren Lewis und McDonald dazu ergänzende Beobachtungen geliefert.

• Jonathan Lewis weist darauf hin, dass coalesce beim costing schlechter abschneidet als NVL, weil es mit dem Standardwert von 1% für Gleichheit operiert, der für viele Funktionsaufrufe verwendet wird. NVL hingegen kann vorhandene Histogramme sinnvoll einsetzen.
• Connor McDonald zeigt eine nette Optimierung für Queries mit Bindevariablen der Form coalesce bzw. NVL(:search_criteria, column) = column. In diesem Fall kann NVL die beiden Fälle in zwei Varianten aufsplitten, während coalesce immer einen Full Table Scan einsetzt.

Wie üblich hängt die Auswahl der passenden Funktion also von den Umständen ab.

Artikel zu redo internals von Frits Hoogland

Um sie leichter wiederzufinden, verlinke ich hier eine Liste der Artikel, die Frits Hoogland in seiner Serie "A look into Oracle redo" veröffentlicht. Vermutlich werde ich hier wenig Inhaltliches ergänzen, da sich diese Artikel nur schwer exzerpieren lassen: sie enthalten einfach zu viele interessante technische Details:

• A look into Oracle redo, part 1: redo allocation latches: der log buffer enthält mindestens zwei public redo strands, die über x$kcrfstrand dargestellt werden. Die Anzahl wird über den internen Parameter _log_parallelism_max gesteuert: Systeme mit einer höheren CPU-Anzahl könnten unter Umständen von einer Erhöhung des Wertes profitieren. Jeder Strand wird von einem "redo allocation latch" geschützt. Neben den public strands gibt es private strands, die ebenfalls von solchen Latches geschützt werden (so dass statt 2 insgesamt 20 Latches sichtbar sind). Es folgt eine intensivere Untersuchung der latch Verwendung unter Verwendung elaborierterer Tools. Sichtbar wird, dass im Fall einer Verwendung aller vorhandenen Latches eine folgende Anforderung zunächst ein spinning im willing to wait Modus durchführt und anschließend in den sleep Modus mit semaphore (über den eine Benachrichtigung erfolgt, wenn das Latch wieder verfügbar wird) übergeht (was Andrey Nikolaev, den ich hier auch gelegentlich irgendwo verlinkt habe, umfassend dargestellt hat). Um den Zusammenhang von redo allocation latch und verwendetem public strand zu bestimmen, muss man aber noch tiefer graben und die memory Nutzung des Prozesses untersuchen. Hier wird dann erläutert, welche Funktion im einzelnen intern aufgerufen werden, aber spätestens hier ist die Nacherzählung nicht mehr hilfreich.
• A look into Oracle redo, part 2: the discovery of the KCRFA structure: geht noch tiefer in die technischen Details der Implementierung und den Aufbau der zugehörigen Memory-Strukturen ein, was ebenso interessant wie schwer exzerpierbar ist.
• A look into Oracle redo, part 3: log writer work cycle overview: beschäftigt sich mit dem Log Writer und seinen Aufgaben. Dazu ist die Dokumentation offenbar nicht allzu ausführlich. Gut sichtbar ist, dass der Log Writer in den log buffer schreibt, was über das Wait Event "log file parallel write" abgebildet wird, und dass seine sleeps über das Event "rdbms ipc message" ausgewiesen werden. Mit debugtrace kann man die zugrunde liegenden Aufrufe, die Integration der Instrumentierung und den Zyklus der Operationen des Prozesses erkennen.
• A look into Oracle redo, part 4: the log writer null write
• A look into Oracle redo, part 5: the log writer writing
• A look into Oracle redo, part 6: oracle post-wait commit and the on disk SCN
• A look into oracle redo, part 7: adaptive log file sync
• A look into oracle redo, part 8: generate redo 
• A look into oracle redo, part 9: commit 
• A look into oracle redo, part 10: commit_wait and commit_logging

Hoffentlich liefere ich gelegentlich ein paar Sätze zu den unkommentierten Artikeln nach, aber leider schreibt der Herr Hogland deutlich schneller, als ich exzerpieren kann...

Anzeigeoptionen für dbms_xplan

Noch ein Verweis auf einen Artikel von Franck Pachot, in dem er Dokumentationslücken zu Oracle schliesst. In diesem Fall erläutert er die Format-Optionen zu dbms_xplan, die ich mit einer gewissen Regelmäßigkeit nachschlagen muss - was durch diesen Link möglicherweise vereinfacht wird.

Erläuterungen zum Result Cache

Franck Pachot liefert in seinem jüngsten Artikel etwas, das die Oracle Dokumentation unterschlägt: eine detaillierte Erläuterung zur Funktionsweise des Result Cache und den Voraussetzungen, die für seine Verwendung vorliegen sollten. Im Artikel wird unter anderem auf folgende Punkte hingewiesen:

• DML invalidiert den result cache
• ein "cache miss" aufgrund der Invalidierung ist kostspielig
• ein "cache miss" ist auch dann teuer, wenn ein gesuchtes Ergebnisnicht im Cache vorliegt
• die Kosten der Operation ergeben sich aufgrund von contention, sind also im single user Zugriff nicht sichtbar

Verantwortlich für diese Effekte ist, dass der Result Cache, durch ein einzelnes Latch geschützt wird: konkurrierende Zugriffe müssen folglich serialisiert werden. Daher ist der Result Cache nur dann nützlich, wenn eine Funktion häufig mit den gleichen Parametern aufgerufen wird und wenn sich keine massiven DML-Änderungen ergeben. Immerhin ist für rein lesende Zugriffe seit 11.2 die Verwendung eines Latch-Zugriffs im "shared mode" eingerichtet - Schreiboperationen nutzen aber immer einen exklusiven Zugriff und führen zu Invalidierungen. Zum Beleg für diese Aussagen liefert der Artikel die zugehörigen Test-Szenarien und ergänzt abschließend noch folgende Beobachtungen:

• jeder "cache miss" erfordert mehrere exklusive Zugriffe auf den Result Cache
• der Cache ist daher nur dann nützlich, wenn die Inhalte weitgehend statisch sind

Ausgehend von den Beobachtungen des Herrn Pachot ist dieses Fazit unmittelbar einleuchtend. Es wäre aber nett, wenn solche Hinweise nicht durch die archäologischen Anstrengungen interessierter Nutzer ausgegraben werden müssten, sondern durch den Hersteller der Software bereitgestellt werden würden. Aber das ist natürlich nicht Neues in diesem Kontext.

Full Outer Join Strategien in unterschiedlichen RDBMS

Für Leute, die parallel mit mehreren RDBMS zu arbeiten haben - so wie ich -, ist es wichtig, die kleinen Unterschiede in der Implentierung im Blick zu behalten. Dabei sind Artikel wie die Untersuchung "NESTED LOOP and full/right outer join in modern RDBMS" von Mohamed Houri ausgesprochen nützlich. Darin erfährt man unter anderem Folgendes:

• weder Oracle, noch der SQL Server, noch Postgres können einen Nested Loop Join verwenden, um einen RIGHT OUTER JOIN auszuführen: sie alle behelfen sich damit, die Mengen in der Operation umzudrehen und einen LEFT OUTER JOIN durchzuführen. Da ich persönlich auch immer nur Left Outer Joins schreibe, habe ich mit dieser Strategie keine Probleme.
• auch mit dem FULL OUTER JOIN gibt es Beschränkungen: Postgres verwendet grundsätzlich keinen Nested Loop Join für diese Operation, sondern immer nur einen HAST bzw. MERGE JOIN.
• Oracle und der SQL Server wandeln die Operation intern um in eine Operation der Form
  T1 LEFT OUTER JOIN T2
  UNION ALL
  T2 ANTI JOIN T1

Für Postgres könnte das ein Grund sein, eine solche Query umzubauen, um manuell die Nested Loop Operation zu ermöglichen (das passende Beispiel zur Umformulierung fände man im Artikel).

Selektive SPM-Erfassung mit Oracle 12.2

Nur damit ich die Option nicht unmittelbar wieder vergesse, der Hinweis auf einen schon vor ein paar Wochen veröffentlichten Artikel von Maria Colgan, in dem die Möglichkeit einer selektiven Erfassung von Baselines vorgestellt wird, die in 12.2 eingeführt wurde. Zur Filterung können dabei dienen:

• parsing schema
• action
• module
• sql_text

Da ich SQL Baselines für die wichtigste Option zur Stabilisierung von Plänen halte, ist das aus meiner Sicht eine sehr nützliche Ergänzung

Optimizer Strategien für Subqueries für Oracle, Postgres und MySQL

Chris Antognini hat in seinem Blog eine sehr spannende Untersuchung zur Frage durchgeführt: wie gut kommen die Optimizer unterschiedlicher RDBMS mit relativ einfachen Subqueries klar? In der umfangreichen Untersuchung betrachtet er sechs unterschiedliche Subquery-Typen:

• Scalar subqueries with equality predicate
• Scalar subqueries with inequality predicate
• Uncorrelated subqueries with either IN or EXISTS
• Uncorrelated subqueries with either NOT IN or NOT EXISTS
• Correlated subqueries with either IN or EXISTS
• Correlated subqueries with either NOT IN or NOT EXISTS

Zu jedem dieser Typen werden wiederum mehrere Test-Abfragen eingesetzt, die sich in der Verwendung von großen und kleinen Tabellen und der Nutzung von eindeutigen und nicht-eindeutigen, sowie von null und not null Spalten unterscheiden. Zu allen Permutationen der jeweiligen Fälle liefert er dann jeweils seine Erwartungen und die Beobachtung, was tatsächlich passiert, sowie eine Einschätzung, ob die Abweichungen im jeweiligen Fall ein Problem darstellen (also die Performance negativ gegenüber dem erwarteten Plan beeinflussen).

Wenig überraschend ist, dass keines der Systeme ein perfektes Ergebnis liefert, bei dem alle Zugriffe effizient erfolgen:

• Oracle Database 12.2: 72 out of 80
• MySQL 8.0.3: 67 out of 80
• PostgreSQL 10.0: 60 out of 80

Mich wundert, dass MySQL hier besser abschneidet als Postgres, aber das grundsätzliche Fazit ist sicher unstrittig: "Since not all queries are handled correctly, for best performance it is sometimes necessary to rewrite them."