Mittwoch, April 30, 2014

Foreign Key Indizes und Spaltenreihenfolgen

Richard Foote hat in seinem Blog schon vor einiger Zeit die Frage beantwortet, ob ein zur Vermeidung von Locking Problemen für einen Foreign Key definierter Index in Reihenfolge und Spaltenauswahl der FK-Definition vollständig entsprechen muss - und die Antwort lautet: nein. Ähnlich wie im Fall eines Index zur Unterstützung eines PK-Constraints genügt es, wenn die führenden Spalten des Index denen des Constraints entsprechen, wobei die Reihenfolge keine Rolle spielt. Dazu gibt's dann natürlich auch ein Beispiel, das auch noch einmal die grundsätzlichen Vorteile der FK-Indizierung darstellt (Vermeidung kostspieliger Locks beim Löschen von PK-Einträgen der Parent-Tabelle, die schnell in deadlocks übergehen können).

Nachtrag 09.05.2014: Außerdem hat Richard Foote auch noch gezeigt, dass auch invisible indexes zur Vermeidung von Locking Effekten verwendet werden können, was wieder ein Hinweis darauf ist, dass man beim Löschen anscheinend nutzloser Indizes vorsichtig sein muss.

Donnerstag, April 24, 2014

Geschichte des Nested Loops

Jonathan Lewis erläutert wie sich der NESTED LOOPS Join und seine Repräsentation im Execution Plan im Laufe der Releases seit 8.1.7.4 verändert haben. Ich verzichte auf eine Wiederholung der Details, da ich die meisten davon schon in älteren Beiträgen erwähnt habe.

Dienstag, April 22, 2014

Insert Performance mit Bitmap und B*Tree Index

Bitmap Indizes machen eine Menge Ärger, wenn es um DML geht. Diese Erkenntnis ist nicht neu und weitgehend richtig, und ich habe hier auch schon mal gelegentlich einen - eher anekdotischen - Fall vorgeführt, in dem ein Insert in eine Tabelle mit Bitmap Index deutlich langsamer von statten ging als ein entsprechendes Insert in eine Tabelle mit B*Tree Index (wobei ich bei erneuter Betrachtung des Beispiels darüber nachdenke, ob das Ergebnis nicht ziemlich massiv durch eine unglückliche Dimensionierung der redo log files beeinflusst war). Nun hat Jonathan Lewis einen Artikel zum Thema veröffentlicht, der erklärt, warum ein Insert im Bitmap-Fall auch schneller erfolgen kann als im B-Tree-Fall und inwiefern dieses Verhalten erklären könnte, wieso Bitmap Indizes nicht zur Vermeidung eines Table Locks beim Löschen von Datensätzen aus der zugehörigen Parent-Tabelle verwendet werden können. Das gleiche Thema hat übrigens dieser Tage auch Richard Foote in seinem Blog angesprochen, dabei aber eine andere Argumentationslinie verfolgt und den Artikel nach einem Kommentar des Herrn Lewis auch noch mal überarbeitet.

Die Antwort auf die angesprochene Frage ist - wie das im Fall von Antworten, die man kennt, ja häufiger vorkommt - eigentlich ganz einfach: ein Blick auf die Session Statistiken eines entsprechenden Test-Falls zeigt, dass die Maintenance des B*Tree-Index sehr viel größere Ressourcen erfordert als die des Bitmap-Index, mehr LIOs, mehr Redo und Undo, mehr Memory in PGA/UGA etc. Diese Statistiken habe ich in einem Kommentar im Scratchpad aufgeführt, und Jonathan Lewis schreibt dazu:
As Martin has pointed out, there are a number of statistics that show large differences between the B-tree and bitmap approaches, but the one he didn’t mention was the key: sorts (rows).
Also knapp vorbei - schade eigentlich... Die Lösung lautet: Oracle verschiebt die Bitmap-Index-Maintenance ans Ende der Insert-Operation - in ähnlicher Weise wie das im Fall eines direct path Inserts für alle Index-Typen geschieht. Die vorsortierten Werte werden dann in den Index-Baum ge-merged, was I/O, Block-Splits etc. minimiert. Andererseits macht diese Verzögerung eine Verwendung im Rahmen der FK-Lock-Vermeidung unmöglich. Eine ähnliche Verzögerungsstrategie kann Oracle offenbar auch bei Delete- und Update-Operationen verwenden. Wie gesagt: ganz einfach, wenn man die Lösung kennt.

