Dienstag, Juni 30, 2015

Permanente Deaktivierung einer SQL Plan Directive

Franck Pachot erklärt, wie man dafür sorgt, dass eine SQL Plan Directive dauerhaft deaktiviert bleibt, um den Overhead des Samplings zu vermeiden. Das Problem dabei ist, dass die Direktive nach einer vollständigen Löschung erneut erzeugt werden kann. Um das zu verhindern, muss die Direktive deaktiviert, aber ihre Löschung verhindert werden - die Befehlsfolge dazu lautet:

exec dbms_spd.alter_sql_plan_directive(14130932452940503769,'ENABLED','NO');
exec dbms_spd.alter_sql_plan_directive(14130932452940503769,'AUTO_DROP','NO');

Grundsätzlich sind die Direktiven jedenfalls ein Thema, das man für 12c klar im Blick behalten muss.

Sonntag, Juni 28, 2015

Cardinality Schätzungen für TEMP TABLE TRANSFORMATION

Randolf Geist hat vor einigen Wochen in zwei Artikeln weitere Details zum Verhalten der Cardinality-Schätzungen im Rahmen der TEMP TABLE TRANSFORMATION geliefert - in Ergänzung zu zwei (geringfügig) älteren Artikeln, die ich hier gelegentlich verlinkt habe und in denen vor allem darauf hingewiesen wurde, dass die Transformation nicht kostenbasiert ist, sondern automatisch angewendet wird (sofern die erforderlichen Voraussetzungen erfüllt sind), dass sie zu einer Materialisierung aller Spalten der CTE führt - und nicht nur der tatsächlich relevanten (und somit unnütze Arbeit hervorrufen kann) -, und eben dass sie unerfreuliche Auswirkungen auf die Bestimmung der Cardinalities hat. Der Detaillierung dieses letzten Punktes dienen die beiden folgenden Artikel:
  • Temp Table Transformation Cardinality Estimates - 1: liefert ein relativ einfaches Self-Join-Beispiel, bei dem die Query mit einem Inline-Hint als Hash Join mit zweifacher Verwendung der Basis-Tabelle ausgeführt wird und ohne den Hint als Hash Join der Materialisierung dieser Basistabelle. Der Join erfolgt dabei über einen Ausdruck (id + 1), was die Aufgabe des Optimizers im Fall der Materialisierung offenbar komplizierter macht: während die Inline-Variante die Cardinality 10000 liefert, ergibt sich für den Fall mit Temp Table Transformation eine Cardinality von 1. Tatsächlich ist die 1 laut CBO-Trace eine aufgerundete 0, denn offenbar verliert der Optimizer durch die Transformation diverse statistische Informationen, insbesondere zu den Spalten-Statistiken. Besonders unerfreulich ist dabei, dass sich die Fehler in der Schätzung - zumindest vor 12c - nicht mit Hilfe des opt_estimate Hints korrigieren lassen. In 12c wird der Hint dann nicht mehr übergangen, führt aber zu anderen Merkwürdigkeiten. Für das vorliegende Beispiel lässt sich das Problem durch extended statistics für den Ausdruck id + 1 beheben, aber es bleibt festzuhalten das die Temp Table Transformation massive Auswirkungen auf die Cardinality-Schätzungen haben kann, und dass die Korrektur der dadurch entstehenden Fehler vor 12c nicht mit Hilfe der klassischen Hints durchgeführt werden kann.
  • Temp Table Transformation Cardinality Estimates - 2: erweitert das Beispiel des ersten Artikels um eine zusätzliche Filterung der id-Spalte, der auf beiden Seiten des Joins 99% der Daten zum Opfer fallen. Während die inline-Version des Ausführungsplan dieser Veränderung der Datenmengen Rechnung trägt, wird die Filterung im Fall der Temp Table Transformation komplett ignoriert. Insofern kann die Transformation in vielen Fällen zu einer deutlichen Überschätzung der Cardinalities führen. Da die Filterung im Fall der Transformation nicht beim step des Tabellenzugriffs erfolgt, sondern erst im folgenden view-step, ergeben sich im Exadata-Fall Zugriffe, die eigentlich vermeidbar wären.
