Falsche Ergebnisse bei Group By Elimination in Oracle 12.2

Jonathan Lewis weist in seinem Blog auf einen Fehler bei der in Oracle 12c eingeführten Group By Elimination hin. Diese dient eigentlich dazu, überflüssige Sortierungen zu vermeiden, wenn ein eindeutiger Schlüssel sicherstellt, dass die Gruppierung nicht erforderlich ist. Leider existiert in 12.2 ein Bug, der dazu führt, dass etwa für ein to_char(business_date, 'YYYY') die Group By Elimination wirksam wird, da die Spalte "business_date" der PK der Tabelle ist. Vom Ergebnis her wäre hier aber eine Gruppierung erforderlich, da die to_char Funktion Duplikate liefert. Letztlich liefert die Operation somit falsche Ergbenisse. Für to_char ist der Bug in Oracle 18 behoben, aber die Funktion extract liefert weiterhin falsche Ergebnisse.

Caching von PL/SQL Funktionsaufrufen

Mohamed Houri zeigt in seinem Blog einen nützlichen Trick: er stellt den Fall einer Query vor, in der ein Wert mit dem Ergebnis eines Funktionsaufrufs verglichen wird:

a.xy_bat_id = f_get_id('BJOBD176')

Dieser Zugriff ruft beim Abrufen von 18605 Datensätzen aus der zugehörigen Tabelle 18605 recursive calls hervor - und darüber hinaus sehr viele consistent gets. Eine Untersuchung mit SQL Trace zeigt, dass fast die gesamte Laufzeit auf diesen wiederholt ausgeführten Funktionsaufruf entfällt. Offenbar wird hier für jeden Datensatz rekursiv die vollständige Query ausgeführt, die in der Funktion gekapselt ist. Um das Verhalten zu ändern, genügt es, den Aufruf in ein "select from dual" zu integrieren, also:

a.xy_bat_id = (select f_get_id('BJOBD176') from dual)

Damit wird ein "scalar subquery caching" hervorgerufen: dadurch dass die Funktion für den gegebenen Eingabewert im Rahmen der gleichen Query immer den gleichen Wert zurückgeben muss, kann der Optimizer das Ergebnis durch einmalige Ausführung bestimmen und für alle folgenden Datensätze wiederverwenden. Dieser Trick ist nicht neu - Mohamed verweist in diesem Zusammenhang auf Tom Kyte -, aber unter den entsprechenden Umständen kann er extrem nützlich sein.

B*Tree Index Optimierungen in Postgres 12

Laurenz Albe erläutert in seinem Artikel im Cybertec Blog ein paar interessante Optimierungen für B*Tree Indizes, die mit Postgres 12 eingeführt wurden. Ein erster Punkt ist, dass Indizes, die als non-unique definiert sind, in Postgres 12 deutlich kompakter erstellt werden. Ursache dafür ist, dass die TID (also die tuple Id, sprich: die physikalische Satzadresse) in den Schlüssel aufgenommen wurde, was eine bessere interne Sortierung der Einträge mit sich bringt und dafür sorgt, dass page splits bei der Erweiterung des Index seltener als 50:50 Splits in der Mitte der Struktur und häufiger als 90:10 Splits am rechten Ende der Index-Struktur erfolgen. Eine weitere Verbesserung betrifft auch die eindeutigen Indizes: die interne Speicherung in den Index Blocks wurde durch eine komprimierte Ablage der Informationen optimiert, was eine Reduzierung der Anzahl der Ebenen in der Index-Struktur mit sich bringt und damit die Zahl der Lesezugriffe beim Zugriff auf einen einzelnen Index-Eintrag reduzieren kann. Die Änderung der internen Index-Struktur ist als Version 4 der B*Tree Index-Implementierung definiert und bedeutet auch, dass Indizes nach einer Migration via pg_upgrade neu aufgebaut werden müssen, um optimiert abgelegt werden zu können. Neben diesen beiden detailliert ausgeführten Verbesserungen werden noch folgende Änderungen erwähnt, die Postgres 12 mit sich bringt:

• Reduzierung des locking overhead bei Inserts
• Einführung einer "redindex concurrently" Option (was aus meiner Sicht ein extrem nützliches Feature ist)
• Performance-Verbesserungen für index-only scans bei Indizes mit vielen Attributen
• Ergänzung einer dictionary view pg_stat_progress_create_index, mit der sich der Fortschritt beim Index-(Neu-)Aufbau überprüfen lässt

Insgesamt sind das diverse hochinteressante Features, die Postgres 12 interessant machen können.

Speicher-Fragmentierung für Linux-Server

Nikolay Savvinov hat zuletzt mehrere interessante Artikel zum Thema der Memory Fragmentation auf Linux-Systemen veröffentlicht, die ich hier einfach mal verlinke, ohne mich allzu intensiv mit den Inhalten zu beschäftigen:

