Zur Semantik der Statistik "table scans (long tables)"

Der aktuelle Beitrag im Scratchpad von Jonathan Lewis hat ein wenig an seine alten Quiz-Night Artikel angeschlossen und mir eine Erwähnung eingetragen:

Martin Preiss (see comments) has been working hard to investigate this, and managed to produce a couple more variations in statistics for 'the same' problem.

Ganz so hart war die Arbeit nicht, aber überraschend war das Ergebnis für mich allemal. Interessant ist zunächst, dass sich zwar Angaben für die Statistiken "table scans (short tables)" und "table scans (long tables)" wechselseitig ausschließen (soll heißen: ein full table scan kann nicht gleichzeitig in beiden Kategorien erscheinen), dass das aber nicht für die übrigen "table scans"-Angaben gilt: also für "table scans (rowid ranges)", "table scans (cache partitions)" und "table scans (direct read)". Dort auftretende Ergebnisse erscheinen zusätzlich auch unter "table scans (short tables)" oder "table scans (long tables)" - sie sind also eher ergänzende Typ-Informationen. Interessant ist darüber hinaus, dass die Semantik der Angaben in "table scans (short tables)" und "table scans (long tables)" auch deutlich von dem abweicht, was zumindest ich mir bisher darunter vorgestellt hatte: für einen parallelisierten Zugriff erscheint hier nicht etwa ein Wert 1 und auch nicht die Anzahl der parallelen Slaves, sondern "Parallelisierungsgrad" * 13, wobei die 13 dem Parameter _px_min_granules_per_slave entspricht und die Anazhl der Chunks angibt, in die die Tabelle zerlegt wurde. Die entsprechende Angabe wiederholt sich in "table scans (rowid ranges)", und die zugehörige Aussage der Dokumentation hatte mich erst auf die Idee gebracht, die Frage der Parallelisierung zu prüfen: "During parallel query, the number of table scans conducted with specified ROWID ranges". Der Wert "Parallelisierungsgrad" * 13 erscheint übrigens auch im sql monitor Ergebnis unter "execs". Unschön daran ist, dass dadurch der Aussagewert von "table scans (long tables)" dadurch ziemlich überschaubar wird, sofern man sich nicht die Mühe macht, die Parallelisierung von Queries einzurechnen. Interessant ist weiterhin der Hinweis auf eine Option, die mir entweder entgangen oder entfallen war: parallele Slaves können in 11g den Buffer Cache verwenden, so dass sie nicht unter "table scans (direct read)" erscheinen müssen - während mir der umgekehrte Fall der serial full table scans mit direct path Nutzung schon häufiger begegnet ist, hatte ich diese Variante bisher nicht im Blick; fast vielleicht damit zusammen hängt, dass mich parallele Zugriffe regelmäßig in Erstaunen versetzen.

Erforderliche Leseoperationen bei der Anlage eines Primary Keys

Ein recht überraschendes Verhalten bei der Constraint-Aktivierung spricht Jonathan Lewis in einem aktuellen Artikel an: wenn man einen mehrspaltigen Primary Key auf einer Tabelle anlegt, auf der zuvor für die betroffenen Spalten noch keine NOT NULL Constraints existierten, dann muss Oracle für jede einzelne Spalte einen Full Table Scan durchführen, um zu prüfen, dass die Spalten tatsächlich keine NULL-Werte enthalten. Anschließend ist ein weiterer Full Table Scan erforderlich, um die Daten für die Index-Anlage zu beschaffen. Sichtbar ist die Ressourcen-Nutzung über die Session-Statistics. Der Eindruck entsteht, dass hier ein effizienteres Vorgehen möglich wäre - aber die Implementierung ist an dieser Stelle offenbar seit vielen Oracle-Versionen die gleiche geblieben.

Extended Statistics und Join Cardinality

Vor ein paar Wochen hat Jonathan Lewis einen recht wichtigen Hinweis für den Einsatz von Extended Statistics (also Statistiken für eine Spaltengruppe, die vorhandene Korrelationseffekte bestimmbar machen - und intern über eine virtuelle Spalte abgebildet sind) zur Korrektur von abwegigen Schätzungen bei (equality) Joins über mehrere Spalten geliefert: um hier brauchbare Kardinalitäten zu bekommen, muss man die Extended Statistics auf beiden Seiten des Joins anlegen. Legt man sie nur auf einer Seite an, so ändert sich nichts an den Schätzungen gegenüber der Variante ohne column group statistics. Wichtig ist in diesem Zusammenhang auch der Hinweis, dass mehrspaltige Indizes die gleiche Wirkung haben und auch die Kombination eines solchen Index auf der einen und Extended Statistics auf der anderen Seite zur Korrektur der Schätzungen dienen können.