Cardinality für IN-Listen mit NULL-Werten in 12.1

Ein sperriger Titel für eine einfache Beobachtung. Vor etwas mehr drei Jahren hatte ich hier überprüft, wie sich die von Jonathan Lewis in Cost Based Oracle beschriebene Berechnung von Cardinalities für IN-Listen unterschiedlicher Struktur in neueren Releases verändert hatte. Zu den Fällen, die im Buch untersucht wurde, gehörten:
  • wiederholte Werte
  • Werte außerhalb des Korridors bekannter Werte (not between low_value and high_value)
  • NULL-Werte
Wobei es zu jedem dieser Fälle unterschiedliche Varianten gab. Bereits in 11.2.0.1 verhielten sich alle untersuchten Fälle bis auf einen ganz plausibel. Der verbliebene Fall war die Behandlung von NULL-Werten - und diesen Fall habe ich mir jetzt noch mal mit 12.1.0.1 angesehen:

-- 12.1.0.1
create table test_inlist
as
select rownum pk_col
     , mod(rownum, 12) mon_col
     , lpad('*', 100, '*') pad_col
  from dual
connect by level <= 120000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST_INLIST', estimate_percent=>dbms_stats.auto_sample_size)

select column_name
     , num_distinct
     , num_nulls
     , density
     , num_buckets
  from user_tab_columns
 where table_name = 'TEST_INLIST'
   and column_name = 'MON_COL';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS    DENSITY NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
MON_COL                                  12          0 ,083333333           1

select count(*)
  from test_inlist
 where mon_col in (3, 25, null);

Plan hash value: 3620027018

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |     3 |   529   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_INLIST | 10000 | 30000 |   529   (1)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MON_COL"=3 OR "MON_COL"=25)

Die einschränkende Bedingung enthält somit einen Wert innerhalb des bekannten Wertebereichs (3), einen Wert weit jenseits des bekannten Wertebereichs (25) und einen NULL-Wert. In 11.2.0.1 wurde dafür noch eine Cardinality von 20000 berechnet: 10000 für den Wert 3, 0 für den Wert 25 und wieder 10000 für den Wert NULL. In 12.1 ist das Verhalten korrigiert, denn mon_col in (NULL) liefert natürlich kein Ergebnis, so dass die angenommenen 10000 dem tatsächlichen Ergebnis entsprechen.

Speichernutzungsdetails in V$PROCESS_MEMORY_DETAIL

Im Rahmen seiner epischen Oracle Memory Troubleshooting Reihe (die im Jahr 2009 begann) erläutert Tanel Poder die verschiedenen Informationen zur PGA-Nutzung der Prozesse, die die dynamischen Performance-Views zur Verfügung stellen:
  • v$process: liefert in den Spalten pga_used_mem und pga_alloc_mem einen (in vielen Fällen bereits ausreichenden) Überblick über die PGA Nutzung.
  • v$process_memory: liefert Informationen zur Verteilung dieser Ressourcennutzung auf die Bereiche SQL, PL/SQL, Java, Unused(Freeable) und Other.
  • für den Bereich SQL liefert v$sql_workarea_active Details zur Verteilung des Speicherverbrauchs auf einzelne Schritte in den Ausführungsplänen.
  • für den Bereich "Other" ist die Analyse etwas komplizierter und erforderte früher die Erzeugung eines PGA/UGA memory heapdump (mit Hilfe von oradebug oder durch Aktivierung eines entsprechenden Events für die Session).
  • Seit 10.2 liefert v$process_memory_detail die benötigten Details. Allerdings wird diese View nur auf ausdrückliche Aufforderung gefüllt (oradebug dump pga_detail_get) - und erst dann, wenn der zugehörige Prozess wieder aktiv wird.
  • Die in der Regel eher kryptischen name und heap_name Angaben aus v$process_memory_detail lassen sich dann (hoffentlich) über eine MOS-Suche auflösen.
  • auf OS-Ebene kann zusätzlich pmap -x verwendet werden.
