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.

CTAS-Statement-Texte in 12.2 Trace-Files nicht mehr gekürzt

Der Titel ist beinahe länger als der folgende Text, aber was tut man nicht alles, um den eigenen Blog nicht völlig einschlafen zu lassen: Martin Bach weist in seinem Blog darauf hin, dass die Texte in der SQL-Trace-Ausgabe für "create table as select" (aka CTAS) in 12.2 nicht mehr auf 20 Zeichen gekürzt werden, was in älteren Releases der Fall war und die Analyse solcher Operationen über Trace erschweren konnte. Darüber war ich in älteren Versionen auch schon gelegentlich gestolpert.

Mehrere SQL_IDs zu einem Query-Text

Jonathan Lewis erläutert in seinem Blog ein - zumindest für mich überraschendes (sprich: bisher unbekanntes oder wieder vergessenes) Phänomen: zum gleichen SQL Text kann es mehrere unterschiedliche SQL_IDs geben. Eine relativ bekannte Ursache dafür ist offenbar, dass die Länge der in einer Query verwendeten Bindevariablen einen Einfluss auf die Erzeugung der SQL_IDs hat, was vermutlich ein Effekt des internen library Mechanismus ist und keine Design-Entscheidung.

Darüber hinaus erwähnt der Autor die Möglichkeit, ein Statement über die Prozedur dbms_shared_pool.markhot() - nun ja: als "hot" zu markieren, was dann sinnvoll ist, wenn sehr viele Sessions permanent die gleiche Query ausführen, so dass sehr viele Zugriffe auf den gleichen child cursor erfolgen. In diesem Fall wird eine neues Attribut in v$db_objct_cache ergänzt, dass als zusätzlicher Textbestandteil hinzukommt und damit einen neuen Hash-Wert und eine neue SQL_ID hervorruft. Dazu gibt es auch eine entsprechende unmark-Routine, mit der man die Kopien wieder reduzieren kann.

Performance Feedback Optionen in 12c aktivieren und deaktivieren

Christian Antognini erläutert in seinem Blog, welche Schwierigkeiten sich in 12c ergeben, wenn man das "Performance Feedback" aktivieren oder deaktivieren möchte. Zur Erinnerung: das Performance Feedback ist ein adaptives Feature und dient zur automatischen Prüfung, ob ein initial gewählter Parallelisierungsgrad tatsächlich vorteilhaft ist. Relativ harmlos ist bei der Konfoiguration noch der Faktor, dass sich der zugehörige Parameter in 12.1 OPTIMIZER_ADAPTIVE_FEATURES nennt, während er in 12.2 (bzw. auch in 12.1, wenn man den passenden Patch installiert hat) auf den Namen OPTIMIZER_ADAPTIVE_STATISTICS hört. Daneben muss in beiden Versionen zusätzlich noch der Parameter PARALLEL_DEGREE_POLICY auf true gesetzt werden, um das Performance Feedback zu aktivieren. Weniger schön ist, dass die Reihenfolge der Setzung der Parameter eine Rolle spielt - und darüber hinaus der interne Parameter _OPTIMIZER_PERFORMANCE_FEEDBACK. Die Nacherzählung der Effekte der Reihenfolge spare ich mir an dieser Stelle.

Änderungen an v$diag_alert_ext in 12.2

Vor kurzem hat Markus Flechtner in seinem Blog auf einige Änderungen hingewiesen, die sich in 12.2 für die View v$diag_alert_ext ergeben haben, mit der man die Inhalte aus alert.log und weiteren Log-Dateien via SQL abfragen kann. Was sich leider anscheinend nicht geändert hat, ist, dass dieser Zugriff weiterhin deutlich langsamer erfolgt als der über die X$-View X$DBGALERTEXT. Dafür ist v$diag_alert_ext aber in 12.2 offiziell dokumentiert, was ihre Verwendung an Stelle des X$-Objekts interessanter macht. Wichtiger ist wahrscheinlich noch, dass v$diag_alert_ext inzwischen sinnvolle Werte in der Spalte con_id enthält und dass die View in einer PDB jeweils nur die Einträge des entsprechenden Containers anzeigt. Und für mich ist noch interessanter, dass die ergänzende View V$DIAG_TRACE_FILE einen SQL-Zugriff auf Trace-Dateien ermöglicht. In Summe sieht es danach aus, dass diese Teile der Infrastruktur inzwischen eine recht hohe Stabilität gewonnen haben - in 11.2.0.3 konnte der Zugriff auf die View noch zu recht harten ORA-07445-Fehlern führen und war daher nicht unbedingt ratsam.

impdp mit metadata_only content führt zu gelockten Statistiken

Der Titel sagt eigentlich schon alles, was ich hier ausführen wollte: Frank Pachot weist darauf hin, dass ein auf Metadaten beschränkter Import automatisch die Statistiken lockt, um dafür zu sorgen, dass sie nicht vom nächsten Statistikerfassungslauf mit relativ uninteressanten 0-rows-Einträgen überschrieben werden. Auf diese Weise kann man mit einem metadata_only Import recht gut die Ausführungspläne des CBO vom Quellsystem nachvollziehen. Was den Herrn Pachot dabei ebenso irritiert wie mich ist, dass dieses Verhalten seit 10.2 gilt - und es ihm erst jetzt aufgefallen ist; und mir dann noch etwas später...

Optimizerstrategien in Postgres und Oracle

Vor einigen Wochen hat Franck Pachot via Twitter die erfreuliche Ankündigung gemacht, eine Artikelserie mit einem Vergleich der Optimizerstrategien in Postgres und Oracle beginnen zu wollen. Noch erfreulicher ist, dass es nicht bei der Ankündigung geblieben ist, sondern dass die Artikelserie zeitnah begonnen hat. Vor ein paar Jahren hatte ich einen Artikel geschrieben, der ein paar einführende Punkte zum gleichen Thema erfassen sollte, aber dabei war der Idealtyp Oracle nur in meinem Kopf als Vergleichsmaßstab im Spiel gewesen - insofern ist das Vorgehen von Monsieur Pachot das plausiblere:

