Montag, November 24, 2014

Zone Maps in 12c

Nur eine knappe Sammlung von Links zum Thema: Zone Maps sind eine neue Errungenschaft in 12c und stellen eine Index-artige Struktur dar, die es erlaubt, eine Filterung von Blöcken anhand der Definition von Wertebereichen durchzuführen. Dazu sind mir zuletzt mehrere Artikel von Richard Foote und ein Beitrag im Data Warehouse Insider Blog begegnet:
  • 12.1.0.2 Introduction to Zone Maps Part I (Map Of The Problematique): Richard Footes grundsätzliche Beschreibung des Features mit dem einleitenden Hinweis, dass die ZoneMaps eine Lizensierung der Partition Option und von Exadata bzw. SuperCluster erfordert - und unter diesen Umständen vermutlich erst einmal keine allzu breite Nutzung erfahren wird. Anhand eines Beispiels wird das grundsätzliche Verhalten erläutert: ZoneMaps speichern die Minimal- und Maximalwerte einer Spalte (oder Spaltenkombination) innerhalb einer Zone - also eines Bereichs von Blocks (im Umfang von 8 mb). Ausgehend von dieser Information kann der Optimizer ein Pruning irrelevanter Zonen durchführen. Dabei stellen die ZoneMaps selbst ein sehr kleines Segment dar - was angesichts der Definition nicht überrascht.
  • 12.1.0.2 Introduction to Zone Maps Part II (Changes): erklärt das Verhalten der ZoneMaps bei DML-Operationen auf der zugehörigen Tabelle. Der große Unterschied zu Indizes ist dabei, dass ZoneMaps nicht automatisch aktualisiert werden, sofern nicht ein REFRESH ON COMMIT definiert ist - in dieser Hinsicht entspricht ihr Verhalten also dem von Materialized Views. Nach DML-Operationen werden betroffene ZoneMaps als stale markiert, aber nicht betroffene Elemente können weiterhin gefiltert werden. Besonders effektiv sind ZoneMaps erwartungsgemäß bei stark geclusterten Daten.
  • 12.1.0.2 Introduction to Zone Maps Part III (Little By Little): untersucht die Auswirkungen der Daten-Clusterung genauer und anhand von Beispielen - aber am Ergebnis ändert sich nichts.
  • Optimizing Table Scans with Zone Maps: ein Überblick zum Thema vom Oracle Product Management Team, der ähnliche Aussagen enthält wie die Artikel des Herrn Foote (mit weniger Tests, dafür aber mehr Visualisierung).
Insgesamt scheint das Feature durchaus interessant und ähnlichen Konzepten verwandt zu sein, die mir anderswo begegnet sind - etwa den Data-Ids im SSAS-Kontext oder den neuen BRIN Indizes (Block Range Index) in postgres 9.5. Aber die Lizenzierungsvorraussetzungen werden es wohl zumindest vorläufig zu einer exotischen Randerscheinung machen.

Baselines und Parsing

Jonathan Lewis weist darauf hin, dass Baselines keine große Hilfe sind, wenn es darum geht, den Aufwand beim Parsen komplexer Join-Operationen zu verringern. In seinem Beispiel führt ein Join von acht Tabellen zur Prüfung von 5040 Join-Reihenfolgen - wobei die Einstellung von _optimizer_max_permutations=2000 offenbar ignoriert wird - und das ändert sich auch dann nicht, wenn eine SQL Plan Baseline für die Query erzeugt wird, weil der Optimizer zunächst versucht, einen besseren Plan zu erzeugen, ehe er die hinterlegte Baseline berücksichtigt. In entsprechenden Fällen kann es sinnvoll sein, die Hints der Baseline in ein SQL Profile zu übertragen (bzw. die Hints aus dem library cache zu extrahieren, wozu es ein Skript von Kerry Osborne und Randolf Geist gibt, das im Artikel verlinkt ist) - denn die Hints des Profiles reduzieren den Suchraum des Optimizers unmittelbar. Abschließend weist der Autor allerdings darauf hin, dass dieser Trick der Ersetzung der Baseline durch ein Profile nicht in jedem Fall funktioniert, so dass hier im Einzelfall genauere Analyse erforderlich wäre.

Donnerstag, November 20, 2014

ANSI Join Syntax und Prädikatanordnung

Die ANSI-Syntax zur Formulierung von Joins finde ich grundsätzlich deutlich lesbarer als Oracles traditionelle Schreibweise und würde sie vermutlich regelmäßig einsetzen, wenn sie nicht so viele Bugs hervorrufen würde. Einer davon ist gestern in einem OTN-Thread aufgetaucht. Dort hatte sich der Fragesteller darüber gewundert, dass zwei Varianten eines Outer-Joins unterschiedliche Ergebnisse lieferten, je nachdem, ob die ON-Prädikate direkt bei den zugehörigen FROM-Klauseln erschienen, oder getrennt von diesen in einem gemeinsamen abschließenden Block. Mein erster Gedanke war, dass die zweite Variante syntaktisch falsch sein sollte, und mein erstes Beispiel schien diese Annahme zu bestätigen, aber offenbar hängen der Erfolg oder Misserfolg - und auch das Ergebnis - von der Reihenfolge der Bedingungen ab. Dazu ein Beispiel:

-- 12.1.0.2
drop table t1;
drop table t2;
drop table t3;
 
create table t1
as
select rownum id
     , 'AAA' col1
  from dual
connect by level <= 15;
 
create table t2
as
select rownum id
     , 'BBB' col1
  from dual
connect by level <= 15;
 
create table t3
as
select rownum id
     , 'CCC' col1
  from dual
connect by level <= 10;

select /* normal ANSI syntax */
        count(*)
  from t1
  left outer join t3
    on (t3.id = t1.id)
 inner join t2
    on (t3.id = t2.id);
 
  COUNT(*)
----------
        10
 
select /* strange ANSI syntax: predicate order 1, 2 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t1.id)
    on (t3.id = t2.id);

    on (t3.id = t1.id)
                *
FEHLER in Zeile 6:
ORA-00904: "T1"."ID": ungültiger Bezeichner
 
select /* strange ANSI syntax: predicate order 2, 1 */
        count(*)
  from t1
  left outer join t3
 inner join t2
    on (t3.id = t2.id)
    on (t3.id = t1.id);
 
  COUNT(*)
----------
        15

Demnach ergibt sich:
  • die (mehr oder minder) normale Anordnung der Elemente liefert ein Ergebnis mit 10 rows.
  • die erste Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t1, t3-t2) liefert einen Fehler.
  • die zweite Version mit Trennung der FROM-Klauseln von den ON-Prädikaten und einer Anordnung der Prädikate (t3-t2, t3-t1) liefert 15 rows.
Wenn man dazu ein CBO-Trace erzeugt (Event 10053) wird deutlich, dass der Optimizer im Rahmen seiner Transformationen den Outer Join aus der ersten Query ausschließt, während er in der zweiten (lauffähigen) Query erhalten bleibt, woraus sich die 10 bzw. 15 Ergebnissätze ergeben:

-- Final query after transformations (reformatted)
/* normal ANSI syntax */
SELECT COUNT(*) "COUNT(*)"
   FROM "C##TEST"."T1" "T1"
      , "C##TEST"."T3" "T3"
      , "C##TEST"."T2" "T2"
  WHERE "T3"."ID" = "T2"."ID"
    AND "T3"."ID" = "T1"."ID"

/* strange ANSI syntax: predicate order 2, 1 */
SELECT COUNT(*) "COUNT(*)"
  FROM "C##TEST"."T1" "T1"
     , (SELECT "T3"."ID" "ID"
          FROM "C##TEST"."T3" "T3"
             , "C##TEST"."T2" "T2"
         WHERE "T3"."ID" = "T2"."ID") "from$_subquery$_004"
WHERE "from$_subquery$_004"."ID"(+) = "T1"."ID"

Ich muss gestehen, dass ich mir nicht völlig sicher bin, welche der beiden Interpretationen der Join-Bedingungen korrekt ist - grundsätzlich handelt es sich offenbar um eine Frage der Reihenfolgen:
  • wenn zunächst der Outer Join von t1 und t3 erfolgt, der 15 rows liefert, und dann ein Inner Join dieses Ergebnisses an t2 durchgeführt wird und die Verknüpfung über die t3.id erfolgt, die im ersten Zwischenergebnis NULL ist, dann ist das Ergebnis 10 korrekt.
  • wenn zunächst der Inner Join von t2 und t3 erfolgt und dann der Outer Join dieses Zwischenergebnisses an t1, dann sollte sich die 15 ergeben.
Persönlich würde ich in einem solchen Fall wahrscheinlich eine explizite Klammerung über Inline-Views (oder CTEs) durchführen (und hoffen, dass der Optimizer diese Klammerung nicht wieder durch Transformationen beseitigt). Ein Bug scheint jedenfalls zu sein, dass die Reihenfolge der Prädikate darüber entscheidet, ob ein Fehler oder ein Ergebnis zurückgeliefert wird. Ich vermute, dass tatsächlich die Reihenfolge der Prädikate entscheidet, welche Verknüpfung Vorrang besitzt - und möglicherweise sagt die ANSI-Spezifikation mehr darüber aus, wie die Verarbeitungsreihenfolge der Join Operationen gedacht ist - wenn ich dazu noch Hinweise finde, ergänze ich sie. Ein Indiz dafür, dass tatsächlich der Inner Join Vorrang haben sollte, liefert postgres: dort liefern beide Varianten 15 Datensätze.