Indizes und Inittrans

Zur Ablenkung hat Richard Foote zuletzt einen Artikel abgeschlossen, in dem er sich mit der Rolle des inittrans-Attributs für Tabellen und Indizes auseinandersetzt. Grundsätzlich bestimmt dieses Attribut, wie viele Transaktions-Slots initial in der ITL (= Interested Transaction List) eines Tabellen-, Index- oder Cluster-Blocks bereitgestellt werden - und jede Operation, die eine Änderung an einem Block durchführen will, muss sich in einen solchen Slot eintragen. Für Tabellen-Blocks kann das Fehlen eines solchen Slots zu hängenden Transaktionen führen, da im Extremfall erst darauf gewartet werden muss, dass ein Slot von einer anderen Transaktion freigegeben wird. Dieses Verhalten zeigt der Herr Foote anhand eines Beispiels, in dem der PCTFREE-Wert eines Objekts auf 0 gesetzt wird, so dass sehr dicht gepackte Blöcke entstehen, in denen die ITL nicht wachsen kann. Außerdem erscheinen in diesem Beispiel die üblichen Block-Dumps und Tabellennamen ziggy und bowie, die erklären, wovon sich der Autor gerade ablenken muss... Für Indizes sieht der Fall anders aus. Wenn man dort einen dicht gepackten Index-Block erzeugt, führt das Auftreten neuer Transaktionen zu einem 50:50-Block-Split, was im Zweifel gegenüber hängenden Transaktionen vorzuziehen sein dürfte. Mir fällt in diesem Zusammenhang noch ein, dass Jonathan Lewis gelegentlich darüber geschrieben hat, dass die beim Split in den entstehenden Blocks auftretenden ITLs zu massiver Platzverschwendung führen können. Gelegentlich könnte ich überprüfen, ob sich an diesem Verhalten in jüngeren Releases etwas geändert hat.

Löschung von Spalten

Mir ist durchaus klar, dass sich ein großer Teil meiner Beiträge hier auf die (verkürzende und manchmal sinnentstellende) Paraphrase der Artikel von Jonathan Lewis beschränkt. Aber an einführenden Sätzen wie den folgenden aus dem Artikel Dropping Columns, den der Herr Lewis gerade bei AllThingsOracle veröffentlicht hat, komme ich einfach nicht vorbei:

One of the bugs that came up on 9th Jan was described as: “Bug 18700681 : DROP COLUMNS USING ALTER TABLE VERY SLOW”. My first thought when I saw this was that it wasn’t a bug it was expected behaviour (with the caveat that “very slow” might actually mean “even slower than the ‘very slow’ that I was expecting”).

Dass die Löschung von Spalten in einer sehr großen Oracle-Tabelle wenig Freude bereitet, gehört jedenfalls zu den Dingen, die mir auch schon aufgefallen sind. Meine Antworten darauf wären in der Regeln CTAS oder dbms_redefinition. Im AllThingsOracle-Artikel erfährt man dazu Folgendes:

• um eine Spalte zu entfernen, kann man sie als unused markieren oder via drop column vollständig entfernen.
• das "set unused" ist relativ billig, da es nur das data dictionary aktualisiert, dafür aber auch keinen Speicherplatz freigibt. Das "drop column" hingegen stellt eine komplette Reorganisation der Tabelle dar und ist entsprechend kostspielig.
• beide Operationen sind irreversibel. In aktuellen Oracle-Versionen könnte man eine Spalte auch als invisible markieren, was man als eine weniger endgültige zum "set unused" ansehen könnte.
• bestimmte Spalten kann man über "drop column" bzw. "set unused" nicht entfernen - etwa den partition key einer partitionierten Tabelle oder den primary key einer IOT, was inhaltlich recht unmittelbar einleuchtet.
• allerdings kann man die Spalten des primary key einer heap Tabelle löschen, was zur Löschung des PK-Index führt.
• ein "drop column" erfordert das Lesen aller Blocks der Tabelle. Werden gleichzeitig mehrere Spalten gelöscht, so wird jede Löschung als separates Update behandelt und erzeugt undo und redo.
• beim "drop column" wird ein exclusives lock auf der Tabelle erzeugt und die Operation in einer einzelnen großen Transaktion durchgeführt, sofern man nicht die checkpoint Klausel spezifiziert. Die checkpoint Option macht es möglich, eine zwischenzeitlich abgebrochene "drop column" Operation über ein "continue" fortzusetzen - allerdings ist die Tabelle nach dem Abbruch für DML und Queries nicht verfügbar.
• als grobe Orientierung bei der Löschung einzelner Spalten gilt (auch für nologging Tabellen):
• undo pro Datensatz: 100 byte + durchschnittliche Spaltengröße.
• redo pro Datensatz: 250 byte + durchschnittliche Spaltengröße.
• ein Test zeigt, dass die Löschung mehrerer Spalten zwar nur einen Tablescan hervorruft, aber undo und redo pro Spalte auftreten und sich summieren. In diesem Test liegt der Overhead für undo bei etwa 78 bytes pro Spalte und Datensatz. Für redo ergeben sich 234 bytes pro Datensatz und Spalte. An den Werten ändert sich nicht viel, wenn man die checkpoint-Klausel verwendet. Allerdings werden in diesem Fall mehrere undo Segmente verwendet.
• eine Spalte kann nicht gelöscht werden, wenn für die Tabelle basic compression im Einsatz ist. Mit "compress for OLTP" (aks "row store compress advanced") ist das hingegen möglich. Allerdings scheint in diesem Fall keine Löschung stattzufinden, sondern nur eine Anpassung im data dictionary.

Von meinen eingangs erwähnten Ideen CTAS und dbms_redefinition ist hier keine Rede. Ihr Vorteil könnte auch höchstens darin liegen, dass sie das Basisobjekt nicht so massiv sperren - andererseits würde DML während der CTAS-Operation (und dem zugehörigen Austausch der Objekte) die Strategie ohnhin witzlos machen.

ANSI Join Syntax vs. Oracle Join Syntax

Neben einem aktuellen Beispiel liefert Jonathan Lewis in seinem Blog eine sehr schöne Erklärung für die Tatsache, dass die ANSI Join Syntax gegenüber der Oracle Join Syntax noch immer im Nachteil ist:

In almost all cases the SQL you write using the ANSI standard syntax is tranformed into a statement using Oracle’s original syntax before being optimised – and there are still odd cases where the translation is not ideal. This can result in poor performance, it can result in wrong results.

Völlig einleuchtend - und so klar hatte ich das Problem bisher nicht fassen können (obwohl es nach dieser Erläuterung ganz und gar offensichtlich ist).

Outer Join Beschränkungen in 12c

Einige interessante Beobachtungen zum Thema "Outer Joins in traditioneller Syntax  (aka Oracle-Join-Syntax) " - also mit Verwendung des + Operators statt des OUTER JOIN Schlüsselworts (aka ANSI-Join-Syntax) hat Philipp Salvisberg vor kurzem in seinem Blog zusammengestellt. Demnach sind die relevanten inhaltlichen Einschränkungen, die den + Operator in älteren Releases betrafen, in 12.1.0.2 alle verschwunden: insbesondere "ORA-01417: a table may be outer joined to at most one other table" und "ORA-01719: outer join operator (+) not allowed in operand of OR or IN" treten nicht mehr auf, und diese Fehler waren in der Vergangenheit gelegentlich ein guter Grund für eine massive Umgestaltung einer Query. Geblieben sind einige exotischere Fehlermeldungen wie etwa "ORA-01416 two tables cannot be outer-joined to each other", die sich gut begründen lassen - weil dergleichen inhaltlich in der Tat völlig sinnlos wäre.

Auf diese Weise scheint der + Operator wieder deutlich an Attraktivität gewonnen zu haben, denn die (aus meiner Sicht lesbarere und auch von Oracle gelegentlich empfohlene) ANSI-Join-Syntax ist meines Wissens immer noch mit relativ vielen Bugs verknüpft (die allerdings eher in komplexeren Ausnahmefällen relevant werden). Auf jeden Fall scheint die Begründung des Verzichts auf den + Operator mit funktionalen Beschränkungen nicht mehr stichhaltig zu sein.