Dienstag, Februar 13, 2018

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.

Donnerstag, Februar 08, 2018

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.
Die folgenden Links werde ich hier voraussichtlich ebenfalls noch ergänzen, aber vermutlich werde ich dazu auch nicht viel mehr zu sagen  haben als zum zweiten Artikel.

Donnerstag, Februar 01, 2018

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.

Dienstag, Januar 30, 2018

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.

Montag, Januar 22, 2018

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

Montag, Januar 15, 2018

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

Dienstag, Januar 02, 2018

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