Mit diesem Vorgehen wird die Ermittlung der Informationen sehr einfach - ihre Interpretation bleibt aber auf ergänzende Erläuterungen (aus MOS oder anderer Quelle) angewiesen: ich zumindest kann mit Namen wie "kxsc: kkspsc0 2" erst einmal wenig anfangen.

Samstag, April 19, 2014

Korrigierte CBO-Cardinality-Schätzungen für OR-Prädikate

Stefan Koehler weist in seinem Blog darauf hin, dass ein von Jonathan Lewis in Cost Based Oracle beschriebenes Problem in aktuelleren Releases behoben ist: OR-verknüpfte Range-Prädikate, die den kompletten Wertebereich einer Tabelle umfassen, werden nun in ein einfaches IS NOT NULL Prädikat umgewandelt. Das handliche Beispiel dafür ist in Blog und Buch eine Monatsliste mit der dafür definierten Einschränkung:
where MONTH_NO > 8 OR MONTH_NO <= 8
In 10.2.0.5 führte diese Ausgangslage noch zu einer Addition der ermittelten Cardinality beider Ranges, was zu einer (leichten) Unterschätzung der Anzahl führte (im Beispiel 986 statt 1200). Mit dem IS NOT NULL Prädikat, das der Optimizer hier (zumindest) seit 11.2 über einen Transformationsschritt einsetzen kann, kommt diese Fehleinschätzung nicht mehr zustande - wobei ich die Korrektur auch schon in 11.2.0.1 sehe (aber leider keine 11.1er Systeme zur Hand habe).

Da ich nicht glaube, dass Jonathan Lewis Zeit in eine überarbeitete Neuauflage von Cost Based Oracle investieren wird (was aus meiner Sicht bedauerlich, aber auch sehr verständlich ist), halte ich derartige Überprüfungen des im Buch beschriebenen Verhaltens unter Verwendung aktueller Releases für extrem nützlich und wichtig.

Donnerstag, April 17, 2014

Zur Semantik der ON clause im ANSI Left Outer Join

Vielleicht wäre es sinnvoller, auf diesen Eintrag zu verzichten, weil er ein wenig peinlich ist - aber was soll's: gestern ist mir im Rahmen eines OTN Threads aufgefallen, dass meine Interpretation der ANSI OUTER JOIN Syntax bislang unzutreffend war. Ich verzichte auf nähere Erläuterungen zu meiner Fehleinschätzung (die für meine übliche Formulierung von ANSI OUTER JOINs ganz plausibel war), und erkläre lieber gleich, was das ON tatsächlich bedeutet:

drop table t1;
drop table t2;

create table t1
as
select rownum id
     , 0 col1
  from dual
connect by level <= 10;

create table t2
as
select rownum id
     , 1 col1
  from dual
connect by level <= 5;

-- Fall 1:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6
         7
         8
         9
        10

-- Fall 2:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id and t2.col1 = 0)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

-- Fall 3:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id and t1.col1 = 1)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

Fall 1 ist dabei völlig harmlos: das ON enthält nur die Join-Bedingung, so dass der OUTER JOIN zu jedem Satz aus t1 den passenden Wert aus t2 verknüpft und die in t2 fehlenden Werte durch NULL ergänzt.

Fall 2 ist auch einleuchtend: in der Join-Bedingung erscheint das Prädikat t2.col1 = 0, das für alle Sätze in t2 false liefert, so dass die T2_ID immer mit NULL gefüllt wird.

Fall 3 ist der, der mir nicht klar war - auch, weil ich noch nicht auf die Idee gekommen war, eine solche Einschränkung im ON zu verwenden. Auch für t1.col1 = 1 gilt, dass die Bedingung immer false ist, aber sie wirkt sich nicht auf die Filterung von t1 aus, sondern führt auch nur dazu, dass die Join-Bedingung für alle Vergleiche false ist, so dass für T2_ID wiederum ausschließlich NULL-Werte erscheinen.

Demnach gilt: die ON clause im LEFT OUTER JOIN definiert, zu welchen Sätzen der Tabelle auf der linken Seite Sätze aus der rechten Tabelle verknüpft werden sollen. Das klingt erst mal selbstverständlich, führt aber nicht unbedingt zu intuitiv verständlichen Bedingungen - sofern man den Mechanismus nicht klar vor Augen hat.

