Truncate-Verhalten in Oracle

Jonathan Lewis hat zuletzt zwei Artikel zum Thema Truncate veröffentlicht, in denen er ein paar grundsätzliche Erläuterungen zu Verhalten gibt:

• Truncate: erzeugt kleine Mengen von undo und redo, da nur die Metadaten für Space-Management und Dictionary Informationen protokolliert werden müssen. Diese Aussage überprüft der Autor durch einen Test, bei dem er die Sequenz "switch logfile, truncate table, dump logfile" ablaufen lässt. Ein interessantes Detail in diesem Zusammenhang ist, dass die data_object_id eines zugehörigen Index nach dem Truncate niedriger ist als die data_object_id der Tabelle, was damit zu tun hat, dass der Index vor der Tabelle truncated wird und somit zuerst eine neue Id erhält. Vor allem aber liefert der Artikel eine Übersicht zur (vermuteten) Schrittabfolge beim Truncate mit den erforderlichen Space-Management-Operationen (für Index und Tabelle) und den Änderungen der Dictionary Objekte. Auffällig ist, dass die Zahl der commits im Rahmen des Tests höher ausfällt als erwartet, aber es sieht so aus, dass die eigentliche Truncate-Operation in einer einzigen Transaktion zusammengefasst ist, während die übrigen Transaktionen weitere Aufräumarbeiten beim Space Management enthalten.
• Truncate – 2: zeigt, dass man das implizite commit eines Truncate mit Hilfe einer autonomen Transaktion daran hindern kann, vorangehende DML-Operationen festzuschreiben (was nicht weiter überrascht).

Indizes löschen

Jonathan Lewis hat - wieder einmal angeregt durch entsprechende Fragen in den OTN-Foren - einen Artikel zur Frage geschrieben: welche Informationen muss man analysieren, um bestimmen zu können, ob ein Index gefahrlos gelöscht werden kann? Bisher ist es zwar nur ein Artikel, aber da ein zweiter angekündigt ist, mache ich gleich eine Aufzählung daraus:

Index Usage

Erklärt zunächst das grundsätzliche Probleme extremer Indizierung: zwar wird - potentiell - die Treffsicherheit der Zugriffe erhöht, aber man bezahlt dafür mit einer massiven Vergrößerung von undo/redo für die Index-Maintenance. Insofern erscheint es naheliegend, überflüssige Indizes zu löschen - also solche, die in den Ausführungsplänen des Optimizers nicht erscheinen -, aber dabei ergeben sich oft Schwierigkeiten:

• die Tatsache, dass ein Index benutzt wird, bedeutet nicht unbedingt, dass er auch benutzt werden sollte. Möglicherweise übersieht der Optimizer einen geeigneteren Index.
• das bedeutet im Gegenzug natürlich auch, dass ein unbenutzter Index unter Umständen benutzt werden sollte.
• die Statistiken eines Index (insbesondere die Anzahl distinkter Einträge) sind eine Information, die der Optimizer berücksichtigen kann, auch wenn ein Index nicht in Query-Plänen erscheint. Theoretisch kann man diese Information auch über extended statistics erfassen, aber mehr als 20 column groups kann man damit pro Tabelle nicht erfassen.
• unbenutzte Indizes können als Lösung des berüchtigten foreign key locking-Problems eine Rolle spielen.

Zur Bestimmung der tatsächlichen Verwendung gibt es diverse Hilfsmittel, die allerdings jeweils ihre spezifischen Beschränkungen haben:

• v$object_usage: hat eine beschränkte Aussagekraft, da die View nur eine simple ja/nein-Logik zur Verwendung abbildet und nichts über die Häufigkeit der Verwendung aussagt.
• v$sql_plan: es ist teuer, diese View zu scannen - und ihre korrespondierenden Gegenstücke in AWR/Statspack basieren auf Sampling, so dass darin Queries fehlen können.
• Analyse der Definitionen: durch Betrachtung der Index-Definitionen kann es bereits möglich sein, Redundanzen zu bestimmen und zu ermitteln, welche Indizes verzichtbar sein sollten. Natürlich kann dieses Vorgehen recht zeitaufwändig sein.
• v$segstat, v$segment_statistics: liefern Informationen zu den Lese- und Schreiboperationen auf den Segmenten - wobei v$segment_statistics nur eine um Namen ergänzte Varainte von v$segstat darstellt. Weitere Details finden sich in der zugrunde liegenden Struktur x$ksolsstat. Die entscheidende Frage ist nun, wie man die darin enthaltenen Angaben sinnvoll interpretieren kann und zwischen der Arbeit, die im Rahmen der Index-Maintenance anfällt, und den eigentlichen Zugriffsoperationen unterscheidet - aber das ist eine Geschichte, die ein andermal erzählt werden soll; nämlich im angekündigten zweiten Artikel. 

Diesen zweiten Artikel plane ich zu ergänzen, aber das mag - urlaubsbedingt - einige Zeit dauern.

UNUSABLE indexes und TRUNCATE TABLE

Ein wichtiger Hinweis von Mauro Pagano, der zwei Informationen zusammenbringt, die mir beide bekannt waren, ohne dass ich mich dafür verbürgen würde, dass ich in die angesprochene Falle nicht schon gelegentlich hineingetappt bin:

