Montag, Juli 10, 2017

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.

Mittwoch, Juli 05, 2017

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.

Dienstag, Juni 27, 2017

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.

Dienstag, Juni 13, 2017

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

Freitag, Juni 09, 2017

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.