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