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