• Postgres vs. Oracle access paths – intro: erklärt die Zielsetzung der Serie und die grundsätzliche Vorgehensweise. Insbesondere weist der Autor darauf hin, dass es ihm nicht um eine Bewertung, sondern um den Vergleich der ihm bekannten Strategien des Oracle Optimizers mit denen bei Postgres. Die verwendeten Versionen sind Oracle 12.2 und Postgres 9.6.2 und die jeweiligen Ausführungspläne werden mit dbms_xplan bzw. explain analyze erzeugt.
• Postgres vs. Oracle access paths I – Seq Scan: hier werden zunächst zwei Tabellen identischer Struktur in beiden Systemen angelegt, die jeweils einen unique index auf einer id-Spalte erhalten. Anschließend erfolgt die Berechnung der Kosten für den seq scan bei Postgres bzw. den Full Table Scan in Oracles Nomenklatur. Für Postgres basiert die Berechnung auf der Anzahl der pages in der Tabelle, der Anzahl der Ergebniszeilen des Scans, der Anzahl der ausgegebenen Zeilen und den Parametern seq_page_cost, cpu_tuple_cost und cpu_operator_cost. Auch für Oracle wird das costing vorgestellt, aber hauptsächlich auf die einschlägigen Ausführung der Herren Lewis und Antognini verwiesen. Erklärt werden auch die Buffer-Informationen in der analyze explain Ausgabe: "shared hit" bedeutet, dass die Buffer aus dem Cache gelesen wurden, "shared read" weist auf einen physikalischen Lesevorgang auf der Platte hin. Mit Hilfe von strace wird dann genauer untersucht, was auf OS-Seite abläuft: über lseek() werden die Start und Endpositionen des Lesevorgangs bestimmt und die zugehörigen pages dann über read() sequentiell gelesen.
• Postgres vs. Oracle access paths II – IndexOnlyScan: erläutert einen entscheidenden Unterschied zwischen den MVCC Implementierungen von Oracle und Postgres: bei Postgres werden nur die Tabellendaten versioniert, bei Oracle zusätzlich auch die Inhalte der Indizes. Daher musste für die sum(n)-Query des ersten Artikels bei Postgres ein seq scan erfolgen, während Oracle ohne Verwendung eines FULL-Hints einen Index-Zugriff gewählt hätte. Nur nach einem vorherigen vacuum und der Setzung eines internen visibility flags kann für deine entsprechende Query ein index-only scan erfolgen. In diesem Fall ergibt sich die Kostenrechnung bei Postgres aus den Faktoren ("Anzahl Index-Blocks" * random_page_cost) +  ("Anzahl Index-Einträge" * cpu_index_tuple_cost) + ("Anzahl Ergebniszeilen" * cpu_tuple_cost).
• Postgres vs. Oracle access paths III – Partial Index: erläutert das Verhalten von Index-Only Scans hinsichtlich der Existenz von NULL-Werten: bei Oracle muss eine Spalte einen NOT NULL Constraint erhalten (oder ein NOT NULL in der zugehörigen Abfrage ergänzt werden), damit der Index-Only Scan möglich wird, weil Indizes bei Oracle NULL-Werte ausklammern (jedenfalls für einen single column index). Alternativ kann man den Index in Oracle auch um eine zweite Spalte erweitern, die eine Konstante enthält, was den Index allerdings vergrößert. Mit Hilfe von Function Based Indexes (FBI) kann man in Oracle die Indizierung auf bestimmte Werte einschränken, muss die entsprechende Bedingung aber dann auch so in der Query verwenden. Die Implementierung von Partial Indexes in Postgres ist flexibler: hier kann man im create index Kommando einfach eine where-Bedingung einfügen. Für die Verwendung des entsprechenden Index ist keine Anpassung der Query erforderlich.
• Postgres vs. Oracle access paths IV – Order By and Index: erläutert die Unterschiede von INDEX FULL SCAN und INDEX FAST FULL SCAN in Oracle und die sich daran anschließenden Effekte hinsichtlich der Sortierung (Variante 1 ist sortiert, Variante 2 nicht). Bei Postgres entspricht der Index Only Scan dem Index Full Scan bei Oracle - liefert also ein sortiertes Ergebnis (wobei jeweils veränderte collations eine zusätzliche Sortierung erforderlich machen können - für beide RDBMS ist es möglich, Indizes zu erzeugen, die collations unterstützen, die nicht der default der gegebenen Datenbank sind).
• Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX: in Postgres liefert der Ausführungsplan zwei cost-Angaben: die startup-Kosten und die Gesamt-Kosten. Um in Oracle eine Kostenangabe für einen Teil der Ergebnismenge zu erhalten, muss man den first_rows_n Parameter verwenden. Erläutert wird auch das Vorgehen bei "fetch first rows" Operationen und beim MIN/MAX Zugriff. Für letzteren gibt es bei Oracle eine spezielle Operation im Plan (INDEX FULL SCAN (MIN/MAX)), während Postgres einen Plan verwendet, der stark dem der "fetch first rows" Operation entspricht.
• Postgres vs. Oracle access paths VI – Index Scan: untersucht das Verhalten unterschiedlicher Index-Scan-Operationen und insbesondere die zugehörigen costing-Angaben. In beiden RDBMS gibt es die Möglichkeit des index only scan ohne Tabellenzugriff. Im Fall eines Tabellenzugriffs erscheint bei Oracle der zusätzliche Step TABLE ACCESS BY INDEX ROWID, Postgres belässt es beim Index Scan using . Die Einschränkung für den Index-Zugriff erscheint bei Oracle unterhalb des Plans als Access-Prädikat, bei Postgres wird sie als "Index Cond" angegeben. Im Artikel wird mit Hilfe von strace genauer untersucht, wie das Postgres costing für einen Range Scan ausfällt und dabei kommt heraus, dass der Planner im Beispiel den clustering factor berücksichtigt und die Kosten auf Basis von Sequential reads berechnet (für die gut sortierten Daten des Tests). Für Oracle ergeben sich random reads (db file sequential reads = single block reads). In beiden RDBMS sind die Abarbeitung und das Costing letztlich ähnlich, wobei sich allerdings die Einheit unterscheidet: Postgres rechnet mit sequential reads, Oracle mit random reads. Allerdings gilt das nur im gegebenen Test-Szenario mit einer hinsichtlich des Index gut geclusterten Tabelle.
• Postgres vs. Oracle access paths VII – Bitmap Index Scan: ...
• Postgres vs. Oracle access paths VIII – Index Scan and Filter: ...
• Postgres vs. Oracle access paths IX – Tid Scan: ...
• Postgres vs. Oracle access paths X – Update: ...
• Postgres vs. Oracle access paths XI – Sample Scan

Ich gehe davon aus, dass die Serie noch weitere Artikel umfassen wird, die ich dann voraussichtlich auch noch hier nacherzählen werde.

Real-Time Materialized Views mit 12.2

Bereits vor einigen Wochen hat Richard Foote einen Artikel zu den Real-Time Materialized Views veröffentlicht, die mit 12.2 eingeführt wurden. Grundsätzlich ist deren Verfahrensweise offenbar relativ einfach: beim Zugriff auf einen nicht vollständig aktuelle MView werden die Informationen der Materialized View mit denen des Materialized View Log kombiniert: es erfolgt also kein ad-hoc-Refresh des MView-Segments, sondern eine Verknüpfung der Daten des MView-Segments mit den Deltas, die im MView Log gespeichert werden. In der MView-Definition muss die Klausel "ENABLE ON QUERY COMPUTATION" ergänzt werden und im Zugriffsplan sieht man eine UNION ALL Operation, die auf die MView und das MView Log zugreift (sowie eine intensive Nutzung des result caches). Die Optimierung erfolgt kostenbasiert, so dass massive Änderungsvolumina die Verwendung der MView ausschalten können. Das klingt alles recht nachvollziehbar und durchdacht.

Löschung von Oracle Komponenten

Da ich mich selbst schon mehr als einmal darüber geärgert habe, wie schwer es ist, solide Aussagen über die Deinstallation von Oracle-Komponenten zu finden, finde ich die entsprechende aktuelle Artikelserie von Mike Dietrich extrem nützlich. Darin erläutert der Autor, was man in 11.2 und 12.2 tun muss, um folgende Komponenten zu entfernen:

• APEX
• OWM (Workspace Manager)
• DV (Database Vault)
• OLS (Label Security)
• SDO (Spatial)
• CONTEXT (Text Indizes)
• ORDIM (Multi Media)
• XOQ (OLAP API)
• APS (OLAP Analytical Workspace)
• AMD (OLAP Catalog)
• OWB (Warehouse Builder)
• EXF/RUL (Expression Filter und Rules Manager)
• EM (Enterprise Manager Database Control)
• JAVAVM/XML (Java Virtual Machine und XDK)
• XDB (XML Database)