• seit 11.2 sorgt ein ALTER INDEX UNUSABLE dafür, dass das zugehörige Index Segment (oder Index-Partition-Segment) entfernt wird.
• ein TRUNCATE TABLE versetzt zugehörige Indizes wieder in den Status USABLE und erzeugt das Index-Segment wieder.

Somit sollte die Reihenfolge vor der Durchführung von Load-Operationen immer so gewählt sein, dass das ALTER INDEX UNUSABLE erst nach einem TRUNCATE TABLE erfolgt.

"Fixed Subqueries" und Partitionierte Tabellen

Jonathan Lewis weist in seinem aktuellen Scratchpad-Artikel darauf hin, dass neue Features des Optimizers nicht immer in allen relevanten Zusammenhängen folgerichtig integriert werden. Das Beispiel, an dem diese Schwierigkeit aufgezeigt wird, ist das der "fixed subqueries" - also Queries der Form "select 42 from dual" -, bei denen der Optimizer (seit 12c) dazu in der Lage ist, zu erkennen, dass der Wert 42 invariant ist, und daher bereits bei der Optimierung berücksichtigt werden kann. Im Artikel wird gezeigt, dass der Optimizer erwartungsgemäß dazu in der Lage ist, einschränkende Prädikate solcher "fixed subqueries" bei den Cardinality-Schätzungen korrekt zu berücksichtigen - und dass das nicht funktioniert, wenn der statische Wert durch eine Funktion verschleiert wird (also wenn satt 42 eine Funktion f(42) erscheint, die 42 zurückliefert). Wenn man aber statt einer einfachen eine partitionierte Tabelle verwendet, ergibt sich die erwähnte Uneinheitlichkeit: für den Fall der Funktionsverwendung ist das Verhalten folgerichtig, aber beim Einsatz des unveränderten Literalwertes wird dieser zwar bei der Cardinality-Schätzung berücksichtigt, nicht aber bei der Bestimmung der Pstart und Pstop values, die mit den Angaben KEY - KEY erscheinen - also zum Compile-Zeitpunkt anscheinend als unbekannt betrachtet werden. Offenbar ist das Verhalten also noch nicht in allen Zusammenhängen konsistent, was vermutlich in folgenden Releases korrigiert werden wird.

Verwaiste SQL-Server-Dateien

Ein schöner Hinweis von Andrej Kuklin, mit dem ich lange zusammen gearbeitet habe (und noch länger Fußball spiele): da der SQL Server bei der Löschung einer zuvor OFFLINE gesetzten Datenbank die zugehörigen Daten- und Log-Dateien nicht ebenfalls löscht, kann man relativ leicht verwaiste Dateien erzeugen, die von keiner DB mehr benötigt werden. Für den SQL Server 2012 liefert Andrej ein Skript, mit dessen Hilfe diese Überreste ermittelt werden können. Für ältere Releases gibt es andere Varianten, die im Artikel verlinkt sind.

Library Cache Verschmutzung

Da ich den Fall hier eher wiederfinde als im OTN-Forum erzähle ich an dieser Stelle kurz einen Thread nach, den ich dort vor einigen Tagen begonnen habe. Meine Frage im Forum lautete: wieso sehe ich in einer RAC-Instanz (11.2.0.3, zwei Knoten, Standard Edition) diverse Queries, zu denen zahlreiche child cursor existieren, obwohl die Queries keine Bindewerte enthalten, so dass die Pläne eigentlich problemlos wiederverwendbar sein sollten? Die Antwort auf die Frage haben mir John Spencer, Randolf Geist und Mohamed Houri geliefert, die die fehlenden Einträge in meinem Lückentext ergänzten. Insbesondere wies mich Randolf darauf hin, dass in der Standard Edition eigentlich keine Parallelisierung im Spiel ist, aber Zugriffe auf gv$-Objekte intern trotzdem parallel operieren - was dazu passte, dass die fraglichen Queries alle auf dynamische Performance-Views zugriffen. Außerdem merkte er an, dass die Spalte REASON in gv$sql_shared_cursor nicht zum Spass eingeführt wurde, sondern wichtige Begründungsdetails dafür liefert, warum ein Plan nicht wiederverwendet werden kann. Möglicherweise hätte ich sofort genauer auf diese Angaben geschaut, wenn ich Tanel Poders nonshared Skript verwendet hätte, auf das Mohamed verwies, und das ein Pivoting der View-Informationen durchführt und die Reason exponiert darstellt. Der Angabe war dann - mehr oder minder - deutlich zu entnehmen, dass hier eine Abweichung zwischen dem parallel_query_default_dop und dem kxfr_Default_DOP vorliegt, was genau zum von John erwähnten "Bug 14711917 - High version count in RAC due to PX_MISMATCH (Doc ID 14711917.8)" passt. Die Ursache für die Abweichung liegt im gegebenen Fall wohl darin, dass die beiden RAC-Knoten tatsächlich einen unterschiedlichen CPU_COUNT besitzen. Den könnte man theoretisch korrigieren, aber sinnvoller erscheint die Verwendung des zugehörigen Patches oder das Upgrade auf 11.2.0.4. Einmal mehr zeigt sich, dass die OTN-Foren eine sehr große Hilfe bei der Problemlösung sein können. Im Thread habe ich meine Beobachtungen recht umfassend dokumentiert - und das spare ich mir deshalb hier.

P.S.: der aufmerksame Leser mag einen Zusammenhang mit meiner letzten Notiz hier im Blog vermuten - und da läge er nicht daneben.