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.

Prepared Statements und Bindewerte in Postgres

Franck Pachot erläutert in seinem Blog die unterschiedlichen Straegien bei der Verwendung von Bindevariablen bei Oracle und Postgres. Interessant sind aus meiner Sicht vor allem die Aussagen zu Postgres:

• normalerweise werden Queries bei jeder Ausführung neu optimiert.
• Ausnahme von dieser Regel sind Statements, die prepared wurden.
• Für diese gilt, dass sie bei den ersten fünf Ausführungen jeweils neu kompiliert werden. 
• Ab der sechsten Ausführung wird dann ein generischer Plan verwendet, der sich aber nicht um die bis dahin verwendeten Bindewerte kümmert (also kein "bind peeking" verwendet).
• Dieser generische Plan verwendet eine Selektivität, die sich ergibt, wenn man die - per default vorhandenen - Histogramme ignoriert.
• Ob der generische Plan verwendet wird, hängt auch noch davon ab, ob der Optimizer ihn als effizienter betrachtet als die "custom plans" (die im Beispiel bei den ersten fünf Ausführungen verwendet wurden). Ist das nicht der Fall, bleibt der Optimizer bei den "custom plans".
• in Postgres 12 lässt sich das Verhalten des Optimizers stärker beeinflussen. Hier wird der PLAN_CACHE_MODE eingeführt, der die Werte AUTO (= das beschriebene Verfahren), FORCE_CUSTOM_PLAN und FORCE_GENERIC_PLAN annehmen kann.
• Postgres unterstützt kein "plan sharing" über de Grenzen von Sessions hinaus: es gibt keinen Shared Pool.

Insgesamt ist das wieder mal ein sehr schönes Beispiel dafür, wie gut sich die Konzepte unterschiedlicher RDBMS erläutern lassen, wenn man sie miteinander vergleicht.

Informationen zur Hint-Verwendung in 19c

Eine schöne Ergänzung für dbms_xplan in 19c ist die Ergänzung des Formats HINT_REPORT - im verknüpften Artikel beschrieben von Gavin Soorma. Das Format liefert Informationen zu den im Statement angegebenen Hints und erklärt, warum ein Hint nicht berücksichtigt wurde. Diese Information musste man bislang über ein CBO Trace (Event 10053) ermitteln.

Fehler beim Mview-Refresh mit Lateral Join in 12.2

Da ich in den ODC Foren (früher OTN Foren) kaum etwas wiederfinde, hier ein Link auf eine Frage, die ich zu einem Verhalten gestellt habe, das mir zuletzt beim Refresh einer Materialized View mit Lateral Join begegnet war:

-- 12.2.0.1 SE
create table t(
   col1 number
 , col2 varchar2(4000)
);

insert into t(col1, col2) values(1, '[{"type":1,"target":42}]');
insert into t(col1, col2) values(2, '[{"type":1,"target":42},{"type":2,"target":43}]');

create materialized view t_mv
as
select t.col1, t.col2, r.val
  from t,
  LATERAL (SELECT MIN(val) AS val
             FROM JSON_TABLE ( t.col2, '$[*].target'
                               COLUMNS (val NUMBER PATH '$')
                              )
           ) r;

SQL> create materialized view t_mv
  2  as
  3  select t.col1, t.col2, r.val
  4    from t,
  5    LATERAL (SELECT MIN(val) AS val
  6               FROM JSON_TABLE ( t.col2, '$[*].target'
  7                                 COLUMNS (val NUMBER PATH '$')
  8                                )
  9             ) r;

Materialized view created.

SQL> exec dbms_mview.refresh('t_mv')