Zu jeder dieser Komponenten gibt es einen eigenen Artikel - Links dazu liefert jeder dieser Artikel. Beinahe in allen Fällen gibt es Gruben, in die der unaufmerksame Administrator hineinstürzen kann. Einmal mehr macht sich Mike Dietrich da sehr verdient.

Redo-Struktur bei Row-Migration und Row-Chaining

Eine interessante Beobachtung von Kamil Stawiarski: wenn ein Update Row-Migration oder Row-Chaining hervorruft, dann entspricht die Struktur der zugehörigen redo-Informationen jener eines Delete mit folgendem Insert. Wenn man darüber nachdenkt, ist das völlig einleuchtend, denn der Datensatz muss aus dem einen Block gelöscht und in den anderen Block eingefügt werden. Aber dass man das so genau in den Redo-Einträgen nachvollziehen kann, war mir neu. Eine Konsequenz dieses Phänomens ist dann auch, dass Row-Migration oder Row-Chaining nicht nur auf Selects eine negative Wirkung haben, sondern auch das Redo-Aufkommen erhöhen.

Nachtrag 26.07.2017: Jonathan Lewis hat in Zusammenhang mit dem Artikel eine Liste der Redo-OP-Codes veröffentlicht, die ihm bekannt sind.

Unpivot mit Oracle

Vor einigen Wochen hat Jonathan Lewis einen Artikel zum Verhalten von UNPIVOT in Oracle veröffentlicht. Darin zeigt er mit Hilfe eines CBO-Traces, dass die UNPIVOT-Operation intern in eine Kombination mehrerer über UNION ALL verknüpfter Queries umgewandelt wird. Interessant ist dabei, dass die Zahl der Blockzugriffe (Buffers-Angabe in den rowsource-Statistiken) nicht höher ist als bei einem einzelnen Full Table Scan. Jonathan Lewis vermutet, dass der zugehörige code path dafür sorgt, dass die Blöcke im Cache Batch-weise gepinnt, dann fünf Mal gescannt und dann wieder freigegeben werden. Immerhin deutet das darauf hin, dass UNPIVOT die bessere Option als die Do-it-yourself-Transposition, die ich in der Regel noch immer verwende.

Analyse der Block-Struktur mit Postgres

Frits Hoogland, dessen Artikel zu Oracle zu den fundiertesten technischen Beiträgen gehören, die mein Blog-Reader mir liefert, hat zuletzt zwei Artikel veröffentlicht, in denen er sich mit der Analyse von Blocks in Postgres-Datenbanken beschäftigt. Da seine Analyse weit über das hinausgeht, was ich in der Vergangenheit in dieser Richtung herausgefunden hatte, erzähle ich hier mal wieder einen Teil der wichtigeren Inhalte nach:

• Postgresql block internals: zeigt die Verwendung der pageinspect Extension, mit der man Blockinhalte darstellen kann. Auch weist der Autor darauf hin, wie vorteilhaft es ist, den Sourcecode unmittelbar einsehen zu können, statt ausschließlich auf Induktion angewiesen zu sein: https://doxygen.postgresql.org/. Mit Hilfe der in der Extension verfügbaren Funktionen (page_header, heap_page_items, get_raw_page) werden der page header ausgelesen und die row entries dargestellt. Über das OS-Kommando xxd wird eine lesbarere Repräsentation der physikalischen Blockinhalte der ros erzeugt.
• Postgresql block internals, part 2: wiederum werden im Test postgres 9.6 und Oracle Linux 7u3 verwendet. Verwiesen wird auf die umfangreiche Dokumentation des Page Layouts unter https://www.postgresql.org/docs/9.6/static/storage-page-layout.htm. Auf Basis der Dokumentation erfolgt eine detaillierte Analyse der einzelnen Bestandteile der Einträge mit den Informationen zu Transaktionsnummern und internen Flags. Genauer untersucht werden die bit mask Informationen in den Feldern t_infomask (Attribut- und Transaktionsinformationen) und t_infomask2 (Update-Status-Angaben, Sichtbarkeit). Weiterhin wird die Block/Page-Struktur für einen Index untersucht, die den gleichen Header, aber andere Inhalte enthält. Zur Analyse gibt es weitere pageinspect-Funktionen, nämlich bt_metap (zur Darstellung der metapage, die auf den root-Block des Index verweist), bt_page_items (zur Darstellung der index entries) und bt_page_stats (zur Darstellung der Index Struktur). Im Fall der Struktur ist eine weitere Detailanalyse erforderlich, um aus den Daten klug zu werden und dabei zieht der Autor wiederum die Angaben des Source-Codes heran. Die zugehörigen Erläuterungen sind zu komplex, als das ich sie hier im Detail wiedergeben wollte. In der Zusammenfassung erscheinen dazu noch folgende Punkte: wenn ein Index sehr klein ist, können root und leaf Einträge in einer page zusammengefasst werden. Für größere Indizes werden die pages getrennt. Der default-Fill-Factor eines Index ist 90% (gegenüber 100% für eine Heap Tabelle). Erläutert werden auch das Split-Verhalten und die Verknüpfung der Leaf-Blocks, die nach meinem Verständnis recht ähnlich aussehen wie im Fall von Oracle (das auch der Herr Hogland als idealtypisches Muster bzw. Basisreferenz betrachtet, um die Unterschiede für den Postgres-Fall zu erläutern).
• Postgresql block internals, part 3: erläutert die Handhabung des Space-Managements im Block bzw. der Page im Fall von DML-Operationen. Im Test wird zunächst das Autovacuum deaktiviert, um die Effekte überhaupt exakt beobachten zu können, was für ein produktives System natürlich keine gute Idee wäre. Dann erfolgt ein Update, das im page header einen neuen Eintrag ergänzt, der über die Informationen in t_xmax und t_cid mit dem ursprünglichen Eintrag zur aktualisierten Id verknüpft ist - bekanntlich arbeitet die MVCC-Implementierung von postgres mit einer expliziten Speicherung unterschiedlicher Versionszustände in der gleichen Tabellenstruktur. Im zugehörigen Index gibt es keinen zusätzlichen Eintrag, da das geänderte Attribut nicht indiziert ist (es handelt sich um ein HOT - heap only tuple - Update): hier verweist in der Page dann ein Pointer vom initialen zum geänderten Datensatz. Erfolgen mehrere Änderungen auf dem selben Datensatz nacheinander, wird die Liste der Verknüpfungen verlängert - was im Fall des HOT Update die Performance beeinträchtigen kann. Aufgeräumt werden kann, wenn keine Transaktion mehr existiert, die noch Zugriff auf eine alte Version benötigt: dann kann vacuum den zugehörigen Datensatz löschen. Durch den vacuum-Befehl werden die Header-Einträge der gelöschten Datensätze aber nicht gelöscht, sondern mit entsprechenden Flags markiert, unter anderem, weil der zugehörige Index weiterhin auf den ersten Eintrag der verketteten Liste verweist. Die Offsets der gelöschten Einträge zeigen dabei, dass sie ans Ende der Page verschoben wurden, um den freien Platz in der Page zu maximieren.
  Um die Einträge vollständig zu beseitigen kann vacuum full verwendet werden, was allerdings ein table lock erfordert und daher in vielen Fällen keine Option darstellt. Im Fall der Aktualisierung eines indizierten Attributs ist ein HOT Backup nicht möglich: stattdessen muss in diesem Fall auch im Index ein neuer Eintrag ergänzt werden - dabei bleibt der alte Eintrag erhalten, aber die Informationen im Index enthalten keine Angaben zur Versionierung, so dass die Entscheidung über die Relevanz eines Ergebnisses erst in der data page getroffen werden kann. Durch ein erneutes vacuum kann in der data page jetzt der Eintrag als leer markiert werden, auf den der ursprüngliche Index-Eintrag verwies. Und im Index wird der zugehörige obsolete Eintrag entfernt.
  Ein weiterer relevanter Fall betrifft das Verhalten von Updates für einen vollständig gefüllten Block (Bzw. Page): ein Update erzeugt unter diesen Umständen eine neue Datensatzversion, die nicht mehr in der initialen Page untergebracht werden kann. Die Verknüpfung in die andere Page kann wiederum über den Pointer t_ctid verfolgt werden. In diesem Fall wird das Update nicht als HOT ausgeführt, sondern es existieren zwei Index-Einträge für den gleichen Wert, die in unterschiedliche Blöcke verweisen (wiederum, weil der Index allein nicht die Information liefert, welcher zugehörige Datensatz für eine bestimmte Transaktion relevant ist). Ein vacuum sorgt wiederum dafür, dass der ursprüngliche Datensatz in der data page als obsolet gekennzeichnet und der darauf verweisende (obsolete) Index-Eintrag entfernt werden.
  Nach dem Abschluss einer DML-Operation muss der nächste lesende Zugriff noch Anpassungen am "hint bit" in t_infomask durchführen, was Einträge im WAL file hervorruft. Dieser zusätzliche Aufwand ist nur für die erste folgende Leseoperation - und auch ein vacuum sorgt für das Setzen des "hint bist".

