Session-spezifische Statistiken für GTT in 12c

Stefan Koehler, der im SAP on Oracle Blog mit schöner Regelmäßigkeit hochinteressante und ausgesprochen fundierte Artikel veröffentlicht, weist darauf hin, dass es in 12c möglich ist, Session-spezifische Statistiken für Global Temporary Tables (GTT) zu erzeugen. Vor 12c überschrieb der letzte dbms_stats-Aufruf die GTT-Statistiken jeweils, was für die erzeugende Session günstig war, für alle anderen Sessions aber eher nicht. Mit 12c werden die Statistiken pro Session gehalten und bei der Optimierung korrekt ausgewertet. In der dbms_xplan-Ausgabe erscheint der erläuternde Hinweis: "Global temporary table session private statistics used". Diese Ergänzung in der Statistikerfassung dürfte einige unübersichtliche Workarounds um ihren Arbeitsplatz bringen.

SYS_OP_MAP_NONNULL in 12c dokumentiert

Ein interessanter Hinweis von Sayan Malakshinov: die nützliche Funktion SYS_OP_MAP_NONNULL ist in 12c dokumentiert. Grundsätzlich ist die Funktion ein Hilfsmittel, um NULL mit NULL gleichsetzen zu können (was ohne dieses Hilfsmittel nicht true, sondern auch wieder NULL wäre). Ein paar Hinweise zu ihren Einsatzmöglichkeiten und Limitierungen hatte ich gelegentlich hier vermerkt.

Schwächen des Optimizers mit MINUS

Jonathan Lewis zeigt in seinem Blog, dass der Optimizer bei MINUS-Operationen nicht unbedingt immer das Offensichtliche erkennt: sein Beispiel selektiert aus einer Tabelle ohne Datensätze von der eine sehr große Menge über MINUS abgezogen wird. Angesichts der Tatsache, dass nach dem Scan der ersten Tabelle klar sein sollte, dass die Ergebnismenge leer bleiben wird, könnte man erwarten, dass der Optimizer auf den Scan der zweiten Tabelle verzichten kann - aber das tut er nicht. Der Autor liefert auch noch einen Trick, mit dem sich das Verhalten korrigieren lässt, aber merkwürdig bleibt es allemal.

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.

Multiplikation von Wahrscheinlichkeiten mit Analytics

Mein langjähriger Kollege Christoph Jung hat in seinem Blog ein schönes Beispiel dafür veröffentlicht, wie man Lücken im verwendeten SQL-Dialekt durch Umstellungen des Ermittlungsverfahrens überbrücken kann. Im gegebenen Fall fehlt eine analytische Multiplikationsfunktion, aber Christoph, der die arkane Kunst der Mathematik deutlich tiefer durchdrungen hat als ich, erinnert daran, dass man eine Multiplikation von Werten in die Addition ihrer Logarithmen umwandeln kann, und liefert eine komplexe Query zur Multiplikation von Wahrscheinlichkeiten und Ermittlung von Konfidenz-Intervallen.

Im Fall Oracle hat man allerdings noch eine andere Möglichkeit: die individuelle Definition fehlender Funktionen als User Defined Aggregates, die Carsten Czarski gelegentlich beschrieben hat.

100K

Reichlich spät fällt mir auf, dass mein Blog die Marke von 100.000 Besuchen überschritten hat - ich sage bewusst Besuche, da es sich natürlich nicht um so viele Besucher handelt. Wenn ich davon ausgehe, dass davon ca. 75.000 durch meine eigenen Aufrufe begründet sind und 20.000 von Leuten stammen, die direkt wieder umgekehrt sind, als sie feststellten, dass nur die SQL-Kommandos als Englisch durchgehen mögen, bleiben doch ein paar tausend Klicks, die ich nicht problemlos wegdiskutieren kann. Dafür danke ich allen Besuchern ganz herzlich und hoffe, dass hier gelegentlich jemand etwas Interessantes gefunden hat.

Noch ein paar nette 12c Features

Julian Dontcheff nennt in seinem Blog zwölf neue Features, die mit 12c verfügbar geworden sind. Die meisten habe ich hier wahrscheinlich schon mal erwähnt, aber sehr interessant finde ich:

• die Option DISABLE_ARCHIVE_LOGGING mit der man die Erzeugung von redo log Informationen beim Import massiv reduzieren kann (was nach dem Import natürlich ein neues Basis-Backup erforderlich macht).
• die TRUNCATE CASCADE Option, die für alle über referentielle Constraints verbundenen Child-Tabellen ein Truncate ausführt, wenn die Parent-Tabelle truncated wird.

Dabei räumt TRUNCATE CASCADE die abhängigen Tabellen komplett leer, führt also tatsächlich auch dort ein Truncate und kein Delete durch, wie das folgende Beispiel zeigt:

drop table t_c;
drop table t_p;

create table t_p (id number primary key);

create table t_c (id number primary key, p_id number);

alter table t_c add constraint t_c_p_fk foreign key (p_id) references t_p(id) on delete cascade;

insert into t_p (id) values (1);

insert into t_c (id, p_id) values (1, 1);

insert into t_c (id, p_id) values (2, null);

commit;

select * from t_c;

        ID       P_ID
---------- ----------
         1          1
         2

truncate table t_p cascade;

select * from t_c;

Es wurden keine Zeilen ausgewählt

Hier verschwindet auch der Datensatz mit der Id 2 aus der Child-Tabelle t_c, obwohl er für die FK-Spalte einen NULL-Wert enthält. Jenseits der Inhalte lässt sich das Verhalten auch daran erkennen, dass die DATA_OBJECT_ID der Tabelle in USER_OBJECTS erhöht wird.

Servicepacks für den SQL Server

Brent Ozar zeigt in seinem Blog eine interessante Auswertung: seit November 2012 hat Microsoft keine neuen Servicepacks mehr veröffentlicht - und er stellt die Frage:

what if Microsoft just stopped releasing SQL Server service packs altogether, and the only updates from here on out were hotfixes and cumulative updates? How would that affect your patching strategy? Most shops I know don’t apply cumulative updates that often, preferring to wait for service packs. There’s an impression – correct or not – that service packs are better-tested than CUs.

Interessant ist auch die recht lebendige Diskussion, die sich in den Kommentaren anschließt.