first_rows und first_rows_n

Jonathan Lewis weist in seinem Blog darauf hin, dass die Optimizer-Modi first_rows und first_rows_n deutlich weniger gemein haben, als man vielleicht annehmen könnte. Während first_rows seit langem nur noch aus Gründen der Abwärtskompatibiltät unterstützt wird und diverse Heuristiken benutzt, um die Optionen des Optimizers einzuschränken, arbeiten die first_rows_n-Varianten strikt kostenbasiert, ohne dem Optimizer massiv in die Arbeit zu pfuschen. Tatsächlich gibt es allerdings einen Fall, in dem first_rows_n sich so verhält wie first_rows, nämlich die - durchaus merkwürdige - Variante first_rows(0). In diesem Fall wechselt der Optimizer intern tatsächlich auf die Strategie first_rows. Allerdings gibt der Herr Lewis zu Recht zu bedenken: "Mind you, if you only want the first zero rows of the result set there’s a much better trick you can use to optimise the query – don’t run the query."

Für mich persönlich brachte der Artikel noch mal die Erinnerung, dass der Session-Parameter und der Hint first_rows_n eine leicht abweichende Parametrisierung besitzen (die auch explizit dokumentiert ist): während der Parameter nur die Werte 1, 10, 100 und 1000 als gültig betrachtet, kann man im Hint für n jeden beliebigen ganzzahligen Wert angeben. Nachlesen kann man die Details in meinen Kommentaren zum Artikeln und der erläuternden (man könnte auch sagen: richtig stellenden) Antwort des Autors.

Der Vollständigkeit halber hier noch ein Link auf meinen älteren Versuch, dem Costing für first_rows_n auf die Spur zu kommen, zum dem mir damals Randolf Geist die Pointe geliefert hatte (man könnte auch sagen: die Richtigstellung).

Nachtrag 02.11.2014: in einem weiteren Artikel zeigt Jonathan Lewis ein Beispiel, in dem first_rows_10 einen HASH JOIN großer Tabellen hervorruft, weil die cardinalities falsch bestimmt werden (nämlich als 10 für beide Eingangsmengen).

Temp Table Transformation

Randolf Geist erläutert in seinem Blog das Verhalten der Temp Table Transformation, die hauptsächlich (aber nicht ausschließlich) dann anzutreffen ist, wenn eine CTE im Plan mehrfach referenziert wird und mindestens ein (Filter- oder Join-) Prädikat enthält, und weist insbesondere darauf hin, dass diese Transformation nicht kostenbasiert erfolgt, sondern automatisch. In Fällen, in denen eine zusätzliche Bedingung jenseits des CTE-Kontexts eine dramatische Reduzierung der Ergebnismenge hervorrufen würde, ist diese Materialisierung demnach ausgesprochen kontraproduktiv. Darüber hinaus rufen die materialiserten CTEs in solchen Fällen seit 11.2.0.4 offenbar extrem abwegige Cardinality-Schätzungen hervor, die in komplexeren Plänen sehr unglückliche Konsequenzen hervorrufen könnten. Insgesamt scheint es gute Gründe zu geben, die Materialisierung von CTEs sehr genau zu beobachten und ggf. durch den (nach wie vor undokumentierten) Inline-Hint zu vermeiden.

Nachtrag 22.05.2015: in einem Folgeartikel zeigt Randolf, dass die temp table transformation offenbar auch dazu führt, dass alle Spalten der CTE materialisiert werden und nicht nur die, die im Rahmen der folgenden Schritte tatsächlich benötigt werden. In vielen anderen Fällen ist Oracle sehr gut darin, die projection zu optimieren (Ausnahme: MERGE), aber hier ist das offenbar nicht so.

Histogramme, Cardinality und der Optimizer Mode first_rows_100