Ich werde wohl in Zukunft häufiger als bisher auf den Inline-Hint zugreifen, um zu bestimmen, wann die Temp Table Transformation den Optimizer in die Irre führt.

Donnerstag, Juni 25, 2015

Leere Index-Blocks nach einem Rollback

Im OTN-Forum wurde vor einigen Tagen die Frage gestellt, wie es dazu kommen kann, dass ein Index nach einer abgebrochenen großen Update-Operation zahlreiche vollständig leere Blocks enthält. Dazu gab es im Forum ein paar kluge Antworten - und eine weniger kluge von mir, die zwar das gundsätzliche Problem benannte (nämlich die Tatsache, dass Index-Einträge nicht direkt gelöscht werden, sondern als gelöscht markiert), aber in der Erläuterung so unvollständig blieb, dass ich selbst nicht mehr sagen kann, worauf ich eigentlich hinaus wollte. Stefan Koehler lieferte ein schönes Test-Beispiel, das den Fall exakt reproduziert, aber darüber hinaus hat Richard Foote den Fall zum Anlass genommen, ein paar neue Artikel in seinem Blog zu veröffentlichen:
  • Empty Leaf Blocks After Rollback Part I (Empty Spaces): erläutert das grundsätzliche Problem: im Zusammenhang mit Indizes setzt sich ein Update aus einem Delete und einem Insert zusammen. Die ursprünglichen Einträge werden als gelöscht markiert und die neuen Einträge an anderer Stelle im Index eingefügt, was zur Entstehung neuer Index Leaf Blocks führen kann. Ein Rollback stellt dann die ursprünglichen Einträge wieder her, während die neuen Einträge als gelöscht markiert werden: es handelt sich also um eine logische Wiederherstellung, die Änderungen der Index-Struktur - wie Block Splits - nicht zurücksetzt. Dazu gibt es dann ein paar Beispiele: zunächst wird das Verhalten eines non-unique Index untersucht, bei dem alle Id-Werte um 1 erhöht werden. Dieser Fall führt zu sehr vielen 50:50-Splits und dazu, dass der Index auf die doppelte Größe wächst, wobei alle Blocks nur noch zur Hälfte gefüllt sind. Ein folgendes Update der Id-Werte um 10000 führt dazu, dass neue Werte jenseits des Bereichs der bisher verwendeten Einträge entstehen (durch 90:10-Splits) - nach dem folgenden Rollback sind diese neuen Blocks dann wieder völlig leer, bleiben aber in der Index-Struktur. Für folgende Zugriffe kann das ein Problem sein - insbesondere für die üblicherweise sehr effizienten INDEX FULL SCAN (MIN/MAX) Operationen, die all diese leeren Blocks überprüfen müssen, um den tatsächlichen Maximalwert zu finden. Ein ALTER INDEX ... COALESCE entfernt die leeren Leaf-Blöcke aus der Struktur.
  • Empty Leaf Blocks After Rollback Part II (Editions of You): erklärt zunächst, dass sich für die beiden Beispiele des vorangehenden Artikel ähnliche Effekte ergeben hätten, wenn statt des Rollbacks ein Commit erfolgt wäre: auch dann wäre die Index-Struktur durch das Update deutlich gewachsen. Es folgt eine Darstellung des Verhaltens beim Einsatz eines unique index. Zunächst zeigt sich, dass der unique index mehr Einträge pro Block unterbringen kann (was damit zusammenhängt, dass Oracle im Fall des nonunique Index die rowid als zusätzliches Attribut im Index speichert, um die Einträge intern eindeutig zu machen). Dann wird das Verhalten beim Update der Id um den Wert 1 beleuchtet: da Einträge im Fall des eindeutigen Index wiederverwendet werden können, wächst dieser Index im Rahmen des Updates nicht, sondern behält seine ursprüngliche Größe. Anders verhält es sich beim Update um 10000, da in diesem Fall keine Wiederverwendung existierender Einträge erfolgen kann.
Damit hätte sich das OTN-Forum mal wieder nützlich gemacht.