Wie üblich ist meine Zusammenfassung deutlich weniger aussagekräftig als das Original, das man sich im Zweifel lieber noch mal komplett durchlesen sollte. Eine derart gute Darstellung des Block Managements bei Postgres hatte ich bisher nicht gelesen.

Optimistisches Locking mit der ORA_ROWSCN

Da ich mir ziemlich sicher bin, gelegentlich wieder danach zu suchen, verlinke ich hier eine Artikelserie von Stew Ashton, die sich mit den Möglichkeiten beschäftigt, die die ORA_ROWSCN bietet, um Transaktionsprobleme zu vermeiden:

• Avoiding Lost Updates with ORA_ROWSCN: zeigt, dass die ORA_ROWSCN als Versionsnummer zur Vermeidung von "lost updates" dienen kann, wenn bestimmte Voraussetzungen erfüllt sind (die Tabelle muss mit rowdependencies erzeugt worden sein, um eine row-genaue Zuordnung der ORA_ROWSCN zu erlauben; das Verfahren funktioniert nicht mit IOTs; vor allem muss in der WHERE-Bedingung des Updates eine Referenzierung zusätzlicher Spalten erfolgen, um Effekte zu berücksichtigen, die beim automatischen internen Restart von Updates entstehen).
• Optimistic Locking: One SCN to rule them all: beschreibt eine API, mit der man Optimistisches Locking unterstützen kann.
• More on Optimistic Locking with ORA_ROWSCN: liefert ergänzende Informationen zum Verhalten des Verfahrens im Kontext der internen Update-Restarts.

Offensichtlich sind meine Kommentare zu den Artikeln nicht besonders erhellend: sie dienen tatsächlich nur dazu, die Links grob einzuordnen.

Änderungen im SQL-Plan-Management mit 12.2

Wieder ein neuer Artikel von Jonathan Lewis: diesmal weist er auf einige interessante Änderungen hin, die sich beim SQL Plan Management mit 12.2 ergeben haben:

• SQL Patches sind jetzt ein offizielles Feature: ihre Verwaltungsroutinen sind vom Package dbms_sqldiag_internal ins Package dbms_sqldiag gewandert.
• der Hint-Text für SQL Patches ist nicht mehr auf 500 Zeichen begrenzt, sondern als CLOB definiert.
• der zugehörige Funktionsname lautet nun create_patch (bisher i_create_patch).
• neben der älteren Variante, bei der der Query-Text übergeben wird, gibt es eine neue Variante, die mit der sql_id arbeitet.

Darüber hinaus enthält der Artikel Abfragen auf sqlobj$data, mit denen man die Inhalte von SQL-Profiles, SQL-Baselines und SQL-Patches anzeigen kann, die alle ihre Hints in der gleichen Tabelle speichern. Eine Variante der Query erlaubt die Abfrage für ein einzelnes Objekt eines dieser Typen, bei der man den Namen explizit angibt (und dafür sqlobj$ zusätzlich joinen muss).

Mit 12.2 wurden weiterhin neue Funktionen für dbms_xplan eingeführt, mit denen man die Pläne zu den erwähnten Plan-Management-Varianten darstellen kann, die intern in sqlobj$plan gespeichert werden.

Partitionierungs-Optionen in 12.2

Jonathan Lewis zeigt in seinem jüngsten Artikel die große Flexibilität, die die Definition von partitionierten Tabellen in Oracle 12.2 errreicht hat. Dabei liefert er größeres Code-Beispiel für ein ALTER TABLE ... MOVE, in dem folgende Punkte aufgeführt sind:

• List-Partitionierung über mehrere Spalten.
• automatic: das Schlüsselwort, das die Generierung neuer Partitionen für neu ankommende Daten erlaubt - das entspricht damit dem Interval-Partitioning für Ranges, das man schon aus älteren Releases kannte.
• indexing off: erlaubt die Beschränkung der Indizierung auf einzelne Partitionen und damit die Definition partieller Indizes.
• read only: erlaubt nur lesende Zugriffe für die betroffene Partition.
• including rows where: erlaubt bei einer MOVE-Operation die Verschiebung von Daten auf der Basis eines Filter-Kriteriums.
• online: erlaubt eine online-MOVE-Operation ohne downtime.
• update indexes: aktualisiert Indizes im Rahmen einer MOVE-Operation.
• local: die Indizes werden als lokale Indizes aufgebaut.
• indexing partial: die Indizes werden für die Partitionen mit der Option "indexing off" nicht erzeugt (also ohne Segmente erzeugt und befinden sich daher im Status "unusable").

Dazu gibt es dann noch allerlei Analyse-Code zum Beleg, dass die MOVE-Operation tatsächlich das erwartete Ergebnis brachte, nämlich "Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation." Insgesamt scheint mir das Partitioning ein Bereich zu sein, in dem 12.2 sehr nützliche Ergänzungen liefert und eine sehr große Flexibilität erlaubt.

Verzögerte Cursor Invalidierung nach DDL-Operationen in 12.2

Heute habe ich zwei neue Artikel in meinem Blog-Reader gefunden, die sich mit Themen der verzögerten Invalidierung abhängiger Cursor bei unterschiedlichen DDL-Operationen beschäftigen:

• Richard Foote erläutert das Verhalten im Zusammenhang mit der Anlage von - was war es noch mal? Ach ja: - Indizes. Wenn man nicht möchte, dass die Erzeugung eines neuen Index automatisch alle von der zugehörigen Tabelle abhängigen Cursor invalidiert, dann kann man das in 12.2 durch die Ergänzung der Klausel DEFERRED INVALIDATION beeinflussen. Allerdings ist es durchaus möglich, Szenarien zu erzeugen, in denen das Verhalten nicht unbedingt dem entspricht, was man sich wünschen würde - und natürlich tut das der Herr Foote in seinem Artikel.
• Frank Pachot spricht deferred invalidation im Kontext von partition exchange an. Dabei zeigt er, dass die Spalte v$sql.is_rolling_invalid in diesem Zusammenhang offenbar nicht plausibel aktualisiert wird, obwohl die verzögerte Invalidierung funktioniert. Werden nicht alle Indizes ausgetauscht, ergibt sich allerdings eine sofortige cursor-Invalidierung. Insgesamt kann das Feature nützlich sein, um einen hard-parse-peek beim partition exchange zu vermeiden.

Die verzögerte Invalidierung gab es bereits vor 12.2 im Rahmen von dbms_stats-Operationen und die Ausweitung auf explizite DDL-Operationen erscheint sehr plausibel.

Semantik des parallel(n) Hints

Jonathan Lewis weist in seinem Blog darauf hin, dass der Hint /*+ parallel(n) */ keineswegs einen parallelen Plan erzwingt, auch wenn eine ausreichende Anzahl an parallelen Ausführungsprozessen zur Verfügung steht. Tatsächlich weist der Hint den Optimizer nur dazu an, die Kosten der parallelen Ausführung für jeden überprüften Plan zu berücksichtigen - aber letztlich wählt der Optimizer den Plan mit den niedrigsten Kosten (sofern kein Bug im Spiel ist) und das kann durchaus ein serieller Plan sein.

Dazu liefert der Artikel ein Beispiel dafür, wie sich das Verhalten zwischen unterschiedlichen Releases verändern kann: für die Beispielquery eines (eindeutigen) id-Zugriffs liefert 11.2.0.4 auch bei Verwendung eines parallel(4)-Hints einen seriellen Plan, was beim Index-Zugriff auf einen einzelnen Datensatz durchaus verständlich ist. Erst wenn man den Zugriff mit einem zusätzlichen FULL-hint ergänzt, ergibt sich die Parallelisierung. In 12.2.0.1 sieht das Verhalten anders aus: dort erscheint ein PX SELECTOR im Plan, der eine hash distribution über Blockadressen ermöglicht, was im Fall globaler Indizes in partitionierten Tabellen hilfreich sein könnte. Das Phänomen hat Jonathan Lewis bereits vor knapp einem Jahr in einem anderen Artikel beschrieben.

Online Partitionierung einer existierenden Tabelle in 12.2

Eine sehr schöne Ergänzung der Partitionierungs-Optionen in 12.2 beschreibt Maria Colgan in ihrem Blog: die Möglichkeit, eine nicht partitionierte Tabelle ohne downtime - also online - in eine partitionierte Tabelle umzuwandeln. Die Syntax dazu sieht etwa folgendermaßen aus:

alter table t
partition by ...
(
   partition p1 ...,
   partition p2,
 ...
)
update indexes online

Das sieht für mich sehr intuitiv und vor allem kompakt aus. Dabei dient "update Indexes" wie üblich dazu, die Indizes während des Aktualisierungsvorgangs verfügbar zu halten. Die Default-Optionen bei der Umwandlung der Indizes sind folgende:

• Indizes, die bereits als "global partitioniert" angelegt wurden, behalten ihr Partitionierungs-Schema
• Indizes, die nicht mit dem "partition key" starten, werden "global non-partitioned indexes"
• Indizes, die mit dem "partition key" starten, werden lokal partitionierte Indizes
• Bitmap Indizes werden zu lokal partitionierte Indizes

Dass man diese Default-Optionen überschreiben kann, ist bei Frau Colgan nur implizit angedeutet (nämlich durch die Verwendung des Terminus "default"), aber Richard Foote hat dazu vor kurzem ein Beispiel veröffentlicht: auf das "update indexes" folgen dann in Klammern die Spezifikationen der Konvertierung.

Eine zweite nützliche Ergänzung zur Partitionierung in 12.2 ist die Möglichkeit, die Basis-Tabelle für eine "partition Exchange" Operation mit einem Befehl "create table ... for exchange with table ..." anzulegen. Allerdings scheint dieses Kommando nicht zur automatischen Generierung der passenden lokalen Indizes zu dienen, so dass hier weiterhin eine gewisse Sorgfalt bei der Vorbereitung des partition exchange erforderlich bleibt - was aber insofern kein größeres Problem darstellt, als der Austausch von Partitionen aus meiner Sicht ohnehin ein Task ist, der verskriptet werden sollte.

Darüber hinaus erwähnt die Autorin noch eine dritte nützliche Ergänzung: die Einführung von interval partitioning für List-partitionierte Tabellen. Dabei hoffe ich, dass die Intervall-Partitionierung in 12.2 stabiler geworden ist, als sie das in früheren Releases geworden ist, aber das ist ein Thema, das im Artikel nicht angesprochen wird - und dem im Detail nachzugehen mir aktuell die Zeit fehlt.

postgres Statistiken: pg_stat_all_tables

Alexey Lesovsky hat bereits eine ganze Reihe interessanter Artikel im Rahmen einer Serie "Deep dive into postgres stats" veröffentlicht. Bisher hatte ich mich davor gedrückt, diese Ausführungen zusammenzufassen, aber heute ist ein ruhiger Tag und die pg_stat_all_tables scheint mir eine besonders interessante Quelle zu sein...

Die View pg_stat_all_tables enthält eine ganze Reihe interessanter Informationen zur Nutzung von Tabellen. Unter anderem weist sie hin auf:

• Zugriffsinformationen: die View enthalt Informationen zur Anzahl sequentieller Scans (also Full Table Scans, wie man sie in anderen RDBMS nennt) und zur Anzahl von Index-Zugriffen sowie zur Anzahl der dabei zurückgelieferten Datensätze. Eine hohe Zahl sequentieller Scans auf großen Tabellen deutet häufig auf fehlende Indizes hin. Weiterhin könnten Index-Zugriffe mit hohen durchschnittlichen Satzanzahlen auf wenig selektive Index-Zugriffe hindeuten.
• Schreiboperationen: zeigt die Menge der DML-Operationen auf den Tabellen. Dabei wird zwischen Standard- und HOT-(=Heap-only tuples) Updates unterschieden, wobei letztere wünschenswert sind, da sie Indizes nicht aktualisieren, wenn ein Update keine inidzierten Werte verändert. Allerdings funktionieren HOT-Updates nur, wenn die zugehörige page ausreichend Platz übrig hat. Für Tabellen, bei denen sich HOT-Updates kaum ergeben, könnte eine Anpassung des Fillfactors sinnvoll sein (der nur für neue Datensätze berücksichtigt wird, den Platzverbrauch des Objekts vergrößert und in diesem Zusammenhang nur dann relevant ist, wenn es tatsächlich updates ohne Bezug auf indizierte Spalten gibt).
• die autovacuum queue: seit 9.6 kann man bestimmte Informationen zum autovacuum aus der View pg_stat_progress_vacuum bekommen. Was fehlt ist allerdings weiterhin eine Liste der Tabellen, für die aktuell ein vacuum-Lauf erforderlich wäre. Dazu hat der Autor eine komplexe Query (die unter anderem auf pg_stat_all_tables zugreift) bereitgestellt, mit deren Hilfe sich die Länge der autovacuum queue bestimmen lässt. Basierend auf den Aussagen der Query kann man dann über Maßnahmen zur Optimierung des autovacuum nachdenken (Erhöhung der autovacuum_max_workers oder Anpassung anderer autovacuum-Parameter).