Korrektur: die abschließende Aussage zu postgres war falsch (basierend auf einem copy&paste-Fehler) - tatsächlich verhält sich postgres exakt genauso wie Oracle und liefert 10 rows, einen Fehler ("ERROR:  invalid reference to FROM-clause entry for table t1. TIP:  There is an entry for table t1, but it cannot be referenced from this part of the query.") bzw. 15 rows.

Nachtrag 21.11.2014: Patrick Barel hat mich in seinem Kommentar davon überzeugt, dass es sich nicht um einen Bug handelt (worauf bereits das postgres-Verhalten hindeutete), sondern um ein definiertes Verhalten. Seltsam sieht es trotzdem aus...

Mittwoch, November 19, 2014

SQL Plan Directive in 12c

Mir ist gerade aufgefallen, dass ich bisher nichts über die in 12c eingeführten SQL Plan Directives geschrieben habe, die mir erstmals im White Paper Optimizer with Oracle Database 12c von Maria Colgan begegnet sind. In der Dokumentation wird die SQL Plan Directive folgendermaßen definiert:
A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive can instruct the optimizer to record a missing extension.
Grundlage für die Anlage einer Direktive sind die Informationen, die über die Automatic Reoptimization Features ermittelt werden. Wenn das Cardinality Feedback (das sich in 12c Statistics Feedback nennt) für eine rowsource Operation eine Abweichung zwischen erwarteter und tatsächlicher Satzanzahl mitteilt, dann wird eine SQL Plan Directive erzeugt, die dem Optimizer mitteilt, bei der nächsten Optimierung der Query zusätzliche dynamic sampling Informationen für Tabellen oder Spaltengruppen zu erfassen - wobei die extended statistics für die column group im Erfolgsfall auch persistent angelegt werden, was die Verwendung der Direktive dann überflüssig machen kann. Die Inhalte der Direktiven sind in den Dictionary Views DBA_SQL_PLAN_DIRECTIVES und DBA_SQL_PLAN_DIR_OBJECTS dargestellt. Direktiven sind nicht an eine SQL_ID gebunden, sondern können für ähnliche Queries herangezogen werden (etwa, wenn die Unterschiede auf der Ebene unterschiedlicher Spalten in der Select-Liste liegen).

Einige interessante Hinweise zu den Änderungen, die sich für die Direktiven in 12.1.0.2 ergeben haben, findet man bei Franck Pachot. Insbesondere erläutert er die Status-Angaben der STATE-Spalte in DBA_SQL_PLAN_DIRECTIVES.

In 12.1.0.1 gab es folgende Status-Angaben:
  • NEW: der Status nach der Anlage der Direktive: zu diesem Zeitpunkt ist nur bekannt, dass es eine Abweichung zwischen erwarteten und tatsächlichen Cardinalities gab.
  • MISSING_STATS: ergibt sich, wenn nach einer folgenden Optimierung (der gleichen oder einer ähnlichen Query) fehlende Statistiken als Ursache der Abweichung bestimmt wurden.
  • HAS_STATS: gibt an, dass die zusätzlichen Statistiken genügen, um die Ausführung zu optimieren.
  • PERMANENT: gibt an, dass die zusätzlichen Statistiken die Fehler in den Schätzungen nicht vermeiden - und deshalb ignoriert werden können.
Für 12.1.0.2 gibt es nur noch zwei Status-Angaben:
  • USABLE: fasst die bisher unter NEW, MISSING_STATS und PERMANENT aufgeschlüsselten Angaben zusammen. Die Semantik ist etwa: die Direktive muss vom Optimizer untersucht werden, aber das Problem ist noch nicht behoben.
  • SUPERSEDED: gibt an, dass die erforderlichen Statistiken erzeugt wurden (HAS_STATS) oder dass die Direktive durch eine andere Direktive überflüssig gemacht wurde.
Demnach ist die Detaillierung der Angaben in 12.1.0.2 geringer, aber die Status-Angaben der älteren Version sind in der (XML-) Spalte NOTES weiterhin verfügbar.

Mein Eindruck ist dabei, dass es sich um ein interessantes Feature mit großem Entwicklungspotential handelt - aber auch um eines, dessen Dokumentation noch eine ganze Menge offener Fragen lässt.

Freitag, November 14, 2014

Materialized Views für postgres

Mit Materialized Views hat sich mein erster Blog-Eintrag zu postgres im Jahr 2005 beschäftigt. Erst seit Version 9.3 gibt es reguläre MViews in postgres, aber Craig Kerstiens urteilt knapp: 
The problem with them is they were largely unusable. This was because they 1. Didn’t auto-refresh and 2. When you did refresh them it would lock the table while it ran the refresh making it unreadable during that time.
In 9.4 ist zumindest das zweite Problem behandelt: mit Hilfe des Befehls REFRESH MATERIALIZED VIEW CONCURRENTLY kann man den Neuaufbau der MView durchführen, ohne massive Locks zu halten - allerdings muss dazu ein unique index existieren, der sämtliche Datensätze enthält.