BEGIN dbms_mview.refresh('t_mv'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 1

Die Anlage der MView ist also zunächst erfolgreich, aber der complete refresh scheitert mit einem unerwarteten ORA-942-Fehler - obwohl die Tabelle im gleichen Schema liegt wie die MView. Ein Blick ins SQL Trace zur Operation zeigt, dass der Fehler offenbar die Statistikerstellung für ein transientes Objekt betrifft: eine VW_LAT, die natürlich nicht persitiert wird:

kkzfThrowError:  error_pos = kkzfGetNumRows:OCIStmtPrepare2 errcode = 942  msgbuf =ORA-00942: table or view does not exist

kkzfPrintError: error = 31933 btm = 0  emsglen = 137
ORA-31933: error occurred during refresh statistics processing at kkzfGetNumRows:OCIStmtPrepare2
ORA-00942: table or view does not exist
kkzfSetupStatsCtxDrv:1: stats is disabled on error 31933
kkzfSetupStatsCtxDrv:1: error = 31933 is cleared
kkzdQueryObjNumByName:------ORA-1403 data not found ---
kkzdQueryObjNumByName:usrid= 104, oname=VW_LAT_9DCD9C42
kkzdQueryObjNumByName:---------------------------------

Im Forum gab es - wie üblich - ein paar gute Hinweise:

• Andrew Sayer lieferte den Vorschlag, einen optimizer_features_enable Hint zu ergänzen, um das Optimizer-Verhalten auf eine andere Version zu setzen (mit 11.1.0.7 funktioniert der refresh).
• Dontatello Settembrino merkte an, dass der refresh funktioniert, wenn man die Operation als SYS ausführt (was kein workaround ist, aber eine interessante Beobachtung).
• Mustafa Kalayci fand heraus, dass der Refresh in 12.2 funktioniert, wenn man ihn in eine skalare Subquery einbaut.

Damit habe ich ausreichend viele Varianten, um dem Problem aus dem Weg zu gehen.

Partitionierung mit Postgres 12

Daniel Westermann hat im dbi Services Blog eine interessante Serie begonnen, die sich mit den aktuellen Möglichkeiten der Partitionierung in Postres 12 (das sich noch im Beta-Status befindet) beschäftigt:

• PostgreSQL partitioning (1): Preparing the data set: beschreibt die Erzeugung einer Testtabelle aus einer öffentlich verfügbaren Datenquelle der US-Regierung. Die Ladeoperation erfolgt über ein copy-Kommando. Zu dieser Tabelle wird eine Materialized View erzeugt. Wenn eine MV einen unique index besitzt kann der Refresh concurrently erfolgen (also parallele Zugriffe während des Refreshs erlauben).
• PostgreSQL partitioning (2): Range partitioning: zeigt das Vorgehen zur Anlage einer Tabelle mit Range-Partitionierung auf Jahresbasis, bei dem zunächst die partitionierte Tabelle und anschließend separat die Partitionen angelegt werden. Die ranges werden in der Definition der Partition explizit angegeben - also nicht über ein Pattern. Außerdem muss die Obergrenze bereits den ersten Tag des Folgejahres angeben, da die "to" Angabe eine exklusives Limit darstellt. Zusätzlich zu den definierten range-Partitionen kann eine default Partition erzeugt werden, die alle Datensätze aufnimmt, die keinem der definierten ranges entsprechen.
• PostgreSQL partitioning (3): List partitioning: zeigt das Verhalten von List Partitionierung, dass anscheinend keine besonderen Überraschungen bietet.
• PostgreSQL partitioning (4): Hash partitioning: beschäftigt sich mit Hash Partitionierung. Dazu wird in der Partitions-Definition eine modulus-Funktion auf einen Spaltenwert verwendet, um die Zuordnung zu bestimmen. In diesem Fall ist die Anlage einer default-Partition nicht möglich (oder sinnvoll). Da die Verteilungsfunktion manuell definiert wird, liegt es beim Verwender, dafür zu sorgen, dass sich eine plausible und gleichmäßige Verteilung auf die Partitionen ergibt. Hier scheint das Verfahren noch nicht allzu elaboriert zu sein.
• PostgreSQL partitioning (5): Partition pruning: behandelt das Partition Pruning, also die Möglichkeit des Optimizers, für eine Abfrage irrelevante Partitionen beim Zugriff ausklammern zu können. In Postgres 10 funktionierte das nur, wenn eine Einschränkung bereits in der Planning Phase bestimmbar war. In Postgres 11 kann das Pruning auch erfolgen, wenn die Einschränkung erst bei der Execution erkennbar wird.
• PostgreSQL partitioning (6): Attaching and detaching partitions: zeigt, wie man Partitionen über attach in eine partitionierte Tabelle eingliedern - bzw. über deatch daraus lösen kann. Die abgehängte Tabelle kann dann nach belieben weiter verwendet werden.
• PostgreSQL partitioning (7): Indexing and constraints: erklärt, wie die Beschränkungen der Partitionierung reduziert werden konnten. In Postgres 10 konnte man keinen Primary Key auf einer partitionierten Tabelle anlegen, was inzwischen möglich ist. Möglich ist auch die Anlage eines Index, der auf eine einzelne Partition beschränkt bleibt. Was noch nicht funktioniert ist die Anlage eines partitionierten Index (in Oracle wäre das ein lokaler Index) mit der Option concurrently. Man kann den Index aber beschränkt auf der Ebene der partitionierten Tabelle anlegen, was ihn in einem invaliden Zustand bringt, und dann ein create index concurrently auf Partitionsebene starten. Anschließend können diese Index-Partitionen an den partitionierten Index attached werden (was diesen in den Zustand valid überführt). Auch die Anpassung von Constraints (etwa not null) kann individuell auf Partitions-Ebene erfolgen.

 Die Serie wird fortgesetzt und ich versuche - wie üblich - die folgenden Artikel hier zu ergänzen.

Indizierung von NULL-Werten

Randolf Geist hat nach einer längeren Pause zuletzt wieder begonnen Blog-Artikel zu veröffentlichen, was mir sehr gut gefällt. In zwei Artikeln behandelt er die Effekte der Indizierung von NULL-Werten, insbesondere in Kombination mit IN/OR Prädikaten:

• Indexing Null Values - Part 1: zeigt zunächst ein Beispiel, in dem ein Index auf einem einzelnen Attribut durch Ergänzung einer Konstante dazu gebracht wird, auch NULL-Werte zu indizieren, was ein übliches Verfahren für derartige Fälle ist. Dieser Index wird dann auch für IS NULL Prädikate verwendet. Im Plan ist erwartungsgemäß zu sehen, dass ein weiteres Prädikat mit einer IN-Liste als Filter-Prädikat für die Tabelle auftaucht. Wenn man den Index neu erzeugt und dieses zweite Attribut als zweite Spalte des Index verwendet, führt die gleiche Query zu einem access-Prädikat für die NULL-Prüfung und einem Filter-Prädikat für die IN-Liste beim Index-Zugriff. Diese Filterung auf dem Index ist zwar günstiger als die Filterung in der Tabelle, aber eigentlich sollte es möglich sein, beide Prädikate im access zu verwenden. Dieser Effekt ist dann offenbar auch dafür verantwortlich, dass hier kein "inlist iterator" verwendet werden kann: anscheinend erlaubt die Implementierung hier keine Kombination der Prädikate aus IS NULL Prüfung und IN/OR Einschränkungen. Dieses Verhalten ändert sich, wenn man die Reihenfolge der Spalten im Index ändert und die Spalte der IS NULL Bedingung ans Ende der Spaltenliste setzt.
• Indexing Null Values - Part 2: behandelt das Verhalten von Bitmap Indizes in vergleichbaren Fällen. Hier ist der Bitmap Index mit mehreren Spalten natürlich eine unübliche Wahl - obwohl er bei entsprechender Datenverteilung ausgesprochen kompakt sein kann. Sichtbar wird, dass Oracle hier sehr merkwürdige Cost-Angaben erzeugt, die wenig mit dem tatsächlichen Aufwand beim Zugriff haben. Auch scheint die Darstellung von Filter- und Acces-Prädikaten im Plan nicht unbedingt viel mit der tatsächlichen Arbeit der runtime engine zu tun zu haben. Plausible Werte erhält man nur mit der vorgesehenen Verwendung einspaltiger Bitmap Indizes, obwohl der mehrspaltige Bitmap Index im Beispiel tatsächlich geringfügig effizienter ist.

Sollte die Serie fortgesetzt werden, ergänze ich die zugehörigen Artikel - vielleicht.

dbms_job Umwandlung bei der Migration zu Oracle 19

Mike Dietrich informiert in seinem Blog darüber, dass mit Oracle 19 die mit dbms_job definierten Jobs in Aufträge des dbms_scheduler umgewandelt werden. Da dbms_scheduler seit den Tagen von Oracle 10 existiert, in so ziemlich allen mir erinnerlichen Punkten robuster und flexibler als dbms_job ist, bessere Überwachungsmechanismen besitzt und da dbms_job mit Oracle 12.2.0.1 endlich als deprecated klassifiziert wurde, halte ich das persönlich erst mal für eine sinnvolle Entwicklung.

Trotzdem muss man im Rahmen des Upgrades natürlich darauf achten, dass es bei der Konvertierung nicht zu unerwarteten Effekten kommt. Zur Umwandlung sind noch folgende Punkte relevant:

1. während des Upgrades auf 19c wird zu jedem dbms_job-Job ein entsprechender dbms_scheduler-Job erstellt
2. das dbms_job-Interface funktioniert weiterhin, aber es wird immer zur Anlage von scheduler-Jobs führen
3. ein zugehöriger Check in preupgrade.jar prüft auf Inkonsistenzen

Somit kann man also dbms_job weiterhin verwenden, aber unter der Haube wird alles in scheduler-Jobs umgewandelt. Ob die weitere Verwendung von dbms_job unter diesen Umständen besonders sinnvoll ist, sei dahingestellt - aber diese Frage stellte sich ja schon von dem Umzug auf Version 19.

OLTP und basic compression ab 12.1 für Datensätze mit mehreren row pieces

Randolf Geist weist in seinem Blog auf eine interessante Änderung hin, die mit Oracle 12.1 eingeführt wurde - aber mir (wie vermutlich auch vielen anderen) entgangen war: inzwischen funktionieren die OLTP und die basic compression auch für Datensätze mit mehr als einem row piece - also mehr als 254 Spalten. Dazu wurde ein neuer interner Parameter "_widetab_comp_enabled" eingeführt, der per default auf true gesetzt wurde. Allerdings gab es in diesem Zusammenhang wohl diverse Probleme im Kontext des redo apply, weshalb das Feature in späteren PSUs für 12.1 teilweise deaktiviert wurde. Anscheinend wurden diese Beschränkungen in 12.2 aufgehoben, aber zumindest die Dokumentation ist in diesem Zusammenhang nicht ganz klar. Der Artikel enthält diverse Testfälle, mit denen man das Verhalten für unterschiedliche Szenarien noch genauer überprüfen kann.

Gestern ging's noch

Brent Ozar hat in seinem Blog eine Liste mit 15 Gründen veröffentlicht, die dazu führen können, dass eine Query im SQL Server plötzlich langsamer wird, als sie das bisher gewesen war - und in den Kommentaren wurden noch ein paar weitere Begründungen aufgeführt. Vor ein paar Jahren hatte Jonathan Lewis eine ähnliche Liste mit 20 Punkten für Oracle erstellt, für die mir Hemant Chitale via Twitter den passenden Link geliefert hat. Zu den aufgeführten Gründen gehören:

• veränderte Workload auf dem Server
• bind peaking/parameter sniffing
• veränderte Objektstatistiken
• veränderte Hardware (in der virtuellen Umgebung/Cloud)
• Anlage, Löschung, Neuaufbau von Indizes
• Trace-Operationen
• Patches
• veränderte Memory-Settings
• Plan-Management-Effekte
• Hint-Auswirkungen
• Locking-Effekte (Hot Blocks)
• Änderungen in konkurrierenden Operationen

Wie man sieht, habe ich nicht alle Punkte übernommen, aber letztlich sind die Ursachen in beiden RDBMS (und in allen anderen, die ich kenne) jeweils ähnlich.

Automatisches SQL Plan Management mit 19c

Nigel Bayliss erläutert im Oracle Optimizer Blog das in Oracle 19c eingeführte "automatic SQL plan management", das die bekannten SPM Mechanismen automatisiert: da es in 19c per default aktiv ist, werden demnach in diesem Release grundsätzlich Alternativpläne erstellt, bewertet und per Baseline festgeschrieben. Um das das Feature zu deaktivieren oder zumindest auf eine manuelle Freigabe der Vorschläge umzustellen, gibt es diverse Aufrufe im DBMS_SPM-Package, die im Artikel erläutert werden.

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.

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.

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.

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.

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.

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.

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.

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.