Donnerstag, November 27, 2014

Privilege Analysis mit 12c

Eine kleine persönliche Beichte, die regelmäßige Leser vielleicht nicht allzu sehr überrascht: Performance-Fragen interessieren mich deutlich mehr als solche der Security. Und noch ein Geständnis: ich habe gelegentlich Applikationen ausgeliefert, deren Berechtigungen größer waren als erforderlich - weil es mir zu mühsam und zu öde war, mich bis ins Detail mit der Privilegienvergabe zu beschäftigen. Für Entwickler, die unter ähnlichen Charakterschwächen leiden, gibt es in 12c jetzt ein großartiges neues Feature: die Privilege Analysis, bei der man einem User initial DBA-Rechte gibt, dann die Applikation laufen lässt und anschließend feststellen kann, welche Rechte tatsächlich benötigt werden. Ein kompaktes Beispiel für die Verwendung findet man bei Franck Pachot,

Mittwoch, November 26, 2014

Instanz-Crash bei Latch-Analyse

Stefan Koehler analysiert bei SAP on Oracle eine Situation, in der das manuelle Setzen und Freigeben von Latches über Oradebug zu einem Instanz-Crash führt. Relevanter als das Ergebnis ist für mich die methodische Vorgehensweise bei der Analyse - sollte ich mich mal wieder mit Latches beschäftigen wollen, dann wäre es nützlich, diesen Artikel noch mal intensiver anzuschauen.

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...