Eine interessante Antwort auf die Frage nach unplausiblen Cardinalities, die sich trotz frequency histogram ergeben, hat Jonathan Lewis in einem OTN-Forums-Thread gegeben. Der vom Fragesteller gelieferte handliche Testfall zeigte sehr deutlich, dass in seinem System mit 11.2.0.4 für einfache Queries sehr merkwürde Cardinalities ermittelt wurden, obwohl das vorhandene Histogramm die tatsächliche Verteilung exakt abbildete. Diverse Beiträger konnten nur feststellen, dass das Verhalten in ihren Systemen (zwischen 11.2.0.1 und 12.1.0.2) nicht reproduzierbar war, aber der Herr Lewis konnte das fehlende Puzzle-Teil benennen: wenn man den Optimizer-Mode auf first_rows_100 umstellt, werden aus den plausiblen Schätzungen augenblicklich recht nutzlose Zahlen. Ich pflege die Wirkung der first_rows_n-Einstellungen regelmäßig zu unterschätzen und sollte mir endlich merken, wie invasiv dieser Modus tatsächlich ist.

Index Reorganisation

Franck Pachot beantwortet in seinem Blog eine Frage, die mir gelegentlich durch den Kopf gegangen ist, ohne dass ich mich ernsthaft um ihre Beantwortung gekümmert hätte: welche Form der Reorganisation eines Index ist unter welchen Voraussetzungen die passendste? Dazu liefert er einen einfachen Testfall, zu dem er die relevanten Statistiken zum Index und zur Ressourcen-Nutzung im Rahmen der Reorganisation beisteuert. Die Ergebnisse lauten ungefähr:

• COALESCE: eine Online-Operation, die die leaf blocks defragmentiert, aber nicht wieder frei gibt, sondern für die zukünftige Verwendung in der Index-Struktur hält.
• SHRINK SPACE COMPACT: ebenfalls eine Online-Operation, die ungefähr das gleiche leistet wie coalesce, aber etwas mehr LIOs hervorruft und die leaf Blocks etwas effektiver aufräumt (Status: FS4 statt FS2).
• SHRINK SPACE: wieder eine Online-Operation mit nahezu identischer Ressourcen-Nutzung wie das shrink space compact, aber in diesem Fall werden die geräumten Blocks tatsächlich wieder freigegeben.
• REBUILD: erfordert ein share lock, das konkurrierende DML-Operationen blockiert, benötigt aber weniger Ressourcen und führt zu einer vollständig aufgeräumten Index-Struktur: in diesem Fall wird auch die Index-Höhe auf den passenden Wert zurückgeführt - was im Fall des kompletten Neuaufbaus keine besondere Überraschung darstellt.
• REBUILD ONLINE: ein EE-Feature, das - wie der Name vermuten lässt - den Neuaufbau ohne umfangreicheres Locking ermöglicht (in der Regel auf Kosten erhöhter Ressourcen-Nutzung.

Insbesondere die Tatsache, dass das shrink space mehr leistet als das shrink space compact ist ein Aspekt, der mir schon mehrfach entfallen ist..

Advanced Index Compression in 12c

Leider schaffe ich es derzeit nicht, mir eigene Gedanken zu machen, so dass ich mich darauf beschränken muss, anderer Leute Ergebnisse zu reproduzieren. Einer der Autoren, bei denen ich das recht gerne mache, ist Richard Foote, der inzwischen wieder sehr regelmäßig neue Artikel schreibt. Zwei davon betreffen die in 12c eingeführte Advanced Index Compression:

• Index Compression Part V: 12c Advanced Index Compression (Little Wonder): erläutert die Hintergünde dieser neuen Option (die eigens lizenziert werden muss). Bisher war es bei der klassischen Index--Komprimierung, bei der Duplikate in den führenden Spalten eines Index durch Referenzen auf eine Prefix-Sektion im Block ersetzt werden, ohne Weiteres möglich, den Index durch die Komprimierung zu vergrößern - dann nämlich, wenn es zu wenig Wiederholungen gab, um den Overhead der eingeführten Symboltabelle zu rechtfertigen. Bei der Advanced Index Compression wird dieses Problem auf zwei Ebenen behandelt: zum einen automatisiert die Option die Bestimmung eines sinnvollen Komprimierungsgrads, zum anderen wählt sie die Komprimierung blockspezifisch aus, so dass einzelne Bereiche des Index, die sich gut komprimieren lassen, besser gepackt werden als andere Bereiche mit wenigen Duplikaten.
• Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl): beschäftigt sich mit den technischen Details der Implementierung und mit der Repräsentation der Komprimierung im Block-Dump. Der zentrale Punkt sind die Einträge kdxlepnro (nro = number of rows?) und kdxlepnco (nco = number of columns?) im Index-Block-Dump, von denen der erste die Anzahl der Einträge in der Prefix-Tabelle angibt und der zweite die Anzahl komprimierter Spalten. Bei kdxlepnco taucht zusätzlich in Klammern die Angabe (Adaptive) auf, die auf die Verwendung des neuen Features hinweist.
• Index Advanced Compression vs. Bitmap Indexes (Candidate): beantwortet die Frage, ob die Advanced Index Compression die Nutzung von Bitmap Indizes uninteressant macht, mit einem schallenden Nein. Während B*Tree-Indizes sich nur auf Block-Ebene komprimieren lassen, kann ein Bitmap-Index die Werte der gesamten Tabelle zusammenfassen. Bei geeigneter Werte-Clusterung kann ein Bitmap-Index extrem kompakt werden - und sehr viel kleiner als jeder noch so gut gepackte B*Tree-Index.