Jonathan Lewis hat mich in diesem Zusammenhang auf einen seiner Artikel verwiesen, in dem er zu einem ähnlichen Beispiel erklärt:
If you’re familiar with ANSI SQL you won’t need more than a couple of moments to interpret the following query – but I have to admit that I had to think about it carefully before I figured out what it was trying to achieve.
Dass auch der Herr Lewis ernsthaft darüber nachdenken musste, ist dann doch wieder beruhigend.

Nebenbei sei noch erwähnt, dass der OTN Thread einen recht massiven Bug für den LEFT OUTER JOIN mit ANSI Syntax in 12c aufzeigt und dass Oracles interne Umformulierung solcher zusätzlicher Bedingungen im ON ziemlich merkwürdig ausfällt.

Und noch ein kleiner Nachtrag: vor einiger Zeit hatte ich hier schon mal über das Thema der OUTER JOIN-Einschränkungen geschrieben, war aber nicht weiter als bis Fall 2 gekommen.

Nachtrag 22.04.2014: auf der Suche nach anderen Dingen bin ich in meinen Archiven auf eine Betrachtung der Semantik des ON im Merge gestoßen, die mir offenbar gelegentlich schon mal ähnliche Kopfschmerzen bereitet hat. Vermutlich sollte ich hier nicht nur Notizen machen, sondern diese bisweilen auch noch mal lesen ...

Mittwoch, April 16, 2014

Read Committed Isolation Level im SQL Server

Im SQLPerformance.com Blog hat Paul White einige interessante Punkte zur Implementierung des Isolation Levels Read Committed im SQL Server aufgeschrieben. Grundsätzlich gibt es im SQL Server dieser Tage zwei Implementierungen für Read Committed, von denen die ältere (zumindest vom Herrn White) als Locking Read Committed bezeichnet wird. Da der Autor einen weiteren Artikel zum Thema ankündigt, starte ich an dieser Stelle eine Aufzählung:
  • The Read Committed Isolation Level: die traditionelle Implementierung des Isolation Levels Read Committed ist im SQL Server aktiv, wenn der Parameter READ_COMMITTED_SNAPSHOT auf OFF gesetzt ist. Sie hält ein möglichst kurzes shared lock auf gelesene rows und pages, das nach der Leseoperation sofort wieder frei gegeben wird (und konkurrierende Transaktionen kurzfristig aussperrt, weil diese zur Datenänderung ein exklusives Lock benötigen würden). Das Verfahren entspricht wohl den Anforderungen des SQL Standards (sagt jedenfalls der Herr White, wobei er allerdings nicht genau bestimmt, von welchem Standard er spricht), bringt aber ein paar sehr bizarre Effekte mit sich. Ein Statement:
    • kann die selbe Zeile mehrfach lesen (wenn sie durch eine konkurrierende Änderung ihre Position im Index bzw. in der Tabelle ändert).
    • kann eine Zeile komplett verpassen.
    • liefert keine konsistente point-in-time-Sicht der Daten: das Ergebnis einer Query kann also ein Zustand sein, den es in dieser Form in den Daten nie gegeben hat.
  • Read Committed Snapshot Isolation: erläutert das Verhalten bei Verwendung von RCSI (also Read Committed Snapshot Isolation). Hier werden konsistente Daten für den Startzeitpunkt der Query gelesen - das Verhalten ist also jenes, das man von Oracles Implementierung von Read Committed her kennt. Der Herr White erläutert relativ ausgiebig die Fälle, in denen dieses Verhalten nicht das gewünschte sein könnte:
    • "RCSI is typically not a good choice for any T-SQL code whose correctness depends on blocking if a concurrent uncommitted change exists." Nun ja, dieser Fall scheint mir halbwegs in den Bereich von dirty read zu reichen.
    • "RCSI might also not be the right choice if the code depends on reading current committed data, rather than the latest committed data as at the time the statement started." Für lang laufende Queries könnte das tatsächlich ein potentielles Problem sein - allerdings halte ich die Standard-Implementierung des SQL Servers nicht für eine sinnvolle Lösung dazu.
    • Details zur technischen Implementierung und den erforderlichen Locks liefert der Folge-Artikel Data Modifications under Read Committed Snapshot Isolation.
    • Weitere Informationen zu möglichen Effekten und Fehlern liefert The SNAPSHOT Isolation Level. Interessant sind dabei einerseits die spezifischen Unterschiede der Implementierung zum Ansatz von Oracle (etwa die Fehlermeldungen beim konkurrierenden Update oder beim Truncate) und andererseits die offensichtlich erforderlichen Verhaltenserklärungen für Aspekte, die dem Oracle-User selbstverständlich erscheinen (Stichwort: Write Skew). Manchmal ist es ganz nützlich, sich klar zu machen, dass Selbstverständlichkeiten von Standpunkt und Perspektive abhängen...
