Skript zur Bestimmung von Index Fragmentation

In den OTN Foren gibt es bei diversen Beiträgern eine gewisse Tendenz dazu, auf bestimmte Schlüsselwörter allergisch zu reagieren. Eines dieser Schlüsselwörter ist: Fragmentation. Im ungünstigen Fall bekommt man dann zur Antwort, dass es so etwas wie Fragmentation nicht gäbe - was ich für ausgemachten Blödsinn halte -, im günstigeren Fall, dass man den Terminus bitte erst mal definieren sollte, was durchaus eine sinnvolle Reaktion darstellt. Aber da der Fall von Index Fragmentation eher weniger kontrovers ist, belasse ich es hier bei der harmlosen Einordnung: ein Index ist dann fragmentiert, wenn er deutlich mehr Leaf Blocks beinhaltet als zur Speicherung der enthaltenen Einträge eigentlich erforderlich wären. Bei Kellyn Pot’Vin-Gorman habe ich heute einen Verweis auf ein nützliches Skript von Franck Pachot gefunden, das Folgendes leistet: "shows the number of rows per block, as well as used and free space per block, and aggregates that by range of values". Für die Detail-Analyse einzelner Indizes kann dieses Skript extrem nützlich sein - und die manuelle Sammlung und Verknüpfung der entsprechenden Informationen vermeiden.

Attribut-Clustering und ein Jubiläum

Vor kurzem hat Connor McDonald eine Artikelserie zum Attribut-Clustering in 12c veröffentlicht, die im dritten Teil ein ziemlich seltsames Verhalten demonstriert. Ein Kommando:

alter table source_data add clustering by linear order(object_id);

führt zu einem erfolgreichen Clustering, während das Kommando:

alter table source_data clustering by linear order(object_id);

erfolgreich abläuft, aber anscheinend keinerlei Wirkung hat: das "add" hat also die entscheidende Wirkung. Randolf Geist hat in seinem Kommentar zum Artikel die naheliegende Frage nach dem Sinn der zweiten Variante gefragt, was Connor McDonald leider nicht beantworten konnte. Seltsam ist das auf jeden Fall.

Einen anderen Punkt will ich auch noch erwähnen: dies ist der tausendste Eintrag in diesem Blog und ich nehme das mal wieder zum Anlass, mich bei meinen Lesern zu bedanken. Darüber hinaus ergänze ich aus kalendarischen Gründen dann auch noch alle jahreszeitüblichen Grüße und Wünsche.

dbms_redefinition und deterministische Funktionen

Vor längerer Zeit war ich hier zum Ergebnis gekommen, dass rdbms_redefinition für komplexere Umbaumaßnahmen nicht verwendbar ist, weil man im Mapping keine komplexere Join-Logik oder Subselects unterbringen kann. Jetzt habe ich bei Connor McDonald gesehen, wie man es richtig macht: statt eines Joins  kann man im col_mapping eine deterministische Funktion unterbringen - und damit wird das Package noch mal deutlich interessanter.

dbms_redefinition mit virtuellen Spalten

Nach längerer Zeit mal wieder etwas selbst Gebasteltes: im OTN Forum General Database Discussions wurde kürzlich ein Fall vorgestellt, in dem die Verschiebung einer Tabelle in einen anderen Tablespace mit Hilfe von dbms_redefinition.redef_table in einen Fehler führt, weil der vereinfachte automatisierte Prozess versucht, eine virtuelle Spalte durch ein Insert zu befüllen. Dazu ein Beispiel, das den OTN Fall noch mal reduziert:

drop table t;
drop table t_int;

