Function Based Indexes und Skip Scans

Zur Vervollständigung des Titels müsste hier eigentlich nur noch stehen: ... funktionieren zusammen nicht. Oder vielleicht etwas ausführlicher: für Function Based Indexes ist der Zugriff über Index Skip Scan nicht möglich. Untersucht hat den Fall Jonathan Lewis, ausgehend von einem Thread im OTN Forum. Dabei ist das Problem vermutlich recht alt, spielt praktisch aber vermutlich selten eine größere Rolle.

Fehlende Baseline-Informationen im AWR

Dominic Brooks weist in seinem Blog darauf hin, dass die Verwendung von SQL Plan Baselines von DBMS_XPLAN.DISPLAY_CURSOR explizit erwähnt wird, in den Angaben von DBMS_XPLAN.DISPLAY_AWR aber fehlt. Seine Untersuchung enthält neben einer umfassenden Beschreibung des Analysevorgehens auch noch einen recht komplizierten Workaround, bei der der PHV2-Wert (also Plan Hash Value 2) aus der OTHER_XML-Spalte von DBA_HIST_SQL_PLAN mit dem entsprechenden Wert in SYS.SQLOBJ$ gejoint wird und auch noch weitere AWR-Tabellen dazugenommen werden, um die Baselineverwendung auf indirektem Weg zu ermitteln - aber das sieht eher wacklig aus.

Aufrunden zur nächsten Zehnerpotenz

Ein kleiner Ausflug in die Welt der nicht ganz so hohen Mathematik: wie berechne ich die nächst höhere Zehnerpotenz zu einem gegebenen Wert mit Datenbankmitteln? Mir fiel dazu nichts Besseres ein als das Aufrunden des Ergebnisses der log-Funktion zum gegebenen Wert und die Verwendung dieses aufgerundeten Ergebnisses als Exponent zur Basis 10. Hier die Varianten für postgres und Oracle:

-- postgres
dbadmin=# select 10 ^ ceiling(log(4711)) round_up;
 round_up
----------
    10000

-- Oracle
SQL> select power(10, ceil(log(10, 4711))) round_up from dual;

  ROUND_UP
----------
     10000

Performance unterschiedlicher Median-Berechnungsverfahren im SQL Server

Aaron Bertrand untersucht im SQL Performance Blog die Performance verschiedener Varianten der Berechnung von Median-Werten (auf Gruppenebene), die mit unterschiedlichen Versionen des SQL Servers (2000-2014) verfügbar wurden. Interessant ist, dass keineswegs eine deutliche Aufwärtsentwicklung bei der Verwendung neuer Features zu beobachten ist: insbesondere die Verwendung der analytischen Funktion PERCENTILE_CONT führt zu extrem schlechten Ergebnissen und der Einsatz der In-Memory Optionen von Hekaton bringt keine deutlichen Verbesserungen gegenüber der Verwendung einer traditionellen Tabelle. Die Analyse beschränkt sich auf Laufzeiten (enthält also keine Untersuchung der Ausführungspläne), beinhaltet aber eine genaue Beschreibung des Testszenarios und umfangreiche Erläuterungen.

Verschwundener Hint

Ich betrachte Hints als ein Instrument, das man mit Bedacht einsetzen und in Produktivsystemen nach Möglichkeit vermeiden sollte, habe sie aber auch dort schon eingesetzt, wenn ich mir anders nicht zu helfen wusste. Darüber hinaus denke ich, dass sie zu Diagnosezwecken ausgesprochen nützlich sind. Hints weisen den Optimizer dazu an, im Falle bestimmter Entscheidungen einer Vorgabe zu folgen (und sind somit keine Empfehlungen, sondern Direktiven), wodurch sich Fehler des Optimizers korrigieren lassen, aber unter Umständen auch sinnvolle Reaktionen des Systems auf veränderte Bedingungen (also Statistiken) verhindert werden. Ein weiteres Argument gegen ein allzu großes Vertrauen in Hints nennt Jonathan Lewis: manchmal verschwinden sie einfach zwischen den Releases. So geschehen mit (no_)cluster_by_rowid, der in 11.2.0.4 verfügbar war, aber in 12.1.0.1 nicht mehr aufzufinden ist, obwohl er unter bestimmten Umständen (insbesondere im Exadata-Kontext) sehr hilfreich sein konnte.

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.