Im Vergleich zu dem, was andere RDBMS an internen Statistiken liefern, sind die Informationen bei postgres überschaubar, erlauben aber durchaus interessante Analysen.

postgres Extensions

Zwei interessante Hinweise findet man im neusten postgres-Artikel von Daniel Westermann:

• das data dicitionary von postgres liefert zahlreiche Informationen zu den verfügbaren und den installierten Extensions:
•  pg_available_extensions: zeigt die verfügbaren und die installierten Extensions inklusive eines Kommentars zu ihrer Funktion.
• pg_available_extension_versions: liefert weitere Detailinformationen zu den Extensions, unter anderem zu den Abhängigkeiten, die zwischen den Erweiterungen bestehen.
• pg_extension: liefert Informationen zu den installierten Extensions. Dabei weichen die Informationen von denen ab, die der psql-Shortcut \px liefert.
• beinahe ebenso interessant ist der zweite Hinweis, den der Herr Westermann liefert: mit dem Switch: "\set ECHO_HIDDEN on" kann man die den psql-Shortcuts zugrunde liegenden Befehle anzeigen lassen. Mir war zwar klar, dass das irgendwie möglich sein sollte, aber bisher hatte ich mir nie die Mühe gemacht, nach dieser Option zu suchen.

Skript zur Bestimmung von Index Fragmentation

In den OTN Foren gibt es bei diversen Beiträgern eine gewisse Tendenz dazu, auf bestimmte Schlüsselwörter allergisch zu reagieren. Eines dieser Schlüsselwörter ist: Fragmentation. Im ungünstigen Fall bekommt man dann zur Antwort, dass es so etwas wie Fragmentation nicht gäbe - was ich für ausgemachten Blödsinn halte -, im günstigeren Fall, dass man den Terminus bitte erst mal definieren sollte, was durchaus eine sinnvolle Reaktion darstellt. Aber da der Fall von Index Fragmentation eher weniger kontrovers ist, belasse ich es hier bei der harmlosen Einordnung: ein Index ist dann fragmentiert, wenn er deutlich mehr Leaf Blocks beinhaltet als zur Speicherung der enthaltenen Einträge eigentlich erforderlich wären. Bei Kellyn Pot’Vin-Gorman habe ich heute einen Verweis auf ein nützliches Skript von Franck Pachot gefunden, das Folgendes leistet: "shows the number of rows per block, as well as used and free space per block, and aggregates that by range of values". Für die Detail-Analyse einzelner Indizes kann dieses Skript extrem nützlich sein - und die manuelle Sammlung und Verknüpfung der entsprechenden Informationen vermeiden.

Index Statistiken und Column Group Statistiken (Extended Statistics)

Nach einiger Zeit widme ich mich mal wieder einem meiner Hobbies: der Zusammenfassung des aktuellen Artikels von Jonathan Lewis. Diesmal geht es darum, dass der Herr Lewis gerne den - von mir häufig wiederholten - Vorschlag macht, bei der Löschung eines aus Zugriffssicht überflüssigen Index entsprechende "extended statistics" für die fragliche column group anzulegen, da der Optimizer die distinct key Angabe eines Index zur Durchführung eines Sanity Checks bei der cardinality-Bestimmung verwenden kann - was die Beurteilung von Index-Löschungen grundsätzlich schwieriger macht als die der Neuanlage von Indizes. Außerdem hat der Autor gelegentlich darauf hingewiesen, dass column group Statistiken dann Probleme bereiten (so heißen: nicht berücksichtigt werden), wenn die Einschränkung für eine der fraglichen Spalten außerhalb des Bereichs der für diese Spalte bekannten Werte liegt (also außerhalb des Korridors von low_value und high_value in user_tab_columns). Im aktuellen Artikel bringt er diese beiden Beobachtungen zusammen und stellt die Frage: kann die Löschung eines Index und Anlage von entsprechenden column group Statistiken im Fall derartiger Prädikate zu einer Veränderung der cardinality Schätzung führen. Dazu gibt es wie üblich ein Test-Beispiel: diesmal mit einer Tabelle mit zwei Spalten mit vollständig korrelierenden Werten, was ohne den Index oder die column groups in jedem Fall zu massiven Fehleinschätzungen des Optimizers führen würde. Auf dieser Basis werden drei Queries ausgeführt:

• eine Query mit Index und zwei Prädikaten innerhalb des Ranges der bekannten Werte
• eine Query mit Index und zwei Prädikaten außerhalb des Ranges der bekannten Werte
• eine Query ohne Index - aber mit column group Statistiken - und zwei Prädikaten außerhalb des Ranges der bekannten Werte

 Die Ergebnisse zeigen für die drei Fälle:

• für die erste Query werden die distinct keys des Index als Sanity Check bei der Kalkulation der Cardinality verwendet: dadurch ergibt sich ein Full Table Scan.
• für die zweite Query werden die individuellen Selektivitäten der Spalten verwendet und jenseits des bekannten Wertebereichs setzt die übliche Abnahme der erwarteten Trefferwahrscheinlichkeit ein (linear decay): dadurch ergibt sich ein Index Zugriff.
• für die dritte Query ergibt sich wieder der Full Table Scan.

Für den Herrn Lewis ergibt sich daraus: "So my concern that substituting column groups for indexes was unfounded – the optimizer was being silly (legal disclaimer: that’s just my opinion) with indexes, and the silly (ditto) behaviour with column groups hasn’t changed anything." Mir ist diese Antwort noch nicht ganz klar, da mein Eindruck ist, dass sich column group und der Index hier nicht zu den gleichen Ergebnissen führen - aber möglicherweise entgeht mir hier etwas Entscheidendes. Möglicherweise bekomme ich dazu noch eine Erklärung, die ich dann hier ergänzen würde.

Nachtrag vom gleichen Tag: Jonathan Lewis hat's erklärt: "The second and third plans both have a cardinality of 79 (which is the only thing the note was about). Were you looking at the cost, perhaps?" Tatsächlich hatte ich auf die cost-Angaben geschaut...

Formatter-Einstellung für SQLcl

Dass die Häufigkeit meiner Beiträge zuletzt weiter abgenommen hat, mag dem regelmäßigen Leser aufgefallen sein und dafür gibt es - wie in solchen Fällen üblich - berufliche und private Ursachen (im weitesten Sinne steht Arbeit im Weg). Um aber nicht völlig zu verstummen hier mal wieder ein Link: Jeff Smith erläutert in seinem Blog, wie man eine SQL-Query in SQLcl mit dem Befehl "format buffer" automatisch formatieren lassen kann. Zusätzlich zeigt er, dass man die Regeln des Formatters im SQL Developer bearbeiten, in eine Datei exportieren und von SQLcl einlesen lassen kann: damit lässt sich die Formatierung dann den eigenen Wünschen entsprechend anpassen. Es wird wirklich Zeit, dass ich SQLcl zu meinem Standard CLI mache (und SQL*Plus nach wenigen Jahrzehnten hinter mir lasse).

