Donnerstag, März 21, 2019

Recursive IM_DOMAIN$ Zugriffe

Jonathan Lewis liefert in seinem Scratchpad eine Lösung für ein Problem, das Franck Pachot vor einiger Zeit angesprochen hatte: in Oracle 18c kann es dazu kommen, dass die folgende Query extrem häufig ausgeführt wird und zu Performance-Problemen führt (oder dazu beiträgt):
select domain# from sys.im_domain$ where objn = :1 and col# = :2
Diese interne Query (sprich: recursive query) gehört - wie der Name schon andeutet - in den InMemory Kontext, erscheint beim Parsen einer User-Query mit Hash Join und wird aber auch dann ausgeführt, wenn die IM Optionen gar nicht genutzt werden: und das selbst dann, wenn die User-Query im Session Cursor Cache vorliegt. Um diese Queries loszuwerden, kann man den internen Parameter "_sqlexec_join_group_aware_hj_enabled" in der Session oder systemweit auf false setzen. Das ist natürlich nur eine Option, wenn man InMemory nicht benötigt.

Montag, März 11, 2019

Deaktivierung des APPEND Hints

Jonathan Lewis erläutert in seinem Scratchpad, welche Optionen es gibt, um einen APPEND-Hint zu deaktivieren. Eine solche Deaktivierung kann z.B. wünschenswert sein, wenn die APPEND-Operationen zu einer massiven Verschwendung von Speicherplatz führen, da die durch sie gefüllten Blöcke immer oberhalb der highwater mark (HWM) ergänzt werden. Nicht in Frage kommt in diesem Fall die naheliegende Lösung eines SQL Patches mit einem Hint ignore_optim_embedded_hints, da APPEND kein Optimizer-Hint ist, sondern in die Kategorie "behaviour" gehört. Stattdessen gibt es folgende Optionen, die die APPEND-Operation in ein normales INSERT ändern:
  • Ergänzung eines row-level triggers. Ein solcher Trigger führt allerdings auch dazu, dass das "array processing" in ein "single row processing" umgewandelt wird, was eine Erhöhung des redo Volumens hervorruft.
  • Verwendung eines non-unique Index zur Unterstützung eines unique constraints. Auch hier kann es zu einem "single row processing". Außerdem hat ein solcher Index in manchen Fällen ungünstige Wirkungen auf die Entscheidungen des Optimizers.
  • Ergänzung eines Foreign Key Constraints: in diesem Fall muss zwar eine Prüfung erfolgen, aber anscheinend kein "single row processing".
Somit ist aus Sicht des Herrn Lewis die Ergänzung eines FK auf eine leere Tabelle (mit PK) die beste Option. Dazu muss in der Tabelle, für die der APPEND-Hint problematisch wäre, eine zusätzliche leere und als "invisible" definierte Spalte ergänzt werden, die per FK auf die leere Parent-Tabelle verweist. Da die Spaltenwerte immer NULL sind, ist keine Prüfung der FK-Beziehung erforderlich und auch eine Index-Maintenance entfällt. Aus Sicht der Wartbarkeit könnten die beiden anderen Alternativen aber vielleicht besser handhabbar sein, als diese aus Performance-Sicht vermutlich günstigste Variante.

Donnerstag, Februar 28, 2019

Optimierung skalarer Subqueries für Oracle und SQL Server

Nenad Noveljic hat zuletzt in zwei Artikel das Verhalten von skalaren Subqueries im SQL Server und in Oracle untersucht und dabei darauf hingewisen, dass dies einer der (nicht allzu häufigen) Fälle ist, in denen der Optimizer des SQL Servers eine bessere Lösung bietet als Oracles Optimizer. Grundsätzlich bereiten skalare Subqueries Schwierigkeiten, können aber in vielen Fällen in einen Join umgewandelt werden. Der SQL Server schafft das intern - also ohne eine explizite Umformulierung durch den Entwickler - in einer deutlich höheren Zahl von Fällen als das Oracle Pendant:
  • Correlated Subqueries in the SELECT Clause: erläutert das costing für skalare Subqueries und weist auf die Fälle hin, in denen skalare Subqueries unerfreulich werden. Das Muster, dass man im Ausführungsplan dazu findet ist a) zwei Children eines Selects ohne einen Join, b) ein Filter Prädikat für das erste child, c) hohe Kosten für das parent Select aufgrund der hohen cardinality des zweiten child. Gezeigt wird auch, wie man eine solche korrelierte skalare Subquery in einen Outer Join umwandelt, was die Kosten der Operation massiv reduzieren kann (was sowohl die Kosten des Optimizers als auch die reale Ressourcennutzung der Operation betrifft). Der SQL Server ist dazu in der Lage diese Umformulierung für den gegebenen Fall durch eine interne Transformation abzubilden.
  • Correlated Subqueries in the SELECT Clause (2): zeigt, dass auch Oracle in manchen Fällen eine solche interne Transformation durchführen kann, was von der Struktur der skalaren Subquery abhängt.
Früher habe ich skalare Subqueries recht gerne benutzt, weil ich sie für recht gut lesbar hielt. Inzwischen neige ich aus Performance-Gründen dazu, sie grundsätzlich überall umzuformulieren, wo sie mir begegnen.