Samstag, Juni 20, 2015

Grundlagen des kostenbasierten Optimizers

Jonathan Lewis hat für AllThingsOracle eine neue Serie Basics of the Cost Based Optimizer gestartet und liefert damit vermutlich eine neue Standardreferenz zum Thema. Allerdings weist der Autor darauf hin, dass die Serie eher einführenden Charakter besitzt und nicht allzu sehr auf die technischen Details eingeht, weshalb ich meine Zusammenfassungen eher knapp zu halten gedenke:
  • Basics of the Cost Based Optimizer – Part 1: beschreibt die Grundlagen der Arbeit des Optimizers: um einen möglichst effektiven Zugriff zu ermöglichen erstellt er ein numerisches Modell basierend auf Informationen über den Daten (= Objektstatistiken), die Leistungsfähigkeit des Rechners (= Systemstatistiken) und die verfügbareb Ressourcen auf dem Rechner (= Konfiguration der Datenbank/Instanz). Dieses Modell ist natürlich nicht immer eine exakte Abbildung der realen Gegebenheiten und aus diesen Differenzen ergeben sich dann die Fehlentscheidungen des Optimizers. Dazu gibt es dann ein umfangreiches Beispiel eines Joins zweier Tabellen, die unter Verwendung unterschiedlicher Strategien miteinander gejoint werden können.
  • Basics of the Cost Based Optimizer – Part 2: liefert Daten zu den in Teil 1 vorgestellten Objektdefinitionen (DDL) und zeigt, dass das Standardmodell relativ leicht zu Fehleinschätzungen führt, und wie man diese durch Konfigurationsänderungen korrigieren kann. Dabei enthalten die Daten eine Ungleichverteilung der Werte (skew). Im Beispiel werden die Optimizer-Kosten der Zugriffe sehr detailliert mit den zugehörigen I/O-Operationen auf Indizes und Tabellen verglichen. Zudem wird der durch ASM verzerrte Clustering Factor eines Index erläutert. Nach Korrektur des CF (durch Verwendung eines angemessenen table_cached_blocks-Parameters) verzichtet der Optimizer auf einen zunächst vorgesehenen Index Hash Join und verwendet stattdessen einen einfachen Index Range Scan mit folgendem Tabellenzugriff.
Ich gehe davon aus, dass in der Serie noch weitere Artikel folgen werden, und diese werde ich dann voraussichtlich hier ergänzen.

Donnerstag, Juni 18, 2015

Typen des Dynamic Sampling Hint

Jonathan Lewis weist - erneut - darauf hin, dass der dynamic sampling Hint in zwei Versionen existiert: als cursor level Hint und als statement level Hint, und dass die Anzahl der gesampelten Blocks von der Version abhängt: Level 4 auf cursor level verwendet 64 Blocks als Sample - sofern bestimmte Voraussetzungen gegeben sind, während Level 4 auf table level 256 Blocks verwendet, und das unabhängig von weiteren Voraussetzungen. Deshalb plädiert der Autor dafür, den Typ explizit zu erwähnen, wenn über dynamic sampling gesprochen wird. Darüber hinaus liefert er einen kleinen PL/SQL Block, der ein CBO Trace (Event 10053) erzeugt, um die unterschiedlichen Sample-Größen der beiden Verfahren anzuzeigen. Die Resultate zeigen, dass curosr level sampling in fast jedem Fall ein kleineres Sample verwendet und auf Level 1 nur unter ganz bestimmten Voraussetzungen überhaupt durchgeführt wird.

Darüber hinaus verweist Jonathan noch auf einen Artikel von Mohamed Houri, der sich mit der Unterscheidung von Fällen, in denen kein Sample erzeugt wurde, und Fällen, in denen das Sample ignoriert wurde, beschäftigt. Anlass für beide Artikel war dabei ein OTN-Thread, in dem eine recht intensive Diskussion des Sampling-Verhaltens erfolgte (unter Teilnahme von Mohamed, Jonathan, Randolf Geist, Dom Brooks, Stefan Koehler), und der schon für sich gesehen die Leküre lohnt.