Serielle und parallele Update-Verarbeitung

Jonathan Lewis hat gestern in seinem Scratchpad danach gefragt, wie es dazu kommen kann, dass ein anscheinend parallelisierbares Update seriell verarbeitet wird, und stellt dazu ein umfangreiches Beispiel zur Verfügung. Die richtige - oder zumindest eine weitgehend richtige - Antwort auf die Quizfrage hat offenbar Franck Pachot geliefert, der darauf hinweist, dass ein paralleles Update in 12.1 für Tabellen mit SecureFile LOBs nur möglich ist, wenn die Tabelle partitioniert ist. Der Herr Lewis bestätigte die Vermutung, dass in seinem Beispiel eine als UNUSED markierte CLOB-Spalte im Spiel war, die in der Objekt-Definition nicht mehr sichtbar war (jedenfalls nicht via dbms_metadata oder in user_lobs; nur user_tab_cols liefert noch einen Eintrag mit einem "date and time" Namen). Außerdem wies er darauf hin, dass die Dokumentation hinsichtlich der existierenden Einschränkungen nicht besonders hilfreich ist, da es:

• sie nicht alle erwähnt
• einige schlecht beschreibt
• und einige nennt, die nicht zutreffen

Außerdem verschwiegt die Dokumentation, welche Meldung dazu im Ausführungsplan erscheint ("PDML disabled because single fragment or non partitioned table used"). Das Szenario ist nicht unbedingt eines, von dem ich mich unmittelbar bedroht fühle, aber hier zeigt sich mal wieder, dass die Dokumentation der Features und Einschränkungen des eigenen Produkts nicht zu Oracles Kernkopetenzen zählt.

Oracle 12.2 VM

Dass Oracle 12.2 (endlich) zum download verfügbar ist, konnte man dieser Tage bereits in jedem Oracle-Blog lesen. Nett finde ich aber insbesondere, dass es dazu gleich auch eine Virtual Box Appliance gibt - worauf Jeff Smith hinweist. Da ich die Installation auf Dauer nicht so sagenhaft spannend finde, bin ich dafür durchaus dankbar.

Online Statistics Gathering in 12c

Maria Colgan hat in den letzten Wochen zwei Artikel zum Thema der Erfassung von Optimizer Statistiken bei der Objektanlage veröffentlicht:

• Online Statistics Gathering: seit Oracle 9 werden die Statistiken für Indizes im Rahmen der Anlage eines Index automatisch erfasst: da in diesem Zusammenhang ohnehin ein Full Scan der Daten und Sortierungen erforderlich sind, kann man die zusätzliche Erfassung der Statistiken problemlos in die Operation integrieren. Mit Oracle 12c wird diese Technik jetzt auch für Tabellen verwendet, wenn sie über direct path Operationen wie CTAS und INSERT append (dort allerdings nur, wenn vorher noch keine Daten in der Tabelle existierten) befüllt werden. Über den Parameter _optimizer_gather_stats_on_load kann man den Mechanismus deaktivieren.
• Histogram sample size and Online Statistics Gathering: darin weist die Autorin darauf hin, dass im Fall der automatischen Statistikerstellung im Rahmen der Ladeoperationen in den Spaltenstatistiken unter Notes ein Eintrag STATS_ON_LOAD erscheint. Führt man anschließend eine Statistikerfassung mit der Option GATHER AUTO durch, dann werden nur Histogramme erzeugt (NOTES = HISTOGRAM_ONLY) und diese mit dem üblichen traurig kleinen Sample von ca. 5500 not null Werten.

Sollte Frau Colgan hier noch weitere Artikel liefern, werde ich sie an dieser Stelle ergänzen.

Intra-block und inter-block chaining

Sayan Malakshinov erläutert in seinem Blog, wie intra-block chaining bei reinen insert Operationen zustande kommt und liefert dazu zunächst die Definitions-Grundlagen:

• laut Doku gilt, dass Datensätze einer Tabelle mit mehr als 255 Spalten, in denen Spalten jenseits Nr. 255 Werte ungleich NULL enthalten "are likely to be chained within the same block. This is called intra-block chaining."
•  intra-block chaining sollte keine Auswirkung auf die I/O performance haben (ist aber in den Session-Statistiken sichtbar).
• Oracle verwendet eine umgekehrte Reihenfolge (reverse order) beim Aufbau der row pieces: im Beispiel mit 300 Spalten wird daher ein piece mit den Spalten 46-300 erzeugt und eines mit den Spalten 1-45..
• NULL-Werte am Ende eines Datensatzes werden nicht physikalisch abgespeichert - das gilt aber nicht für row pieces.
• das intra-block chaining ergibt sich nur bei inserts: sind updates im Spiel, so wird das row piece in einen anderen Block verlagert und es ergibt sich inter-block chaining.

Neben den Beispielen im Artikel ist vor allem der Hinweis interessant, dass der Umgang mit Trailing Nulls zu recht bizarren Effekten führen kann: im Beispiel gelingt es dem Herrn Malakshinov mit einem Insert eines Datensatzes mit einem Wertes in Spalte 1 einer Tabelle mit 355 Spalten und drei folgenden updates auf die Spalten 300, 301 und 302 eine Aufteilung dieses Datensatzes in vier row pieces (mit inter-block chaining) hervorzurufen:

• das insert legt ein row piece an, bei dem die trailing nulls keine Bedeutung haben.
• das erste Update führt zur Teilung des pieces in zwei Teile: 1-45 und 46-300.
• das zweite Update teilt das größere Stück wiederum in zwei Teile 46 und 47-301.
• und das dritte Update wiederholt diese Operation mit dem Ergebnis 47 und 48-302.

Insgesamt ergeben sich somit zwei pieces mit nur einem Attribut. In einem solchen Szenario könnte chaining sehr schnell zu einem massiven Problem werden. Aber vielleicht sollte man einfach grundsätzlich von Tabellen mit mehr als 255 Spalten Abstand nehmen: sie bereiten wenig Freude.

Nachtrag 21.04.2017: in einem weiteren Artikel zeigt der Autor, dass mit 12.2 auch updates zu intra-block-chaining führen, was mir eine günstige Entwicklung zu sein scheint.

Nachtrag 22.06.2017: Jonathan Lewis spricht das Thema in seinem Scratchpad an und verweist auf die erwähnten Artikel.

Interval-Reference Partitionierung in 12c

Früher einmal habe ich meine Blog-Beiträge selbst erdacht und geschrieben - inzwischen gebe ich sie gerne in Auftrag oder lasse sie in Auftrag geben. So auch hier: in der letzten Woche war Markus Flechtner von Trivadis bei uns im Haus und hat uns in die dunklen Geheimnisse von Oracle 12 eingeweiht. Viele Fragen blieben nicht offen, aber einer meiner Kollegen hatte ein paar Detailfragen zum Thema Interval-Reference-Partitionierung und row movement. Ich hätte es wahrscheinlich bei der Antwort "das ist ein weites Feld" bewenden lassen und vielleicht noch ein paar haltlose Versprechungen gemacht, gelegentlich mal einen Blick darauf zu werfen. Nicht so der Herr Flechtner, der daraus gleich einen Artikel Interval-Reference-Partitionierung: Partition-Merge und Row-Movement gemacht hat. Darin finden sich unter anderem folgende Beobachtungen:

• zur Erinnerung: beim reference partitioning erbt eine child Tabelle die Partitionierungs-Charakteristiken der parent Tabelle. Und beim interval partitioning werden erforderliche Partitionen nach Bedarf auf Basis einer vorgegebenen Ranges-Größe (oder Intervall-Angabe) erzeugt.
• die für die child-Tabelle angelegten Partitionen korrespondieren exakt mit denen der parent-Tabelle: auch die generierten Namen der Partitionen sind identisch.
• ein Merge für Partitionen der parent-Tabelle wird automatisch an die child-Tabelle propagiert. Allerdings sind die Namen auf parent- und child-Ebene dann nicht mehr identisch.
• ein Merge auf child-Ebene ist nicht möglich.
• wie üblich führt die Merge-Operation zu einer Invalidierung der Indizes, sofern nicht die Klausel UPDATE INDEXES verwendet wird.
• um Verschiebungen von Datensätzen über Partitionsgrenzen zu erlauben, muss row movement aktiviert sein: dabei muss es erst auf child-, dann auf parent-Ebene aktiviert werden (bei umgekehrter Reihenfolge ergibt sich ein Fehler "ORA-14662: row movement cannot be enabled".

Viele weitere Fragen würden mir in diesem Zusammenhang auch nicht mehr einfallen (außer vielleicht, ob sich split partition entsprechend verhält, wovon ich erst einmal ausgehe). In jedem Fall ein herzlicher Dank an den Autor für die rasche Beantwortung dieser Fragen.

Nicht deterministische JDBC-Anmeldeprobleme mit Oracle 11.2.0.3

Im Lauf der Woche bin ich einem Problem beim Zugriff auf eine Oracle-Datenbank via JDBC begegnet, das eine umfangreichere Schilderung verdient hätte - wenn Uwe Küchler diese Schilderung nicht schon vor einigen Jahren in seinem Blog unter Berücksichtigung aller relevanten Details durchgeführt hätte. Das Problem lag darin, dass Anmeldungen in manchen Fällen problemlos erfolgten, dann aber wieder in Timeouts liefen, ohne dass sich dafür auf Netzwerkebene eine Erklärung finden ließ. Verantwortlich ist die Verwendung von Zufallszahlen beim in 11g eingesetzten Authentifizierungsverfahren - aber ich spare mir jede weitere Erklärung, da sich alles, was man dazu wissen muss, im verlinkten Artikel findet. Dafür noch mal mein Dank an den Autor.

Zur Semantik des USE_NL Hints

Ich erinnere mich, dass Jonathan Lewis diesen Punkt schon häufiger erwähnt hat, aber offenbar hatte er tatsächlich noch keinen Artikel dazu geschrieben, und dies jetzt nachgeholt: häufig sieht man in Oracle SQL-Queries Hints der folgenden Form:

use_nl(t1 t2)

Und es gibt wohl in der Tat viele Verwender, die davon ausgehen, dass man den Optimizer damit anweist, einen NESTED LOOPS Join zu verwenden, bei dem t1 unmittelbar mit t2 verknüpft wird, wobei t1 die driving table ist, also zuerst abgefragt wird. Das ist allerdings nicht der Fall. Tatsächlich ist der Hint nur eine Kuzform für:

use_nl(t1) use_nl(t2)

Der Hint sagt nichts darüber, in welcher Reihenfolge der Zugriff erfolgt - und wenn weitere Tabellen im Join beteiligt sind, kann es durchaus dazu kommen, dass t1 und t2 nur mittelbar miteinander verknüft werden. Um die Ausführungsreihenfolge zu bestimmen, benötigt man zumindest einen weiteren leading-Hint. Das Beispiel macht einmal mehr deutlich, dass die Verwendung von Hints deutlich komplzierter ist, als sie manchmal auszusehen scheint.

Redundante Prädikate zur SQL Optimierung im SQL Server

Da ich nicht so oft lobende Erwähnungen auf anderen Webseiten erhalte, will ich nicht darauf verzichten, diese hier zu verlinken: mein alter Freund und ehemaliger Kollege Andrej Kuklin hat im SDX Blog einen Artikel veröffentlicht, der sich damit beschäftigt, wie man Queries im SQL Server durch die Ergänzung eigentlich redundanter Prädikate optimieren kann. In seinem Beispiel läuft eine Query mit einem Inner Join schnell, so lange über eine gegebene Variable auf ein bestimmtes Datum eingeschränkt wird, wobei die entsprechende Spalte auch in der Join-Bedingung erscheint. Andrejs (und meine) Annahme ist, dass hier - so wie bei Oracle - ein Fall von transitive closure vorliegt: die auf der einen Seite angegebe Einschränkung wird dupliziert und auch auf die zweite Menge/Tabelle angewendet:

explain plan for
SELECT
    fp.*
   ,ft.TradeID
   ,ft.IsCompleted
   ,ft.Amount
FROM
    FactPosition fp
INNER JOIN FactTrade ft ON
      ft.DateID=fp.DateId
      AND ft.PositionID=fp.PositionId
WHERE
    fp.DateId=20151028;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|*  1 |  HASH JOIN                   |                 |  2309 |   101K|    26   (4)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| FACTPOSITION    |  1000 | 24000 |     9   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | FACTPOSITION_PK |  1000 |       |     5   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| FACTTRADE       |  2000 | 42000 |    16   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | FACTTRADE_PK    |  2000 |       |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("FT"."DATEID"="FP"."DATEID" AND "FT"."POSITIONID"="FP"."POSITIONID")
   3 - access("FP"."DATEID"=20151028)
   5 - access("FT"."DATEID"=20151028)

Im Plan sieht man, dass die DateId-Einschränkung für beide Tabellen hergezogen werden kann (Step 3 und Step 5). Wird jedoch statt der Angabe der Variable (oder wie bei mir: eines Literals) eine Subquery verwendet ("WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate)"), dann kann der Optimizer diese Einschränkung im SQL Server und in Oracle nicht auf die zweite Tabelle anwenden und muss auf dieser einen full table scan durchführen, obwohl der Index-Zugriff deutlich schneller wäre. Um dem Optimizer die zusätzliche Information zu liefern, muss in diesem Fall die Subquery dupliziert werden (also: "WHERE fp.DateId=(SELECT MAX(DateId) FROM DimDate) AND ft.DateId=(SELECT MAX(DateId) FROM DimDate);"). Inhaltlich würde ich annehmen, dass der Optimizer eine solche Umformung auch selbständig ergänzen könnte, aber in den aktuellen Versionen von SQL Server und Oracle tut er das offenbar noch nicht. Interessant ist jedenfalls mal wieder zu sehen, wie ähnlich sich relationale Datenbanken in vielen Fällen verhalten.

Anders als meine Notiz hier zeichnet sich Andrejs Artikel übrigens durch die detaillierte Präsentation des Beispiels und der sich ergebenden SQL Server Pläne aus.

Maria Colgan antwortet bei AskTom

Ein vielversprechender Start ins Jahr 2017: Maria Colgan gehört jetzt neben Chris Saxon und Connor McDonald zum Team bei AskTom. Außerdem hat sie damit angefangen, in ihrem eigenen Blog zu schreiben. Da es vermutlich kaum jemanden gibt, der mehr über den Optimizer (oder ind In-Memory-Optionen) weiß als Frau Colgan, ist das eine günstige Entwicklung.