Weitere Artikel sind angekündigt, die ich dann voraussichtlich ebenfalls hier zusammenfasse werde. Grundsätzlich sieht das wie eine sehr sinnvolle Funktionalität aus - ich hatte mich in der Vergangenheit häufiger gefragt, wieso ich zunächst einen plausiblen Kompressionsgrad ermitteln musste (per ANALYZE INDEX), ehe ich diesem Vorschlag folgte, da ich an dieser Stelle recht wenig Spielraum für individuelle Anpassungen und vom Vorschlag abweichende Entscheidungen sah.

Detailsinformationen zu den system statistics

Franck Pachot liefert im dbi services Blog ein nettes Skript zur Analyse der Systemstatistiken, auf denen das Costing des Optimizers basiert. Dabei ergänzt er zu den Basisinformationen aus (SYS.)AUX_STATS$ diverse Angaben zu ihrer Berechnung, die man sich ohne Skript mehr oder minder mühsam zusammensuchen muss.

Optimierung für pg_dump und pg_restore

Josh Berkus erwähnt in seinem Blog einige Optionen, mit denen man die Performance der postgres-Export und Import-Tools pg_dump und pg_restore verbessern kann. Die Liste enthält unter anderem folgende Punkte:

• Parallelisierung über die -j multiprocess Option (für pg_restore und seit 9.3 auch für pg_dump).
• Komprimierung des Dumps bei der Erstellung.
• Deaktivierung der Binär-Replikation im Zielsystem während des Imports.
• Anpassungen der postgresql.conf des Ziel-Clusters zur Unterstützung einer schnellen Wiederherstellung. Die Wiedergabe der genannten Optionen erspare ich mir.

Da das Importieren von postgres-Dumps ohne Optimierung eher zäh abläuft, werde ich diese Hinweise bei nächster Gelegenheit berücksichtigen.

Read Privileg in 12.1.0.2 (ohne Select for Update)

Zu den eher unverständlichen Entscheidungen, die man bei Oracle im Zusammenhang der Definition von Privilegien gemacht hat, gehörte sicherlich die Zuordnung des Rechts zur Ausführung der Operation SELECT FOR UPDATE zum einfachen SELECT-Privileg. Dadurch konnten Benutzer allerlei Unfug anstellen, weil die Operation recht massive Sperren erzeugt. In 12.1.0.2 gibt es jetzt das neue READ-Privileg, das SELECT FOR UPDATE explizit nicht mehr enthält. Den Hinweis auf diese Ergänzung habe ich bei Gary Myers gefunden.

SQL Developer Tipps

Nachdem ich im September bei sehr vielen Artikeln der Reihe 30 Posts in 30 Days von Jeff Smith den Eindruck hatte, dass da Features erwähnt werden, die ausgesprochen nützlich wären, wenn ich sqlplus gelegentlich beiseite legen würde, scheint's mir angemessen, den Index-Artikel zur Serie zu verlinken. Der SQL Developer ist wirklich ein großartiges Tool - insbesondere in Anbetracht seines Preises.

Log File Sync, Log File Parallel Write und Durchschnittswerte

Nikolay Savvinov erläutert in seinem Blog die Probleme, die sich ergeben, wenn man die im AWR angezeigten Duchschnittswerte der Events Log File Sync (LFS) und Log File Parallel Write (LFPW) allzu ernst nimmt. Dazu noch mal eine minimale Definition der Events:

• Log File Sync: ist das Event, auf das User-Sessions warten, die ein Commit abgesetzt haben, das vom LWGR verarbeitet werden soll.
• Log File Parallel Write: ist das Event auf das der LWGR wartet, der die Inhalte des log Buffers auf die Platte schreiben will. Dies ist demnach ein I/O-Event. Da der LWGR mehrere Session-Commits in einem Gruppen- (oder Piggyback-) Commit zusammenfassen kann, können sich für LFPW verlängerte Wartezeiten ergeben, die dann zu verlängerten LFS-Waits führen.

In einem Single User System liegen LFS und LFPW recht nah beieinander, aber je mehr Sessions gleichzeitige Commits absetzen, desto mehr Gruppen-Commits ergeben sich und desto stärker werden die durchschnittlichen LFS-Waits verlängert (die LFPW-Waits natürlich auch, aber der Effekt betrifft LFS stärker, da jedes LFPW-Waits mehrere LFS-Waits hervorruft). AWR-Durchschnittswerte sind in diesem Fall deutlich weniger aussagekräftig als ASH-Daten oder Wait-Histogramme. Ich spare mir an dieser Stelle die komplette Nacherzählung der Untersuchung (und ihrer Schlussfolgerungen), ergänze aber noch den auch beim Herrn Savvinov erwähnten Link auf einen älteren Artikel von James Morle, der sich dem gleichen Thema widmet.

Tablespace verkleinern

Jonathan Lewis hat dieser Tage zwei Artikel zum Thema der Verkleinerung von Tablespaces veröffentlicht:

• http://jonathanlewis.wordpress.com/2014/09/19/shrink-tablespace-2/: weist darauf hin, dass es in aktuellen Releases sinnvoll ist, zugehörige Indizes vor einer Move-Operation einer Tabelle als unusable zu markieren, da die Segmente in diesem Fall sofort gelöscht werden (nach der Move-Operation findet diese Löschung ohnehin statt, da die Index-rowid-Referenzen durch die Bewegung der Tabelle unbrauchbar werden). Diese Löschung erlaubt dann die Tabelle "weiter vorn" im data file abzulegen, die Wahrscheinlichkeit ungenutzter Lücken nimmt ab und die HWM kann nach der Reorganisation weiter nach unten verschoben werden. Dazu gibt es - wie üblich - ein kleines Beispiel.
• http://jonathanlewis.wordpress.com/2014/10/01/shrink-tablespace-3/: zeigt, dass die Verschiebung der Tabellen "nach vorne" auch in system managed tablespaces nicht in jedem Fall ohne Probleme funktioniert - insbesondere wenn die Extents nicht mit uniform size angelegt wurden -, weil Oracle eine elaboriertere Logik der Nutzung freien Speicherbereiche verwendet und zunächst versucht, bereits teilweise gefüllte 1mb-Bereiche zu füllen.

Nachtrag 09.10.2014: Vor ein paar Wochen hatte Franck Pachot ein auf x$ktfbue zugreifendes  (und damit unhandliche Operationen auf dba_extends vermeidendes) Skript zur Generierung von Statements zur Verkleinerung von data files veröffentlicht. Bei Tom Kyte habe ich vor vielen Jahren etwas Ähnliches ausgeborgt, aber da ich nicht mehr als DBA praktiziere, brauche ich dergleichen nicht mehr oft.

Nachtrag 17.11.2014: Noch ein weiterer Artikel von Jonathan Lewis zum Thema mit einer Aufzählung pragmatischer Hinweise (zum Teil in Überschneidung mit den vorangehenden Artikeln):

• der recylebin sollte geleert werden, ehe die Reorganisation startet.
• Indizes sollten vorab als unusable markiert werden (um diese Segmente zu löschen, die ohnehin invalidiert werden).
• man sollte über die Wirkungen der minimum extent size nachdenken.
• nicht immer ist die "highest object first" Strategie die sinnvollste.
• die Verschiebung mehrerer kleinerer Objekte kann Raum für größere Objekte schaffen.
• unter Umständen ist die Verschiebung aller Objekte in einen neuen TS das effektivste Verfahren.