create table t (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace users
;

alter table t add constraint t_pk primary key (id);

insert into t (id, col_1, col_2)
values (1, 42, null);

commit;

select * from t;

        ID      COL_1      COL_2 COL_VIRTUAL
---------- ---------- ---------- -----------
         1         42                     42

select table_name, tablespace_name from user_tables where table_name = 'T';


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS


exec dbms_redefinition.redef_table (uname=>user, tname=>'t', table_part_tablespace=> 'EXAMPLE');  
*
ERROR at line 1:
ORA-42008: error occurred while instantiating the redefinition
ORA-12018: following error encountered during code generation for "C##TEST"."REDEF$_T92713"
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3385
ORA-06512: at line 1

Zur Erinnerung: rdbms_redefinition überführt die Daten der Originaltabelle in eine Interim-Tabelle und tauscht die beiden Objekte intern gegeneinander aus. Änderungen (also DML-Operationen), die sich nach dem Start der Reorganisation in der Quelltabelle ergeben, werden intern vermerkt und in der Zieltabelle vor Abschluss der Umstellung nachgezogen, so dass die Operation komplett online ablaufen kann. Die Prozedur redef_table ist dabei eine vereinfachte Variante, sie bietet ein:

single interface that integrates several redefinition steps including the CAN_REDEF_TABLE Procedure, the START_REDEF_TABLE Procedure, the COPY_TABLE_DEPENDENTS Procedure and the FINISH_REDEF_TABLE Procedure. This procedure can change data storage properties including tablespaces (for table, partition, subpartition, index, LOB column), compress type (for table, partition, subpartition, index, LOB column) and STORE_AS clause for the LOB column.

Im gegebenen Fall übersieht die Prozedur aber offenbar, dass man eine virtuelle Spalte nicht einfach mit einem Insert füllen kann, sondern ausklammern muss. Um das zu erreichen, kann man die Einzelschritte, die in redef_table zusammengefasst sind, einzeln aufrufen:

create table t_int (
    id number
  , col_1 number
  , col_2 number
  , col_virtual number generated always as (nvl(col_1, col_2))
)
tablespace example
;

declare
    l_colmap varchar(512);
begin
    l_colmap := 'id, col_1, col_2';
    dbms_redefinition.start_redef_table (  
        uname => user
      , orig_table => 't'
      , int_table => 't_int'
      , col_mapping => l_colmap );

end;
/

exec dbms_redefinition.finish_redef_table ( user, 't', 't_int' );

select table_name, tablespace_name from user_tables where table_name = 'T';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              EXAMPLE

Im Rahmen der Prozedur start_redef_table kann man die interim Tabelle t_int explizit angeben (nachdem man sie zuvor passend definiert hat) und über ein col_mapping die Liste der Spalten aufführen, die übertragen werden sollen. Das ist zwar nicht ganz so komfortabel wie der redef_table-Aufruf, führt aber zum gewünschten Ergebnis.

Massendatenlöschungen

Ich werde nachlässig: auf der Suche nach meiner Zusammenfassung der ersten beiden Teile von Jonathan Lewis AllThingsOracle-Serie zu Massendatenlöschungen ist mir aufgefallen, dass ich dazu keine Zusammenfassungen geschrieben habe. Aber das lässt sich ja ändern: 

Massive Deletes – Part 1: beschäftigt sich zunächst mit strategischen und taktischen Fragen bei der Löschung von Massendaten. Auf der strategischen Ebene liegen Fragen nach der Zielsetzung der Löschung: was verspricht man sich davon und lohnt sich der Aufwand überhaupt und schafft die Löschung nicht vielleicht neue Probleme. Auf taktischer Ebene liegen Fragen der Verfügbarkeit von Ressourcen, der Möglichkeit einer Downtime zu Wartungszwecken etc. Dazu liefert der Autor zwei Szenarien in denen Partitioninierung - bzw. ihr Fehlen - und globale Indizes eine Rolle spielen. Ausgehend davon werden zwei übliche Gründe für Löschungen und drei Standard-Muster aufgeführt. Die Gründe sind:

• Verbesserung der Perfromance
• Reduzierung der Storage-Verwendung

Bei den Pattern werden folgende Fälle aufgeführt:

• Löschung von Daten auf Basis einer Eingangszeit
• Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung
• Löschung der Daten einer bestimmten Kategorie

In Zusammenhang mit diesen Mustern steht die Frage, wie Oracle die Daten auf Blockebene speichert. Dabei führt die "Löschung von Daten auf Basis einer Eingangszeit" dazu, dass komplette Blöcke in den ersten Extents einer Tabelle frei werden, die dann wieder von (erneut gut geclusterten) neuen Daten belegt werden. Bei den beiden anderen Mustern kann sich die physikalische Clusterung der Daten deutlich verändern, da die "Löschung der Daten einer bestimmten Kategorie" vermutlich aus allen Blöcken etwa den gleichen Anteil entfernt, während bei der "Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung" eine gewisse Korrelation zur zeitlichen Entwicklung vorliegt,die aber weniger stark ist als bei den Eingangszeitpunkten. Diese physikalische Clusterung der Daten kann sich dann wieder massiv auf die Effektivität von Indizes auswirken (Stichwort: clustering factor). Außerdem spielt die Menge der Indizes natürlich eine große Rolle bei der Löschung selbst, da diese Indizes während der Löschung verwaltet werden müssen.

Massive Deletes – Part 2: beginnt mit der Beobachtung, dass die Frage, wo massive Datenlöschungen beginnen, angesichts sehr unterschiedlicher Hardware schwer zu definieren ist. Im Beispiel wird eine Tabelle von 1,6G erzeugt, die 10M rows für 10 Jahre enthält (1M pro Jahr). Auf dieser Basis werden die drei im ersten Teil skizzierten Szenarien durchgespielt. Die Effekte der Löschung werden mit einer Query überprüft, die über die Anzahl der rows pro Block gruppiert. Für die "Löschung von Daten auf Basis einer Eingangszeit" ergeben sich auch nach der Löschung sehr dicht gepackte Blöcke. Für die Variante "Löschung von Daten auf Basis des Endzeitpunkts der Bearbeitung" bleiben relativ viele Blöcke mit einem deutlich niedrigeren Füllgrad stehen, von denen allerdings die überwiegende Mehrheit nicht für neue Inserts verwendet werden würden. Auch für die Indizes ergibt sich ein ähnliches Bild - allerdings ist die zugehörige Analyse-Query deutlich komplexer: jedenfalls wird der Füllgrad der Indizes der Indizes reduziert. Abhängig von der Verteilung der Einträge kann die Maintenance bei der Löschung sehr teuer werden, so dass es unter Umständen sinnvoller sein könnte, den Index vor der delete Operation als unusable zu markieren (oder zu löschen) und anschließend neu aufzubauen.

Massive Deletes – Part 3: erläutert die Bereiche, in denen eine Löschung - wie jede andere DML-Operation - einen Overhead hervorruft, und da der Herr Lewis das sehr präzise zusammenfasst, zitiere ich hier extensiv:

• redo: "every change to a data block generates redo – a description of the new information that has been written to the block plus an overhead of a few tens of bytes: and if you’ve got four indexes on the table then you may be changing 5 blocks for every row you delete."
• undo: "Every time you delete a row you “preserve” the row by writing into the undo segment a description of how to re-insert that row, and how to “undelete” the index entries. Each undo entry has an overhead of a few tens of bytes and each undo entry IS a change to a data block so (redo second thoughts) generates even more redo. Combining the two sources of redo, a single row delete with four affected indexes could produce around 1KB of redo, plus the size of the row and index entries deleted."
• I/O effects: hier kürze ich ab: Blöcke müssen von der Platte und in den Cache gelesen werden - und umgekehrt wieder geschrieben.
  
• Concurrency: auch hier versuche ich's mit Verkürzung: aufgrund Oracles MVCC-Verfahrens muss mit zusätzlichen redo und undo Effekten gerechnet werden.
  

Weiterhin wird erklärt, dass die Optimizer-Kosten eines delete denen eines entsprechenden select rowid from ... entsprechen. Für das delete hat der Optimizer im gegebenen Fall die Auswahl zwischen drei Strategien: FULL TABLE SCAN, INDEX FAST FULL SCAN (der aber erst seit 12c ohne Hint-Verwendung berücksichtigt wird) und INDEX RANGE SCAN. In zwei Beispielen wird nun ausgeführt, wie die Ressourcen-Nutzung in Hinsicht auf redo und undo für verschiedene Zugriffs-Varianten aussieht (abhängig von der Anzahl der vorhandenen Indizes) - einmal für eine Löschung gut geclusterter Daten und einmal für Daten, die stark verteilt sind. Interessant ist dabei vor allem folgender Punkt:

It’s not a well-known phenomenon, but Oracle uses a completely different strategy for deleting through an index range/full scan from the strategy it uses when delete through a tablescan or index fast full scan. For the tablescan/index fast full scan Oracle deletes a row from the table then updates each index in turn, before moving on to delete the next row.  For the index range scan / full scan Oracle deletes a table row but records the rowid and the key values for each of the indexes that will need to be updated – then carries on to the next row without updating any indexes. When the table delete is complete Oracle sorts all the data that it has accumulated by index, by key, and uses bulk updates to do delayed maintenance on the indexes. Since bulk updates result in one undo record and one redo change vector per block updated (rather than one per row updated) the number of redo entries can drop dramatically with a corresponding drop in the redo and undo size.

Insofern kann die Löschung über die den Index hinsichtlich der Index-Maintenance also grundsätzlich effizienter sein als die Verwendung von FTS (bzw. dem eng verwandten IFFS). Allerdings erfordert der Index Range Scan (bzw. der verwandte Index Full Scan) zusätzliche Sortierungen. Für die unterschiedlichen Pattern ergibt sich dabei jeweils eine sehr unterschiedliche Effektivität der Verfahren: während für die gut geclusterten Daten der Index Range Scan sehr performant erfolgt, ist bei den in der Tabelle stark verteilten Daten der FTS die bessere Wahl. Dabei ist die Arbeit zur Änderung der Tabelle unter Umständen im einen Fall höher, während die Index-Maintenance in diesem Fall höher wird und umgekehrt. Weniger gut schneidet in vielen Fällen der Index Fast Full Scan, der in 12c unter Umständen sehr häufig ausgewählt werden kann, weil er für select rowid from ... Queries eine sehr günstige Wahl ist.

Da Jonathan Lewis im dritten Artikel bereits weitere Beiträge angekündigt hat, werde ich hier vermutlich später noch Ergänzungen vornehmen.

Degenerierte Indizes

Seit ich begonnen habe, mich mit Datenbanken zu beschäftigen, lautet eine der populärsten Fragen in Oracle-Foren: mit welcher Häufigkeit soll ich meine B*Tree-Indizes neu aufbauen lassen? Zu behaupten darüber hätten Kriege stattgefunden, wäre vielleicht ein wenig übertrieben - aber heftige Auseinandersetzungen waren es allemal. Inzwischen ist dieser Konflikt weitgehend zur Ruhe gekommen, was vermutlich damit zusammen hängt, dass die Antwort eigentlich unstrittig ist: Oracles Implementierung von Indizes macht Rebuilds weitgehend unnötig - sie sind nur für seltene Fälle mit besonderen Bedingungen für die zugehörigen DML-Operationen relevant. Richard Foote hat sich schon vor mehr als zehn Jahren in Oracle B-Tree Index Internals: Rebuilding The Truth mit den Aussagen der Befürworter von regelmäßigen Rebuild-Operationen auseinander gesetzt und diese widerlegt. Trotzdem findet man den Vorschlag solcher Rebuilds auch noch in jüngeren Publikationen, wobei die Formulierungen allmählich den Bereich unfreiwilliger Komik erreichen, was David Aldridge auf Richards Footes Webseite kommentierte:

Interesting quote from Page 728 of that book: “Even if index rebuilding were to be proven as [sic] a useless activity, the Placebo effect on the end users is enough to justify the task.”. It opens up a whole new class of performance tuning … perhaps it could be called “Faith-based Tuning”.

Jonathan Lewis schrieb dazu: "If the only effect is a placebo effect couldn’t you just tell the users that you had rebuilt the indexes rather than actually doing it?"

So viel zur Geschichte - wobei es sich aus meiner Sicht um Ausschnitte aus dem Kapitel zur Auseinandersetzung zwischen wissenschaftlicher und mythischer Welterklärung handelt. Aber der eigentliche Anlass für diesen Eintrag war ein anderer: Mohamed Houri hat gerade einen Artikel zu seiner Verwendung des Scripts Index Sizing von Jonathan Lewis veröffentlicht, mit dessen Hilfe er einen jener seltenen degenerierten Indizes identifizieren konnte, die tatsächlich von einem Rebuild profitieren - also deutlich komprimiert werden können. Letztlich leistet das Skript dabei nicht mehr (und nicht weniger), als die tatsächliche Größe eines Index mit der angesichts des Datenvolumens erwartbaren Minimalgröße zu vergleichen. Mohamed ergänzt dieses Ergebnis in seinem Artikel um eine Visualisierung der durch die Funktion sys_op_lbid ermittelten Anzahlen von Index-Einträgen pro Leaf-Block und diese Darstellung zeigt, dass es neben einer großen Zahl gut gefüllter Blocks (mit 422 Einträgen) eine noch größere Anzahl relativ schwach gefüllter Blöcke (mit 113 Einträgen) gibt. In seinem Kommentar zum Artikel erinnert Jonathan Lewis wiederum daran, dass hier womöglich das bekannte Problem der Vervielfältigung von ITL Einträgen im Spiel ist, das in solchen Fällen regelmäßig aufzutreten pflegt.

Vielleicht hätte ich mir (und meinen vorgestellten Lesern) den historischen Einstieg auch ersparen können, aber offenbar habe ich inzwischen das Alter erreicht, in dem man beginnt, von den alten Zeiten zu schwadronieren, in denen alles besser/schlechter/anders gewesen 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..

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.

Deadlock bei Shrink Space

Jonathan Lewis beschreibt in seinem Blog ein ziemlich erstaunliches Phänomen: wenn man zwei Indizes der gleichen Tabelle in unterschiedlichen Sessions über ALTER INDEX ... SHRINK SPACE verkleinern möchte, dann versuchen beide Sessions ein exklusives (TM-) Lock auf die Tabelle zu bekommen - und blockieren sich dadurch gegenseitig. In der Alert.log finden sich auch Hinweise auf das deadlock, aber es erfolgt keine automatische Auflösung - beide Sessions warten und warten und warten...

Das Verhalten lässt sich in 11.2 mit einem einfachen Test problemlos reproduzieren (Anlage einer Tabelle mit zwei Indizes; paralleler Aufruf von Shrink Space für beide Indizes in unterschiedlichen Sessions).

Analyse für Index Rebuild

Die Zeiten, in denen das Thema automatisierter und regelmäßiger Index Rebuilds intensiv diskutiert wurde, sind wahrscheinlich vorbei, obwohl in den einschlägigen Oracle-Foren noch immer zugehörige Fragen auftauchen. Ein Grund dafür, dass das Thema nicht deutlich früher ad acta gelegt wurde, war möglicherweise die Tatsache, dass der Oracle Support selbst ein paar Skripte zur Bestimmung von Rebuild-Kandidaten bereitstellte. Richard Foote weist nun in seinem Blog darauf hin, dass eines der abwegigeren Exemplare dieser Gattung inzwischen von den MOS-Seiten entfernt wurde, während ein paar weniger problematische Dokumente weiterhin verfügbar sind.

Der Vollständigkeit halber noch mal der Hinweis darauf, dass Index-Rebuilds in der Regel zweckfrei sind und nur für bestimmte Muster des Index-Wachstums eine gewisse Relevanz besitzen (ein solches Muster wären monoton ansteigende neue Werte, während die alten Werte zum größten Teil gelöscht werden, weil dadurch viele Leaf-Blocks mit sehr wenigen Einträgen erhalten bleiben können). So ziemlich alles, was es zu diesem Thema zu sagen gibt, findet man in der entsprechenden Kategorie in Richard Footes Blog. Mein Favorit unter den Beiträgen ist ein Kommentar von David Aldridge, der schreibt:

Interesting quote from Page 728 of that book [i.e. D.K. Burleson: Oracle Tuning: The Definite Reference]: “Even if index rebuilding were to be proven as [sic] a useless activity, the Placebo effect on the end users is enough to justify the task.”. It opens up a whole new class of performance tuning … perhaps it could be called “Faith-based Tuning”.

Jonathan Lewis schlug darauf hin vor: "If the only effect is a placebo effect couldn’t you just tell the users that you had rebuilt the indexes rather than actually doing it ?"

DBMS_REDEFINITION und Massenupdates

Eine der von mir am häufigsten zitierten Antworten auf Oracle-Performance-Fragen ist Tom Kytes schöner Satz: "If I had to update millions of records I would probably opt to NOT update." Er bezieht sich darauf, dass Massenupdates eine sehr teure Operation darstellen, da sie Änderungen an jedem betroffenen Block und große Menge von redo und undo hervorrufen. Effizienter ist stattdessen der Neuaufbau der geänderten Datenmenge über CTAS in einer Hilfstabelle und die anschließende Umbenennung der Objekte.

Das Verfahren stößt allerdings an seine Grenzen, wenn es kein Wartungsfenster für die Durchführung des Austauschs gibt, da permanent DML-Operationen auf der fraglichen Tabelle durchgeführt werden. Für diesen Fall gibt es die Möglichkeit der Online-Reorganisation mit Hilfe von DBMS_REDEFINITION, die Tom Kyte ebenfalls gelegentlich erläutert hat. Dazu ein übersichtliches Beispiel:

-- Session 1:
drop table t;
drop table t_redefine;

create table t (id primary key, col_org, col2, padding)
as
select rownum id
     , 0 col_org
     , 0 col2
     , lpad('*', 50, '*') padding
  from dual
connect by level <= 1000000;


create table t_redefine (
    id number
  , col_upd number
  , col2 number
  , padding varchar2(50)
);  
    

declare
    l_colmap varchar(512);
  begin
    l_colmap := 'id, 1 col_upd, col2 + 2 col2, padding ';

    dbms_redefinition.start_redef_table
    (  uname           => user,
       orig_table      => 'T',
       int_table       => 'T_REDEFINE',
       orderby_cols    => 'ID',
       col_mapping     => l_colmap );
 end;
/

-- Session 2:   
update t set col2 = col2 + 2 where id <= 5;

select id, col_org, col2 from t where id <= 10;

        ID    COL_ORG       COL2
---------- ---------- ----------
         1          0          2
         2          0          2
         3          0          2
         4          0          2
         5          0          2
         6          0          0
         7          0          0
         8          0          0
         9          0          0
        10          0          0

-- Session 1:  
exec dbms_redefinition.finish_redef_table ( user, 'T', 'T_REDEFINE' );
--> wartet auf einen Abschluss der offenen Transaktion gegen T

-- Session 2:
commit;
-- Session 1:  
--> der Aufruf von dbms_redefinition.finish_redef_table meldet Vollzug

-- Session 2:
select id, col_upd, col2 from t where id <= 10;

        ID    COL_UPD       COL2
---------- ---------- ----------
         1          1          4
         2          1          4
         3          1          4
         4          1          4
         5          1          4
         6          1          2
         7          1          2
         8          1          2
         9          1          2
        10          1          2

Der Test definiert eine Quelltabelle T, deren Spalte col_org durch eine Spalte col_upd ersetzt werden soll, außerdem wird der Wert für col2 behutsam erhöht. Dazu wird eine Hilfstabelle T_REDEFINE mit den gewünschten Spalten angelegt und anschließend die Prozedur dbms_redefinition.start_redef_table aufgerufen, der neben den Angaben der Ziel- und der Hilfstabelle ein column_mapping übergeben wird, das die inhaltliche Füllung der Spalten nach der Reorganisation definiert. In diesem Mapping sind leider keine skalaren Subqueries erlaubt - wenn man versucht l_colmap mit der Angabe:

l_colmap := 'id, (select 2 from dual) col_upd, padding ';

zu füllen, erhält man den Fehler

ORA-22818: Unterabfrage-Ausdrücke sind hier nicht zulässig

Demnach können im Rahmen der Redefinition offenbar keine komplexeren Join-Operationen zur Füllung einer verändert definierten Spalte eingesetzt werden. Möglich ist allerdings die Ableitung von Spalten (col2 + 2). Änderungen der Quelltabelle, die nach dem Start der Redefinition in anderen Sessions durchgeführt wurden, werden korrekt propagiert. Die technische Erklärung des Verhaltens liefert ein SQL-Trace, dem zu entnehmen ist, dass zur Tabelle T eine Materialized View T_REDEFINE mit fast refresh erzeugt wird:

create snapshot "TEST"."T_REDEFINE"   on prebuilt table with reduced 
  precision  refresh fast with primary key  as select id, 1 col_upd, col2 + 2 
  col2, padding  from "TEST"."T"   "T"

...

INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "TEST"."T_REDEFINE"("ID",
  "COL_UPD","COL2","PADDING") SELECT "T"."ID",1,"T"."COL2"+2,"T"."PADDING" 
  FROM "TEST"."T" "T" ORDER BY ID

Die aus der Quelltabelle T übernommenen Spalten werden somit aktualisiert und parallel durchgeführte Änderungen sind im Zielobjekt weiterhin sichtbar. Nicht möglich scheint allerdings die Aktualisierung unter Zuhilfenahme einer Referenz zu sein, die beim Massenupdate über CTAS zu meinen präferierten Vorgehensweisen gehört. Ein großer Vorteil von dbms_redefinition ist allerdings, dass die Prozeduren des Packages sich um die Behandlung abhängiger Objekte (Indizes, Trigger etc.) kümmern können.

Nachtrag 16.11.2016: Connor McDonald zeigt, wie man die hier nicht verwendbare Join-Operation durch eine deterministische Funktion ersetzen kann: damit wird dbms_redefinition dann extrem wertvoll.

Online -Reorganisation für Partitionierte Tabellen mit 12c

Richard Foote schreibt in seinem Blog über die Möglichkeiten der Online Reorganisation von Tabellen in den Releases 11 und 12:

• 12c Online Partitioned Table Reorganisation Part I (Prelude) erklärt das Verhalten in Version 11:
• ALTER TABLE ... MOVE ONLINE ist keine gültige Option, da die ONLINE-Option nur für IOTs verfügbar ist.
• ein ALTER TABLE ... MOVE kann nicht erfolgen, wenn andere Sessions offene Transaktionen mit DML auf die Tabelle durchführen ("ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired").
• ein laufendes ALTER TABLE ... MOVE hindert umgekehrt andere Sessions an den Durchführung von DML-Operationen und hinterlässt alle zugehörigen Indizes im Status UNUSABLE.
• ein ähnliches Verhalten ergibt sich bei der Reorganisation von Tabellen-Partitionen.
• die Alternative dbms_redefinition hat ihre eigenen Probleme und ist deutlich unhandlicher.
• 12c Online Partitioned Table Reorganisation Part II (Move On) erläutert die Änderungen, die sich mit 12c ergeben haben:
•  Tabellen-Partitionen können jetzt online reorganisiert werden, wobei die zugehörigen Indizes aktualisiert werden und verfügbar bleiben.
• erforderlich ist dafür die Syntax: ALTER TABLE ... PARTITION ... UPDATE INDEXES ONLINE. 
• Die MOVE-Operation beim Vorliegen offener Transaktionen scheitert nun nicht mehr an ORA-00054, sondern wartet auf das Commit der zugreifenden Sessions, um ein exklusives table partition lock bekommen zu können.
• die zugehörigen Indizes bleiben im Status USABLE.
• nach dem Start des MOVE-Kommandos abgesetzte DML-Operationen anderer Sessions können problemlos durchgeführt werden.
• Leider betrifft die Möglichkeit der Online Reorganisation zur Zeit nur partitionierte Tabellen, während nicht-partitionierte Tabellen davon ausgeschlossen sind - was ein Argument dafür sein könnte, solche Tabellen als partitionierte Objekte mit einer Partition anzulegen.