• How to hang a server with a single ping, and other fun things we learned in a 18c upgrade: behandelt die Probleme eines Upgrades eines alten und komplexen Systems von Oracle 11 auf 18, die sich zunächst in einem hohen Load Average manifestierten und mit ps analysiert werden konnten, wobei vor allem der "wait channel" (wchan) ausgewertet wurde. Das Ergebnis deutete auf Memory Fragmentierung hin, da vor allem der Channel cma_acquie_dev sichtbar wurde, wobei CMA für "Contiguos Memory Allocator" steht. Zur Behebung der Symptome diente zunächst die Deaktivierung von NUMA, aber die eigentliche Ursache waren wohl rds-ping Operationen. Wer angesichts dieser Zusammenfassung etwas ratlos bleibt (zumindest im Bereich der Auflösung), darf das gerne auf meine mangelnde Sachkenntnis in diesen Bereichen zurückführen.
• Memory fragmentation: the silent performance killer: erklärt Memory Fragementation und zeigt, mit welchen Mitteln man die zugehörigen Effekte in Linux-Systemen analysieren kann. Hier versuche ich mich erst gar nicht an der Zusammenfassung, sondern zitiere "As usual, exact solution will depend on the specific scenario of the problem, but it would typically involve changing VM settings (such as vm.min_kbytes_free) or adjusting memory cgroup configuration."
• Where did my RAM go?: liefert ein wenig R code zur Visualisierung der Performance-Informationen.

Da ich in Linux-Zusammenhängen häufiger über Memory-Fragen stolpere, werden ich das Werkzeug möglicherweise gelegentlich zum Einsatz bringen.

Erläuterungen zu Execution Plans in Postgres

Damit ich es wieder finde: David Conlin hat einen Glossar zu den Angaben in den Execution Plans des explain Befehls in Postgres veröffentlicht. Darin finden sich auch Link zu älteren Artikeln von Hubert Lubaczewski (aka DEPESZ). Obwohl ich ziemlich oft auf Ausführungspläne schaue, gibt es in diesem Bereich immer wieder Überraschungen - und da ist jeder erhellende Beitrag nützlich. Interessant ist etwa der Hinweis, dass die summierten Angaben bei "Materialize" Knoten aufgrund von Rundungen manchmal nicht zu den untergeordneten Werten passen. Oder die exakte Erklärung für "Actual Startup Time" (= Laufzeit bis der erste Datensatz zurückgeliefert ist). Oder die exakten Erklärungen zu den Buffer-Angaben.

Keine non-pdb Systeme mehr mit Oracle 20

Es kommt nicht überraschend, sei hier aber erwähnt: mit Oracle 20 wird die non-CDB Architektur nicht mehr zur Verfügung stehen. Dafür ist mit Oracle 19 in SE2 und EE die Verwendung von drei (vom User erzeugten) pluggable databases auch ohne Multitenant-Lizenz erlaubt. So nachzulesen bei Mike Dietrich.

Löschung von automatisch erzeugten Indizes in Oracle 19c

Die automatische Generierung von Indizes durch das "Auto Indexing" in Oracle 19c sieht auf den ersten Blick wie ein ausgesprochen interessantes Feature aus. Das ist sie zweifellos auch. Wie gut sie funktioniert, ist ein anderes Thema. Franck Pachot zeigt, dass es zumindest nicht ganz leicht ist, die automatisch generierten Indizes wieder los zu werden: mit einem simplen "drop index" klappt das schon mal nicht - und ob manuelle Anpassungen in sys.ind$ tatsächlich eine gute Idee sind, wage ich (wie auch der Herr Pachot) zu bezweifeln.

AWK-Skripte zur Auswertung von CBO Traces

Die durch das Trace Event 10053 erzeugten Optimizer Traces sind eine großartige Hilfe, wenn es darum geht, die Entscheidungen des Optimizers nachzuvollziehen. Leider sind die Ausgaben aber so umfangreich und unübersichtlich, dass es unter Umständen ziemlich lange dauert, bis man die relevanten Details daraus exzerpiert hat. Zu Vereinfachung des Vorgehens hat Nenad Noveljic ein paar Skripte auf AWK-Basis veröffentlicht:

• EXTRACTING QUERY BLOCK INFORMATION FROM OPTIMIZER TRACE: erläutert das Skript opt_qb.awk, mit dessen Hilfe man Informationen zu einem bestimmten Query-Block anzeigen lassen kann.
• EXTRACTING JOIN ORDER FROM OPTIMIZER TRACE: erläutert das Skript opt_jo.awk, das die Informationen zu einer bestimmten Join Reihenfolge liefert.

Ob ich diese Skripte tatsächlich nutzen werde, weiß ich noch nicht: aber die Idee einer solchen Filterung erscheint mir ausgesprochen einleuchtend.