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."

Visualisierung der page Inhalte in postgres

Bertrand Drouvot, von dem ich schon viele interessante Artikel zu Oracle-Fragestellungen gelesen habe, hat in zwei Artikeln sein Tool "pgdfv" (PostgreSQL data file visualizer) vorgestellt, das zur Darstellung der Verteilung von Datensätzen innerhalb einer page bzw. eines files dient:

• Welcome to pgdfv: PostgreSQL data file visualizer:  zeigt die Ergebnisse für eine page einer Tabelle
• Visualize PostgreSQL index file with pgdfv: zeigt die Ergebnisse für eine page eines Index 

Diese Repräsentation erlaubt es auch sehr gut, die Effekte von vacuum-Operationen zu beobachten. Im ersten Artikel verweist der Autor auf Frits Hooglands Darstellung des Dateiaufbaus in postgres, die man nicht oft genug erwähnen kann.

Probleme mit der automatischen Statistikerfassung in 12c

Mohamed Houri zeigt einen interessanten Fall, in dem das in 12c ergänzte Feature der automatischen Erstellung von Optimizer Statistiken für eine zuvor leere Tabelle unerfreuliche Effekte mit sich bringt. Im geschilderten Fall erfolgt das zugehörige INSERT /*+ append */ auf Basis einer remote Tabelle und die automatische Statistikerfassung verlängerte die Laufzeit der Operation auf mehrere Tage. Das Feature wurde dabei nicht aktiv, wenn aus der Spaltenliste einzelne Spalten ausgeschlossen wurden. Zudem kann es über den Hint no_gather_optimizer_statistics deaktiviert werden. Ich muss gestehen, dass mir nicht völlig klar ist, warum die Statistikerfassung im vorgestellten Fall solche Probleme bereitet, aber die Erinnerung daran, dass man es unter Umständen unterdrücken sollte, könnte gelegentlich nützlich sein.

Extended Statistics und dynamic sampling

Eigentlich ist dynamic sampling eine Standardantwort auf falsche cardinalities für komplexe Queries mit hoher Laufzeit, bei denen das Parsing gegenüber der Gesamtlaufzeit in den Hintergrund tritt. Leider ist das Zusammenspiel zwischen dynamic sampling und statischen Statistiken noch ausbaufähig: aktuell werden z.B. extended statistics beim Einsatz von dynamic sampling komplett ignoriert - wie Franck Pachot in seinem Blog zeigt. Besonders gut gefällt mir das Fazit:

In this case, Adaptive Dynamic Sampling is a good approximation. But it would be better to have a level of dynamic sampling that does not consider a predicate as a complex one when the extended statistics exactly match the predicate. Before there is enough artificial intelligence to cope with this, the best recommendation is to focus on design. In this case, ensuring that we have only uppercase values is the best way to keep queries and estimations simple.

Ich denke, dass genau solche Effekte dafür sorgen, dass auch in Zeiten von "self-driving databases" in absehbarer Zeit nicht damit zu rechnen ist, dass Datenbankspezialisten über Umschulungen nachdenken müssen.

Verhalten der auto_sample_size in 12c

Nigel Byliss erläutert im Oracle Optimizer Blog, die erfreulichen Änderungen, die für die auto_sample_size in 12c eingeführt wurden. Dabei ist die auto_sample_size der default-Wert für den Parameter estimate_percent der dbms_stats.gather_*_stats Prozeduren. Obwohl sie für viele Statistiken tatsächlich bereits seit ihrer Einführung sehr gute Ergebnisse lieferte, gab es einen Bereich, in dem ihre Ergebnisse recht erbärmlich ausfallen konnten, nämlich die Erstellung von Histogrammen, denn dafür wurde stets ein mikroskopisches Sample von gerade einmal 5500 Datensätzen verwendet.

In 12c wird nun folgendes Verfahren verwendet:

• es erfolgt ein full table scan (also 100% Sample).
• die Ermittlung von NDV-Werten (sprich: number of distinct values) erfolgt ohne Sortierung, sondern verwendet einen "approximate NDV algorithm", der mit Hash Werten arbeitet. Die Genauigkeit dieses Algorithmus ist dicht an 100%.
• frequency und top frequency Histogramme werden mit den Daten des full table scans aufgebaut - also nicht mehr auf Basis einer minimalen Stichprobe. Zur Erinnerung: ein top frequency Histogramm kommt in Frage wenn die top 254 Werte mehr als 99% aller not null Werte ausmachen.
• hybrid histograms verwenden weiterhin ein kleineres Sample: dieser Schritt ist also von der Basiserfassung getrennt.
• Index-Statistiken werden mit einer automatisch ermittelten Stichprobengröße erzeugt.

Da mir die 5500 (non null) Zeilen in der Vergangenheit regelmäßig Ärger bereitet haben, halte ich diese Veränderung für ausgesprochen vorteilhaft.