Der Autor präsentiert diese Informationen eher wertneutral ("We also have to bear in mind that many production queries only really need an approximate or best-effort answer to some types of question anyway. The fact that some rows are missing or double-counted might not matter much in the broader scheme of things."), aber ich erinnere mich noch daran, wie ungläubig ich reagiert habe, als ich erstmals mit diesem Verhalten konfrontiert wurde. Meiner Meinung nach ist diese Implementierung von Read Committed in einem OLTP System nahezu unbrauchbar (und ein sehr starkes Argument für Oracles Lesekonsistenzmechanismen). Aber inzwischen gibt es ja die Snapshot-Alternative, die Paul White in einem Folgeartikel erläutern will, den ich dann hier ergänzen werde (wenn ich es nicht vergesse).

Dienstag, April 08, 2014

Ausführungspläne erzeugen und interpretieren

Bisher habe ich auf die Frage nach einer guten Erklärung für Ausführungspläne in der Regel auf Troubleshooting Oracle Performance von Christian Antognini verwiesen - oder auch auf die im Netz verfügbare (und hier verlinkte) gekürzte Version des sechsten Kapitels. Als Alternative oder Ergänzung dazu kommt inzwischen eine gerade von Jonathan Lewis für AllThingsOracle begonnene einführende Serie in Betracht:
  • Execution Plans: Part 1 Finding plans: erklärt die verschiedenen Möglichkeiten, wie man Pläne anzeigen lassen kann (Explain Plan, Autotrace, dbms_xplan.display_cursor), und weist auf ihre besonderen Eigenschaften und Beschränkungen hin (Stichwort: Explain Plan und Bindevariablen).
  • Execution Plans Part 2: Things to see: erläutert die Format-Optionen für dbms_xplan.display_cursor (Outline Data, Peeked Binds, Predicate Information). Außerdem werden die Möglichkeiten des Zugriffs auf historische Pläne im Automatic Workload Repository über dbms_xplan.display_awr erläutert. Neu war mir die Möglichkeit, eine Query über dbms_workload_repository. add_colored_sql({sql_id}) zu taggen, um dadurch sicher zu stellen, dass ein Statement im AWR nicht übersehen wird.
  • Execution Plans Part 3: “The Rule”: erläutert die Grundregeln für die Interpretation von Plänen (deren Ausnahmen in folgenden Artikeln erklärt werden):
    • Einrückungen in der Plandarstellung erklären die Semantik der Operationen.
    • Jede Operation hat eine Id und nahezu jede eine ParentId.
    • Der angebotene Merksatz für die Interpretation lautet "first child first, recursive descent" - was mir nicht allzu selbsterklärend erscheint; allerdings fällt mir auch nichts Besseres dazu ein. Entscheidend ist jedenfalls die Suche nach der am weitesten oben erscheinenden child-Operation ohne eigene Kinder, die den Start der Verarbeitung darstellt (was jetzt aber auch nicht griffiger formuliert ist).
    • Pläne lassen sich in der Regel sehr gut in Teilbereiche zerlegen - "there are no hard plans, there are only long plans".
    • Die Interpretation wird anhand eines übersichtlichen HASH JOIN Beispiels vorgeführt.
  • Execution Plans Part 4: Precision and Timing: erklärt die Rolle blockierender Operationen: im Hash Join muss die in-memory hash table komplett aufgebaut sein, ehe die probe Zugriffe für die zweite Tabelle erfolgen können, die ihrerseits sukzessive ausgeführt werden können. Im Fall eines enstprechenden Merge Joins müssen beide Input-Mengen sortiert sein (sofern sie nicht schon sortiert sind - also etwa über einen entsprechenden Index eingelesen), es existieren also zwei blockierende Operationen. Im Fall des Merge-Joins zeigt der Plan mit rowsource statistics eine seltsame Angabe für die Starts der SORT JOIN Operation: die Sortierung findet natürlich nur einmal statt, aber der Schritt im Ausführungsplan wäre mit der sperrigeren Beschreibung "probe an in-memory sorted data set, but acquire it and sort it if it’s not already in memory" besser umschrieben. Es folgt eine kurze Erläuterung zu Access- und Filter-Prädikaten - erster dienen dem Zugriff, letztere führen eine Filterung der gelieferten Ergebnisse durch. Alle Beispiele profitieren von der Verwendung von Plänen mit rowsource statistcs.
  • Execution Plans part 5: First Child Variations: zeigt einige Fälle, in denen die "first child first, recursive descent" Regel nicht greift. Betroffen sind unter anderem scalar subqueries (dort ist das letzte child des Select die treibende Operation) und subquery factoring.
  • Execution Plans Part 6: Pushed Subqueries: zeigt - anhand von zwei sehr ähnlichen Beispielen -, dass die "first child first" Regel bei Verwendung von Pushed Subqueries nicht funktioniert, weil die zugehörige Filter-Bedingung nur in der predicate section des Plans repräsentiert ist. Dabei bedeutet das "Pushed" für die Subquery, dass sie zum frühestmöglichen Zeitpunkt ausgeführt wird. Dabei führt die Verschiebung der Subquery-Ausführung zu einer starken Veränderung der Reihenfolgen-Semantik im Plan. Der Aufbau der zugehörigen Test-Queries erfordert den Einsatz der Hints no_unnest (um zu verhindern, dass eine Subquery in einen Join umgeandelt wird) und push_subq (um das Pushen der Subquery zu erzwingen). Leider werden die Queries in den Filter-Prädikaten nicht korrekt dargestellt (sondern durch leere Klammern repräsentiert), wenn der Plan aus dem Speicher gezogen wird (dbms_xplan.display_cursor).
  • Execution Plans Part 7: Query Blocks and Inline Views: erläutert die Rolle von Query Blocks, die man mit Hilfe des Hints qb_name explizit benennen kann und die in der Predicate Section sichtbar sind. Darüber hinaus wird auf die View-Elemente (VW_%), hingewiesen die als Ergebnisse von Transformationsprozessen im Plan erscheinen können (im Beispiel etwa durch die Umwandlung einer korrelierten Subquery in einen einfachen Join mit dem Subquery-Ergebnis). Außerdem weist Jonathan Lewis mal wieder darauf hin, dass der Optimizer kein Freund der ANSI-Join-Syntax ist - in gegebenen Fall funktioniert die Zuordnung der qb_name-Angaben nicht mehr so ganz, wenn ANSI-Joins verwendet werden (weil die interne Transformation von ANSI- in Native-Oracle-Syntax manchmal überraschende Dinge tut).
  • Execution Plans Part 8: Cost, time, etc.: erläutert, wie man die Spalten des Execution Plans zu den Kosten und zum Timing interpretiert (also: Rows, Bytes, Cost und Time). Dabei entsprechen die Erklärungen denen, die der Autor an anderer Stelle gegeben hat, und die ich an anderer Stelle reproduziert habe - ich spare mir die Wiederholung. Insbesondere wird auf die diversen Ungenauigkeiten der Angaben (etwa bei den Bytes) hingewiesen.
  • Execution Plans Part 9: Multiplication: zeigt die Schwierigkeiten, die sich bei der Interpretation von Plänen daraus ergeben können, dass child Operationen mehrfach ausgeführt werden. In diesem Fall ist die Berechnung der Kosten nicht folgerichtig - insbesondere, wenn nlj_batching bei einem Nested Loops im Spiel ist, weil die Kosten-Darstellung für Nested Loops diesen neuen Trick nicht berücksichtigt. Die Informationen sind folglich cum grano salis zu nehmen...
  • Execution Plans Part 10: Guesswork: erläutert die Schwierigkeiten, die sich aus der Repräsentation von Filter-Steps im Plan ergeben - ein Aspekt ist dabei (wieder) die unbekannte Anzahl der Ausführungen (die im Plan ausgewiesenen cardinalities hängen dabei mit der Rolle von scalar subquery caching zusammen). In solchen Fällen muss die Analyse diverse Faktoren in den Plan hineinrechnen, die dort nicht explizit enthalten sind. Außerdem variieren die Schätzungen für cost und cardinality massiv, wenn unterschiedliche Zugriffsstrategien im Spiel sind (die im Beispiel über Hints hervorgerufen werden) - und das sollten sie natürlich nicht tun.
  • Execution Plans Part 11: Actuals: beschäftigt sich mit rowsource statistics, workarea Informationen (samt einer Query zur Bestimmung potentiell interessanter Fälle) und der Interpretation der gelieferten Ausführungsstatistiken. Interessant ist dabei der Hinweis, dass der Zugriff auf v$sql_workarea einen Scan des gesamten library caches hervorruft und mit Bedacht durchgeführt werden sollte.
  • Execution Plans Part 12: Cardinality Feedback: erklärt, wie man mit Hilfe von rowsource Statistiken und dem Vergeleich von E-rows und A-rows die entscheidenden Irrtümer der Cardinality-Schätzungen bestimmen kann. Insbesondere wird darauf hingewiesen, dass der erste größere Fehler bei der Abschätzungen alle folgenden Entscheidungen des Optimizers beeinflusst. In den zugehörigen Beispielen wird ein unglücklichen NL-Join analysiert, daneben werden Subquery-Ergebnis-Caching-Probleme beleuchtet (die sich ergeben, wenn die Runtime Engine nicht dazu in der Lage ist alle distinkten Ergebnisse einer Subquery im Speicher zu halten, so dass sich wiederholte Zugriffe ergeben). In einem Nebensatz wird das 11er Feature Cardinality Feedback angesprochen, das im Beispiel zu einer Reoptimierung und der Generierung ergänzender Hints führt, was im hier verlinkten Artikel und den zugehörigen Kommentaren genauer untersucht wurde.
  • Execution Plans Part 13: SQL Trace: erläutert die Möglichkeiten der Aktivierung der Trace-Erfassung (Level, Optionen, Scope: Session oder Statement) und gibt knappe Hinweise zur Deutung der Ergebnisse im erzeugten File und seiner via tkprof formatierten Version.
  • Execution Plans Part 14: SQL Monitoring: liefert Informationen zur Verfügbarkeit des Monitorings und zur Auswertung der Resultate in HTML und textueller Form.
      Ein Index zu den Artikeln der Serie, den man als Antwort auf entsprechende Anfragen in Datenbank-Foren verwenden kann, findet sich hier.

      Montag, April 07, 2014

      Datenbank-Videos von Jens Dittrich

      An der Universität des Saarlandes habe ich allerlei studiert, aber keine Informatik - trotzdem hätte ich auf die ebenso umfangreiche wie interessante Sammlung der Youtube-Videos von Jens Dittrich vielleicht schon früher mal hinweisen können. Hier findet man Einführendes aber auch komplexe Detailanalysen - und dabei zeichnen sich die Beiträge insbesondere auch durch die unterhaltsame Präsentation aus, für die der Herr Professor Dittrich schon allerlei Preise gewonnen hat. Einmal mehr muss ich bei Carl Einstein borgen: "Vor Leistung ist Lob töricht. Ich stelle meine Bewunderung fest."

      Samstag, April 05, 2014

      Änderung für NVL costing

      Jonathan Lewis weist in seinem Blog darauf hin, dass die cardinality für Prädikate der Form: NVL(spalte, konstanter_datumswert) bis Version 11.1.0.7 mit dem Standardwert "5% for range-based predicate on function(col)" berechnet wurde, aber in 11.2.0.4 eine plausiblere Behandlung erfährt, die das Prädikat in den NULL-Fall und den Fall des Vergleichs der vorhandenen Werte aufspaltet, und dadurch deutlich zutreffendere Schätzungen liefert (was für ähnlich gelagerte Fälle auch schon in früheren Releases möglich war). Zwischen 11.2.0.1 und 11.2.0.3 scheint die Überarbeitung des Verhaltens allerdings erst einmal in die falsche Richtung geführt zu haben (wie die Kommentare zum Artikel zeigen). Erinnerungswürdig ist aber in jedem Fall dass dies wieder eine jener Optimierungen ist, die beim Upgrade eines Systems zur recht massiven Planänderungen führen können.

      Dienstag, April 01, 2014

      Add Column DDL-Optimierung in 11g

      Im OTN-Forum wurde heute ein Feature erwähnt, das mir bisher entgangen war (oder das ich schon wieder vergessen hatte): das Hinzufügen einer Spalte mit Default-Wert und NOT NULL Constraint kann ohne Update der betroffenen Datensätze durchgeführt werden und erfordert als reine Metadaten-Anpassung nur sehr wenig Zeit. Ohne den NOT NULL Constraint muss hingegen jeder einzelne Datensatz aktualisiert werden, was sehr lange Laufzeiten hervorruft. In 12c ist inzwischen auch die Variante ohne den NOT NULL Constraint optimiert. Ein (von Mohamed Houri ergänzter) Blick auf die Ausführungspläne von Queries, die über eine solche Spalte einschränken, zeigt ein NVL-Prädikat, das die vorhandenen NULL-Werte in den default-Wert umwandelt - aufgrund des Constraints kann die Spalte ja keine "echten" NULL-Werte enthalten, so dass das Verfahren damit keine Probleme haben kann. In 12c ist dieses ergänzte Prädikat im Plan nicht mehr sichtbar, aber im CBO-Trace kann man die Unterschiede erkennen:

      -- 12.1.0.1
      -- dbms_xplan.display for both queries
      
      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------
      Plan hash value: 3474700848
       
      ----------------------------------------------------------------------------
      | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |       |     1 |    13 |     5   (0)| 00:00:01 |
      |   1 |  SORT AGGREGATE    |       |     1 |    13 |            |          |
      |*  2 |   TABLE ACCESS FULL| TBL_1 |    10 |   130 |     5   (0)| 00:00:01 |
      ----------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - filter("COL_4"=100)
       
      
      -- event 10053 with NOT NULL constraint for col_4
      
      ============
      Plan Table
      ============
      --------------------------------------+-----------------------------------+
      | Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
      --------------------------------------+-----------------------------------+
      | 0   | SELECT STATEMENT    |         |       |       |     5 |           |
      | 1   |  SORT AGGREGATE     |         |     1 |    13 |       |           |
      | 2   |   TABLE ACCESS FULL | TBL_1   |    10 |   130 |     5 |  00:00:01 |
      --------------------------------------+-----------------------------------+
      Predicate Information:
      ----------------------
      2 - filter(NVL("COL_4",100)=100)
      
      
      -- event 10053 without NOT NULL constraint for col_4
      
      ============
      Plan Table
      ============
      --------------------------------------+-----------------------------------+
      | Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
      --------------------------------------+-----------------------------------+
      | 0   | SELECT STATEMENT    |         |       |       |     5 |           |
      | 1   |  SORT AGGREGATE     |         |     1 |    13 |       |           |
      | 2   |   TABLE ACCESS FULL | TBL_1   |    10 |   130 |     5 |  00:00:01 |
      --------------------------------------+-----------------------------------+
      Predicate Information:
      ----------------------
      2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00003$",0)),NULL,NVL("COL_4",100),'0',NVL("COL_4",100),'1',"COL_4")=100)
      

      Offenbar wird im Fall der ergänzten Spalte ohne NOT NULL constraint eine zusätzliche virtuelle Spalte vom Typ RAW ergänzt, deren Inhalt gesetzt wird, so bald in col_4 ein veränderter Wert eingetragen wird - und über die SYS_OP_VECBIT function (die Jared Still gelegentlich erwähnt: "Return the value of the bit at position N in a raw value" - aber sie ist natürlich nicht offiziell dokumentiert) kann bestimmt werden, ob in der Spalte ein Wert zu finden ist: also ein Update nach der initialen Definition der Spalte erfolgte.