Montag, Februar 18, 2019

CTEs ohne Materialisierung in Postgres 12

Jonathan S. Katz weist in einem Artikel auf eine wichtige Verbesserung hin, die mit Postgres 12 verfügbar werden soll: CTEs werden dann nicht mehr automatisch materialisiert. In der commit message findet man dazu folgende Beschreibung:
By default, we will inline [CTEs] into the outer query (removing the optimization fence) if they are called just once. If they are called more than once, we will keep the old behavior by default, but the user can override this and force inlining by specifying NOT MATERIALIZED.
Damit  entspricht das Verhalten recht genau dem, das auch bei Oracle verwendet wird. Aus meiner Sicht ist das eine extrem wichtige Änderung, da ich CTEs eigentlich extrem gerne verwende, um SQL lesbarer zu machen - aber im Fall von Postgres immer darüber nachdenken musste, ob ich damit dem Planner ein Problem bereitete. Das sollte jetzt einfacher werden.

Freitag, Februar 15, 2019

Erweiterte Analytische Funktionen in Postgres 11

Markus Winand zeigt, welche Verbesserungen Postgres 11 im Bereich der analytischen Funktionen bringt: insbesondere werden jetzt "Frame Units" in der OVER clause unterstützt - also Einschränkungen wie:
row between unbound preceeding and current row.
Wobei neben "row" auch "range" und "groups" als Einheit erscheinen können. Insbesondere "groups" ist dabei eine interessant Ergänzung, die nicht die Anzahl der Datensätze, sondern die der distinkten Werte berücksichtigt. Eine weitere wichtige Neuerung, die bisher nur Postgres anbietet, ist die "exclude" clause, mit der man Datensätze aus der Gruppierung in der Window-Funktion ausnehmen kann. Der Artikel spricht auch diverse andere Ergänzungen in Postgres 11 an, aber die führe ich hier nicht noch mal auf.

Freitag, Januar 18, 2019

Formatierungsoption hint_report für dbms_xplan

Eine schöne Ergänzung für die dbms_xplan.display%-Funktionen in Oracle 19 hat Nigel Bayliss im Oracle Optimizer Blog angesprochen: die Formatierungsoption hint_report. Diese liefert, was bisher nur über Trace Events wie 10053 zu erkennen war: eine Information dazu, welche explizit gesetzten Hints bei der Generierung eines Ausführungsplans tatsächlich berücksichtigt wurden. Die Option liefert einen Abschnitt "Hint Report" unter dem Ausführungsplan (und unterhalb der "Predicate Information") und in diesem Report erscheinen vor den Hints auf einzelne Buchstaben abgekürzte Kategorisierungen:
  • E: error - zeigt an, dass der Hint syntaktisch nicht korrekt ist und deshalb ignoriert wurde.
  • U: unusued  - zeigt an, dass der Hint zwar syntaktisch korrekt ist, vom Optimizer aber nicht verwendet wurde.
Dass dieses Hilfsmittel zwar nützlich ist, aber nicht alle Fragen beantwortet, zeigt Jonathan Lewis in einem ergänzenden Artikel: in seinem Beispiel erscheint ein Hint mit dem U-Kennzeichen, aber ein anderer Hint "ordered" ist nicht mit U gekennzeichnet, obwohl der resultierende Plan deutlich zeigt, dass die Zugriffsreihenfolge nicht der Reihenfolge der Tabellen in der From-Klausel entspricht. Schuld daran ist eine Query Transformation, die die Reihenfolge in der From-Klausel vor der Planerstellung umstellte. Das Fazit des Herrn Lewis lautet: auch mit dem hint_report wird es Fälle geben, in denen man das 10053er Trace befragen muss. Und: auf den "ordered" Hint sollte man zu Gunsten seines seit 18 Jahren verfügbaren Nachfolgers "leading" verzichten.

Mittwoch, Januar 16, 2019

Dokumentation für Statspack

Oracle ist ziemlich gut bei der Entwicklung von relationalen Datanbankmanagementsystemen. Weniger gut ist die Firma beim Dokumentieren der eigenen Software - und bei der geeigneten Präsentation dieser Dokumentation im Internet. Ein Stück Dokumentation, das ich auch schon gelegentlich gesucht und nicht gefunden habe, liefert Pierre Forstmann in seinem Blog: die Dokumentation für Statspack. Dass man eine komplexe Dokumentation auch über viele Releases in angemessener Form im Netz präsentieren kann, beweist übrigens Postgres.

Freitag, Januar 11, 2019

Erweiterte pg_stat_statements_reset Funktion in Postgres 12

Auf eine interessante Ergänzung der Funktion pg_stat_statements_reset in Postgres 12 weist Daniel Westermann hin: war es bisher nur möglich, die pg_stat_statements Datenbasis komplett zu löschen, erhält die Funktion in der kommenden Version zusätzliche Parameter, die eine Löschung auf den Ebenen userid, dbid und queryid erlauben. Dadurch wird dann eine bessere Kontrolle der Statistiken zu den im System ablaufenden Queries möglich. Da die pg_stat_statements für mich das zentrale Werkzeug der Performance-Analyse in Postgres darstellt, sind solche Ergänzungen ausgesprochen willkommen.