View-Elemente im Execution Plan

Jonathan Lewis erläutet in seinem Blog die Regeln der Namensgebung von View-Schritten, die in einem Ausführungsplan erscheinen.

Join Factorization

Im Blog der cbo Entwickler findet sich eine Erläuterung zum neuen Feature der Join Factorization, das mit Version 11.2 eingeführt wurde. Dabei werden UNION ALL Operationen, bei denen ein Zugriff auf eine bestimmte Tabelle in beiden vereinigten Mengen stattfindet, so umgeschrieben, dass die mehrfach verwendete Tabelle außerhalb des UNION ALL an die übrigen Ergebnisteile gejoint wird. Auf diese Fälle kann der mehrfache Zugriff auf die gleichen Daten vermieden werden. Offenbar werden SET-Operationen in Oracle nicht so gut unterstützt wie Joins (was erst mal schade ist, da SET-Operationen so verständlich sind - finde ich jedenfalls); aber immerhin kann der Optimizer offenbar immer mehr clevere Umformungen einsetzen, um diese Defizite zu verkleinern.

In einem anderen Zusammenhang verweist Jonathan Lewis auf eine Präsentation von Joze Senegacnik, die sich ebenfalls mit dem Thema beschäftigt, und die über einen Link geladen werden kann (nach vorheriger Anmeldung), den Oscar de la Torre in seinem Kommentar liefert.

Table Compression und Block Dump

Gestern hatte ich die Vermutung geäußert, dass ein Block Dump möglicherweise weitere Hinweise auf die technische Umsetzung der Table Compression liefern könnte. Dazu habe ich mir mit Hilfe der ORA_ROWSCN-Funktion jeweils einen Block aus einer komprimierten und einer nicht komprimierten Tabelle des gleichen Inhalts herausgesucht und dann gedumpt (die Tabellen waren compress_str_1_nocomp und compress_str_1 aus dem gestrigen Test):

-- compress_str_1: komprimiert  
alter system dump datafile 4 block 184331;
-- compress_str_1_nocomp: nicht komprimiert  
alter system dump datafile 4 block 187019;

Im Block Dump sieht man sehr viele Details, die mir zum größten Teil unklar sind, aber auch ein paar Punkte, die zu einer Deutung einladen. Zunächst erscheint ein Block, der - abgesehen von den Ids der Objekte und internen Strukturen - in beiden Varianten nahezu identisch ist:

Start dump data blocks tsn: 4 file#:4 minblk 184331 maxblk 184331
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16961547
BH (0x000007FF47FDDCB8) file#: 4 rdba: 0x0102d00b (4/184331) class: 1 ba: 0x000007FF47C9E000
  set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 53,28
  dbwrid: 0 obj: 72311 objn: 72311 tsn: 4 afn: 4 hint: f
  hash: [0x000007FF5E497448,0x000007FF5E497448] lru: [0x000007FF4CFB4E20,0x000007FF47FD9B20]
  ckptq: [NULL] fileq: [NULL] objq: [0x000007FF5AC0A538,0x000007FF47FD9B48]
  st: XCURRENT md: NULL tch: 3
  flags: only_sequential_access
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0102d00b (4/184331)
scn: 0x0000.001b0d85 seq: 0x01 flg: 0x04 tail: 0x0d850601
frmt: 0x02 chkval: 0xf5cc type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000000E152400 to 0x000000000E154400
00E152400 0000A206 0102D00B 001B0D85 04010000  [................]
00E152410 0000F5CC 11E48C01 00011A77 001B0D80  [........w.......]
00E152420 00000000 00320003 0102D008 0000FFFF  [......2.........]
00E152430 00000000 00000000 00000000 00008000  [................]
...

In der komprimierten Version folgt dann ganz am Ende des Abschnitts (Zeile 509-511) ein Block, der den einzigen Spaltenwert der Tabelle genau einmal enthält:

00E1543D0 2C000101 00010100 61E9CF02 61616161  [...,.......aaaaa]
00E1543E0 61616161 61616161 61616161 61616161  [aaaaaaaaaaaaaaaa]
00E1543F0 61616161 30303161 30303030 0D850601  [aaaaa1000000....]

In der nicht komprimierten Version erscheint dieser Wert ab Zeile 74 (und bis Zeile 501) immer wieder:

00E152940 61210100 61616161 61616161 61616161  [..!aaaaaaaaaaaaa]
00E152950 61616161 61616161 61616161 30303161  [aaaaaaaaaaaaa100]
00E152960 30303030 2101002C 61616161 61616161  [0000,..!aaaaaaaa]
00E152970 61616161 61616161 61616161 61616161  [aaaaaaaaaaaaaaaa]
00E152980 30316161 30303030 01002C30 61616121  [aa1000000,..!aaa]
00E152990 61616161 61616161 61616161 61616161  [aaaaaaaaaaaaaaaa]
00E1529A0 61616161 31616161 30303030 002C3030  [aaaaaaa1000000,.]
...

Dann folgt in beiden Fällen wieder eine nahezu identische Passage, die auch die ITL-Sektion des Blocks darstellt:

 Object id on Block? Y
 seg/obj: 0x11a78  csc: 0x00.1b0e50  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x102da88 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.001b0e50
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0102da8b
data_block_dump,data header at 0xe15247c 

Anschließend folgt auf eine Trennlinie ein Abschnitt mit Statistik-Informationen, von denen ich aber nur wenige entschlüsseln kann:

tsiz: 0x1f80
hsiz: 0x184
pbl: 0x0e15247c
     76543210
flag=--------
ntab=1
nrow=185
frre=-1
fsbo=0x184
fseo=0x4c3
avsp=0x33f
tosp=0x33f

Die nrow-Angabe zeigt die Anzahl der Sätze im Block (185 für den unkormprimierten Fall, 720 für den komprimierten) und einige andere Werte hat Jonathan Lewis gelegentlich erläutert (avsp - available space, fsbo - beginning of free space, fseo - end of free space). Einen direkten Bezug zur Komprimierung hat offenbar der nächste Abschnitt, der nur im Fall der Compression erscheint:

    r0_9ir2=0x0
    mec_kdbh9ir2=0x0
                  76543210
    shcf_kdbh9ir2=----------
              76543210
    flag_9ir2=--R----C    Archive compression: N
        fcls_9ir2[2]={ 0 32768 }

Was das genau bedeuten mag, kann ich nicht sagen.

In beiden Fällen folgt dann eine Liste der Rows mit Offset-Angaben:

0xe:pti[0]    nrow=185    offs=0
0x12:pri[0]    offs=0x1f5b
0x14:pri[1]    offs=0x1f36
0x16:pri[2]    offs=0x1f11
0x18:pri[3]    offs=0x1eec
0x1a:pri[4]    offs=0x1ec7
0x1c:pri[5]    offs=0x1ea2
0x1e:pri[6]    offs=0x1e7d
...

Anschließend folgt in beiden Fällen ein Abschnitt, der offenbar weitere Details zu den einzelnen Sätzen liefert:

-- komprimiert 
block_row_dump:
tab 0, row 0, @0x1f5c
tl: 36 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30
bindmp: 02 cf e9 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 31 30 30 30 30 30 30
tab 1, row 0, @0x1f57
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30
bindmp: 2c 00 01 01 00
tab 1, row 1, @0x1f52
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30
bindmp: 2c 00 01 01 00

-- unkomprimiert
block_row_dump:
tab 0, row 0, @0x1f5b
tl: 37 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30
tab 0, row 1, @0x1f36
tl: 37 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30
tab 0, row 2, @0x1f11
tl: 37 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [33]
 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
 61 31 30 30 30 30 30 30

Auffällig ist, dass die komprimierte Version zwischen einer tab 0 mit genau einer row und einer tab 1 mit 719 rows unterscheidet, während die nicht komprimierte Tabelle nur eine tab 0 mit 185 ros enthält. Vermutlich ist tab 0 die Symboltabelle mit den wiederholten Elementen. Der bindmap Eintrag, der für alle Elemente in tab 1 identisch ist, verweist dann vermutlich auf diese tab 0.

Man kann also aus dem Dump durchaus allerlei herauslesen, aber ich sehe noch nicht, welche Token in der Symboltabelle erscheinen können (obwohl viel dafür spricht, dass es sich um komplette Spaltenwerte handelt). Dazu vielleicht demnächst ein weiterer Versuch.

Table Compression und Sortierung

Vor einigen Tagen habe ich in einer Datenbank einen Effekt beobachtet, den ich nicht erwartet hatte: ich wollte dort die Komprimierung einer zweispaltigen Tabelle dadurch verbessern, dass ich die Daten nach der Spalte mit der geringeren Anzahl distinkter Werte sortieren ließ - aber tatsächlich verschlechterte sich die Komprmierung dadurch sogar marginal. Grund genug, das Verhalten noch mal genauer anzuschauen. Bei Ordix findet man eine hübsche Darstellung des Komprimierungsverfahrens:

Die Komprimierung erfolgt auf Blockebene. Mehrfacheinträge im Datenblock werden nur einmal gespeichert. Ein Pointer im Datenteil des Blocks referenziert auf eine Symboltabelle, in der die sich wiederholenden Werte gespeichert werden. Diese Symboltabelle befindet sich in einem dafür reservierten Bereich innerhalb des Blocks. Der restliche Platz steht den eigentlichen Daten zur Verfügung. [...]

Diese Methode, die bereits bei der BulkCompression-Methode von Oracle 9i verwendet wurde, wird technisch identisch ebenfalls für die neue Methode verwendet: compress for all operations

Ich bleibe erst mal bei der einfachen BulkCompression. Hier ein Beispiel, von dem ich annahm, dass es das oben geschilderte Verhalten reproduzieren könnte (Windows 7, Oracle 11.2, 8k blocksize): Ich lege drei Tabellen an, eine sortiert nach der ersten Spalte mit 1.000.000 Ausprägungen, eine sortiert nach der zweiten Spalte mit 1.000 Ausprägungen und eine sortiert nach Zufall (in der Gestalt von DBMS_RANDOM.VALUE):

create table compress_num_ord_col1 compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by mod(level, 1000);
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_col1')
 
create table compress_num_ord_rn compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by level;
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_rn')

create table compress_num_ord_random compress
as
select level rn
     , mod(level, 1000) col1
  from dual
connect by level <= 1000000
 order by dbms_random.value;
 
exec dbms_stats.gather_table_stats(user, 'compress_num_ord_random')

select table_name
     , pct_free
     , blocks
    , compression
    , compress_for
  from user_tables
 where table_name like 'COMPRESS_NUM%';

TABLE_NAME                       PCT_FREE     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
COMPRESS_NUM_ORD_COL1                   0       1528 ENABLED  BASIC
COMPRESS_NUM_ORD_RANDOM                 0       1760 ENABLED  BASIC
COMPRESS_NUM_ORD_RN                     0       1763 ENABLED  BASIC

Interessant ist zunächst, dass der PCT_FREE-Wert 0 ist, was laut Doku zu den Tricks der Compression gehört: "Tables with COMPRESS or COMPRESS BASIC use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the physical_attributes_clause." Noch interessanter ist, dass das Ergebnis meinen ursprünglichen Vorstellungen entspricht, aber nicht den Beobachtungen, die den Test anregten. Wahrscheinlich sollte ich den Test im fraglichen System (10.2.0.4) noch mal wiederholen.

Im Moment interessiert mich aber noch eine andere Frage: was sind "Mehrfacheinträge im Datenblock"? Handelt es sich dabei um komplette Spaltenwerte oder können auch Teilstrings durch Platzhalter ersetzt werden. Dazu folgendes (nicht eben elegantes) Beispiel: diesmal lege ich vier Tabellen an, in denen jeweils ein längerer String mit unterschiedlichen Werten konkateniert wird:

create table compress_str_1_nocomp
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || 1000000 col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1_nocomp')

create table compress_str_1000000 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + level) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1000000')

create table compress_str_1000 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + mod(level, 1000)) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1000')

create table compress_str_100 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || (1000000 + mod(level, 100)) col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_100')


create table compress_str_1 compress
as
select 'aaaaaaaaaaaaaaaaaaaaaaaaaa' || 1000000 col1
  from dual
connect by level <= 1000000;

exec dbms_stats.gather_table_stats(user, 'compress_str_1')

select table_name
     , num_distinct
  from user_tab_cols
 where column_name = 'COL1'
   and table_name like 'COMPRESS_STR%';
   
TABLE_NAME                     NUM_DISTINCT
------------------------------ ------------
COMPRESS_STR_1                            1
COMPRESS_STR_100                        100
COMPRESS_STR_1000                      1000
COMPRESS_STR_1000000                1000000

select table_name
     , pct_free
     , blocks
    , compression
    , compress_for
  from user_tables
 where table_name like 'COMPRESS_STR%';
 
TABLE_NAME                       PCT_FREE     BLOCKS COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------
COMPRESS_STR_1_NOCOMP                  10       5500 DISABLED
COMPRESS_STR_1000000                    0       4941 ENABLED  BASIC
COMPRESS_STR_1000                       0       4941 ENABLED  BASIC
COMPRESS_STR_100                        0       2655 ENABLED  BASIC
COMPRESS_STR_1                          0       1408 ENABLED  BASIC

Der Test ist nicht gerade präzise, aber ich interpretiere die Ergebnisse so, dass tatsächlich nur komplette Strings ersetzt werden: jeder Block enthält für COMPRESS_STR_1_NOCOMP 185 Sätze. Da für COMPRESS_STR_100 jeder hundertste Satz identisch ist, kann hier eine Komprimierung erfolgen, während die Fälle, in denen die Duplikate weiter auseinander liegen (COMPRESS_STR_1000, COMPRESS_STR_1000000) keine Komprimierung erfahren. Möglicherweise könnte ein Blockdump das Verfahren noch etwas deutlicher beleuchten - und möglicherweise probiere ich das gelegentlich noch aus.

Oracle Online Dokumentation

Charles Hooper erklärt in seinem Blog, wie man die Oracle Online Dokumenation sinnvoll nutzen kann. Besonders interessant sieht dabei die Google-Suche mit dem Suchbegriff "site:download.oracle.com" aus. Wie man vermeiden kann, bei Google-Suchoperationen immer zuerst bei den Ausführungen von Herrn Burleson zu landen, kann man übrigens auch in einem neueren Thread bei Charles Hooper nachlesen: http://hoopercharles.wordpress.com/2011/02/11/on-the-topic-of-copyright/#comment-2842 (eigentlich geht es da um eine ziemlich lächerliche DMCA-Angelegenheit).

Deferred Segment Creation

Deferred Segment Creation ist ein neues Feature in Version 11.2 und dient dazu, die einem Objekt zugeordneten Segmente erst dann zu erzeugen, wenn sie tatsächlich benötigt werden - also dann, wenn Daten eingefügt werden. Die Dokumentation erklärt dazu:

SEGMENT CREATION DEFERRED: This clause defers creation of the table segment — as well as segments for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table — until the first row of data is inserted into the table. At that time, the segments for the table, LOB columns and indexes, and explicitly created indexes are all materialized and inherit any storage properties specified in this CREATE TABLE statement or, in the case of explicitly created indexes, the CREATE INDEX statement. These segments are created regardless whether the initial insert operation is uncommitted or rolled back. This is the default value.

In den letzten Wochen haben sich Tom Kyte und Richard Foote mit dem Feature etwas intensiver beschäftigt:

• http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html: Tom Kyte erläutert neben der eigentlichen Funktionalität auch die Möglichkeiten der Deaktivierung (was vielleicht gar nicht so uninteressant ist)
• http://richardfoote.wordpress.com/2011/02/17/oracle11g-creation-on-demand-indexes-invisible-touch/: Richard Foote erklärt (wen wundert's?) vor allem das Verhalten von abhängigen Indizes.

Bei Richard Foote findet man außerdem auch ein plausible Erklärung für die Einführung der Deferred Segment Creation:

This means for those packaged applications where a large number of objects get created of which relatively few are actually ever used by the specific deployment of the application (eg. SAP) , a substantial amount of storage could potentially be saved. It also can save a significant amount of time deploying such applications as the overheads associated with actually creating the never to be used segments can be avoided.

Komisch, dass so häufig der Name SAP erscheint, wenn es um eine seltsame Nutzung von Datenbanken geht ...

Nicht direkt mit dem Thema verknüpft, aber auch interessant ist Richard Footes Beobachtung, dass in 11.2 ein weiteres Feature zur Vermeidung nutzloser Speicherverwendung eingeführt wurde, nämlich die Löschung der unbrauchbaren Segmente eines als UNUSABLE gekennzeichneten Index.

Parallelisierte Constraint-Aktivierung

Das Thema selbst finde ich nicht unbedingt so bedeutsam, aber Timur Akhmadeevs Untersuchung enthält so ziemlich alle Analyseschritte, die ich mir denken kann (vor allem, wenn man noch Tanel Poders kommentierenden Hinweis auf ORADEBUG dazunimmt - ein Hilfsmittel, das mir fast völlig fremd ist).

Das Ergebnis der Untersuchung ist übrigens, dass eine parallelisierte Constraint-Aktivierung nur über Outlines erreicht werden kann - andere Parallelisierungsanweisungen bleiben unwirksam, da die Aktivierung eine rekursive Operation ist.

Host Title

Wahrscheinlich wird nicht jeder meine Begeisterung für folgenden Fund nachvollziehen können: Rob van Wijk, der ein wirklich großartiges Blog betreibt, zeigt hier, dass man in sqlplus das Kommando Host Title verwenden kann, um die aktuellen Verbindungsinformationen der Session in den Header des Shell-Fensters eintragen zu lassen. Die dort ebenfalls angesprochene Möglichkeit, diese Informationen ins SQL-Prompt zu setzen, habe ich wegen der daraus (nahezu) zwangsläufig folgenden Verschiebung des Prompts nicht dauerhaft verwendet - auskommentiert lag die zugehörige Query aber immer noch in meinem login-Template.

Tracefile

Jonathan Lewis erwähnt in seinem Blog, dass die View v$process in Version 11 eine Spalte TRACEFILE enthält, die Pfad und Name des zum Process gehörigen Trace-Files enthält. Damit kann ich ein ganz besonders häßliches SQL-Script ausrangieren, mit dem ich mir bisher die Angaben zusammengesucht hatte.

ODCIAggregate

Carsten Czarski zeigt in seinem Blog, wie man benutzerdefinierte Aggregatsfunktionen (auch als analytische Funktionen) erzeugen kann und liefert dazu das praktische Beispiel einer Aggregatsfunktion für die Bildung eines Produktes. Die Basis dafür sind die ODCIAggregate-Routinen, die anscheinend auch hinsichtlich ihrer Performance optimiert sind.

Pro Oracle SQL

Kaum jemand schreibt so fundierte Rezensionen zu Oracle-Fachbüchern wie Charles Hooper. Sehr lesenswert ist beispielsweise seine nicht unbedingt schmeichelhafte, aber in jedem Fall gut begründete Besprechung des Buchs Oracle Tuning: The Definitive Reference Second Edition, die mit der Überschrift "1,100 Page True-False Quiz, 'Definitive' is Left as an Exercise for the Student" einsetzt. Für mich sind Hoopers Einschätzungen völlig nachvollziehbar - wobei er in der Regel zu durchaus positiven Beurteilungen kommt. Das Buch Pro Oracle SQL habe ich kürzlich aufgrund einer positiven Hooperschen Besprechung erworben, und will hier jetzt ein paar Eindrücke festhalten. Erschienen ist der Band 2010 bei Apress - und das allein kann schon als erster Hinweis auf gehobene Qualität gesehen werden, denn mir ist kein wirklich schlechtes Oracle-Buch bekannt, das in diesem Verlag erschienen wäre. Auch die Liste der Autoren, die jeweils ein oder mehrere Kapitel beigetragen haben, ist vielversprechend: Karen Morton, Kerry Osborne, Robyn Sands, Riyaj Shamsudeen und Jared Still - fast alles Blog-Autoren, deren Artikel ich schon seit längerer Zeit verfolge. Alle Autoren machen ausgiebig Gebrauch von praktischen Beispielen, wodurch die Aussagen überprüfbar werden und besser nachvollzogen werden können. Im einzelnen werden folgende Themen behandelt:

• 1 Core SQL (K. Morton): knappe Einführung in sqlplus und kurze Vorstellung der grundlegenden Syntaxelemente von SQL.
• 2 SQL Execution (K. Morton): Erläuterungen zur Architektur des Oracle Servers, der Verarbeitung von Queries und einiger interner Transformationen (Subquery Unnesting, Predicate Pushing etc.); die Erklärungen sind zutreffend, aber ich bin mir nicht ganz sicher, ob die unterschiedlichen Themen so recht zusammen in ein Kapitel passen.
• 3 Access and Join Methods (K. Morton): Darstellung von Zugriffs- und Join-Möglichkeiten. Ein klar strukturiertes Kapitel mit fundierten Erläuterungen.
• 4 SQL is About Sets (K. Morton): ein kurzes Plädoyer für satzorientiertes Denken (im Gegensatz zu prozeduralem) bei der Lösung von SQL-Fragestellungen. Ich denke, das gehört zu den Dingen, die auch Tom Kyte mit schöner Regelmäßigkeit erwähnt.
• 5 It's About the Question (K. Morton): noch ein Plädoyer, diesmal dafür, Fragen zu analysieren statt SQL-Queries. Wieder ein Gedanke, den auch Tom Kyte häufig formuliert. Mir passiert es leider immer noch gelegentlich, dass ich Queries erst optimiere, ehe ich erkenne, dass sie inhaltlich nutzlos sind.
• 6 SQL Execution Plans (K. Morton): eine solide Einführung, aber das entsprechende Kapitel in Christian Antoginis Buch Troubleshooting Oracle Performance ist besser, was sicher auch an den unterschiedlichen Zielsetzungen der Bücher liegt.
• 7 Advanced Grouping (J. Still): knappe Darstellung einfacher Gruppierungen und der neueren Erweiterungen des Sprachumfangs über CUBE, ROLLUP und die ergänzenden Hilfsfunktionen. Nicht schlecht, aber doch ziemlich knapp. Mir haben die entsprechen Beiträge Rob van Wijks noch etwas besser gefallen.
• 8 Analytic Functions (R. Shamsudeen): noch eine solide, aber auch ziemlich knappe Einführung - bei AskTom findet man Beispiele für so ziemlich jede denkbare Verwendung von Analytics. Am Ende des Kapitels findet sich die Aussage, dass ein serieller Prozess nicht mehr als 5% und ein parallelisierter Zugriff nicht mehr als 30% der PGA-Ressourcen nutzen kann (S. 250) - und das stimmt seit 10.2 nicht mehr, wie Joze Senegacnik gezeigt hat. Das ist allerdings so ungefähr der einzige sachliche Fehler, den ich im Buch gefunden habe.
• 9 The Model Clause (R. Shamsudeen): kurze Einführung der Model Clause, mit der ich immer noch meine Schwierigkeiten habe. Für mich ist auch in diesem Fall Rob van Wijks Vorgehen nachvollziehbarer, der die Model Clause an sehr einfachen Beispieldaten erläutert. Aber selbst die einfachen Beispiele haben mich bisher noch nicht dazu gebracht, die Model Clause selbst einmal praktisch einzusetzen. Vielleicht mangelt's mir aber auch nur an Begeistung und Verständnis für komplexere Algorithmen ... - dass man mit der Model Clause und solider mathematischer Ausbildung eindrucksvolle Dinge machen kann, hat Alberto Dell'Era gelegentlich gezeigt.
• 10 Subquery Factoring (J. Still): erläutert die Nutzung dieses Features, das außerhalb der Oracle-Welt oft als CTE (Common Table Expression) bezeichnet wird - und das syntaktisch als With-clause erscheint. Beschrieben werden die Hints MATERIALIZE und INLINE, über die beeinflusst werden kann, ob eine CTE über TEMP TABLE TRANSFORMATION als temporäres Objekt angelegt wird oder einfach in die rahmende Query integriert wird - und die Wirkungen dieser Varianten auf die Query-Performance. Außerdem wird erläutert, wie man durch rekursive CTEs hierarchische Abfragen erzeugen kann - was als Alternative zum CONNECT BY dienen kann.
• 11 Semi-joins and Anti-joins (K. Osborne): ein Kapitel, das mir sehr gut gefallen hat. Erläutert werden zunächst die Unterschiede zwischen (NOT) IN und (NOT) EXISTS. Noch interessanter fand ich die Erklärungen zu den Anti-joins, bei denen für mich neu war, dass ausgerechnet die von mir geschätzte MINUS-Operation nicht von anti-join optimizations profitiert.
• 12 Indexes (R. Shamsudeen): ein interessantes Kapitel, das nicht allzu sehr in die Tiefe geht, aber sehr viele relevante Punkte anspricht. Zu den Bitmap Indizes wird einmal mehr behauptet, dass sie nur für Spalten mit niedriger Cardinality geeignet seien, was Richard Foote gelegentlich widerlegt hat.
• 13 Beyond the Select (K. Osborne): knappe aber erhellende Darstellung zu INSERT, UPDATE, DELETE, MERGE, TRUNCATE etc.
• 14 Transaction Processing (R. Sands): Einführung in die Transaktionssteuerung relationaler Datenbanken (ACID, Isolation Levels) und Oracles spezifische Implementierung. Frau Sands verweist gleich zu Beginn auf Tom Kytes Äußerungen zum Thema, die da doch deutlich detaillierter sind.
• 15 Testing and Quality Assurance (R. Sands): ein relativ theoretisches Kapitel zur Bedeutung von Tests. Nicht schlecht, aber ziemlich allgemein.
• 16 Plan Stability and Control (K. Osborne): für mich eins der interessantesten Themen des Buchs. Ein wenig bedauere ich, dass Kerry Osborne die verwendeten Code-Stücke nicht im gedruckten Text untergebracht hat, kann mir aber vorstellen, dass das relativ viel Platz erfordert hätte. In jedem Fall eine sehr klare Einführung zu den Themen Outlines, Profiles und Baselines. Ich erinnere mich dunkel daran, dass Osborne und Jonathan Lewis vor einiger Zeit über die Rolle der Profiles diskutiert haben, wobei Lewis die Ansicht vertrat, dass sie grundsätzliche etwas Anderes seien als Outlines und Baselines und dem cbo vor allem bessere Statistikinformationen lieferten - was zumindest plausibel klingt.

Alle Kapitel sind grundsolide, viele gehen aber nicht allzu sehr in die Tiefe. Ich nehme an, dass das dem Konzept des Buchs entspricht, aber vielleicht wäre in diesem Zusammenhang eine kurze Erklärung der Zielsetzung und des angesprochenen Leserkreises nützlich gewesen - wie man sie in vielen anderen Apress Büchern findet. Laut Apress Roadmap (auf der Rückseite des Buchs) steht der Band zwischen dem einführenden Begining Oracle SQL und Christian Antogninis Buch und das passt zu meiner Wahrnehmung.

SQL Model Clause

Die SQL Model Clause ist wahrscheinlich das Element im Oracle-SQL, mit dem ich am wenigsten anfangen kann. Gelegentlich habe ich in diesem Blog schon mal auf ein paar Artikel verwiesen, in denen Rob van Wijk das Thema genauer beleuchtet - aber meine eigenen Erkenntnisse dazu sind immer noch sehr bescheiden. Daher hier mal ein kleines Beispiel, in dem ich etwas mit Model deutlich kompakter ausdrücken kann, als das mit anderen SQL-Varianten möglich wäre:

with
data
as
(
select empno
     , deptno
     , sal sal_org
     , sal
  from emp
)
select empno
     , deptno
     , sal_org
     , sal
  from data
 model
       partition by (deptno)
       dimension by (empno)
       measures (sal, sal_org)
       rules iterate(10)
       (sal[any] = sal[cv(empno)] - sal[cv(empno)]/10)

EMPNO     DEPTNO    SAL_ORG        SAL
----- ---------- ---------- ----------
 7499         30       1600 557,885504
 7521         30       1250  435,84805
 7654         30       1250  435,84805
 7698         30       2850 993,733554
 7844         30       1500  523,01766
 7900         30        950 331,244518
 7369         20        800 278,942752
 7566         20       2975 1037,31836
 7788         20       3000 1046,03532
 7876         20       1100 383,546284
 7902         20       3000 1046,03532
 7782         10       2450 854,262178
 7839         10       5000  1743,3922
 7934         10       1300 453,281972

Hier ziehe ich den emp-Mitarbeitern in 10 Iterationen jeweils 10% ihres Gehalts ab. Interessant am Beispiel sind in erster Linie die CV-Funktion (= CurrentValue) und das ITERATE-Kommando zur Definition der Schleifenanzahl. In diesem Fall ist das inhaltlich nicht besonders spannend, aber mehr fällt mir zu Model noch nicht ein. Interessanter wäre wahrscheinlich der Fall sukzessiver Steigerungen z.B. zu Ermittlung von Zinseszins-Effekten.

Statistik

Lies, damned lies and statistics... Ein paar instruktive Basisinformationen zu Datenverteilungseffekten findet man in Craig Shallahamers Blog.

Sort V1 und V2

Nachdem ich zuletzt mehrfach über allerlei Effekte im Zusammenhang von Sortierungen geschrieben habe, hier mal ein paar Überlegungen zum (inzwischen nicht mehr ganz so) neuen Sortierverfahren V2. Zum alten Mechanismus insertion sort (V1) hat Jonathan Lewis in seinem cbo-Buch und in seinem Blog allerlei geschrieben. Grundsätzlich wird bei diesem Verfahren intern ein B*Baum gefüllt:

The problem with the V1 sort is that the “sorting” mechanism works by building a balanced binary index as it reads the data. Although the implementation is made a little more complicated by the complexities of dynamic memory allocation the basic mechanism simply stacks your unsorted data at one end of the workarea memory while dynamically maintaining an index into that data at the other end of the workarea.

Über den V2-Mechanismus schreibt Jonathan Lewis auch einiges, aber das dahinter stehende Verfahren ist für mich weniger klar erkennbar. Auch Joze Senegacniks Präsentation zum PGA-Management enthält ein paar Hinweise, aber keine umfassende Erklärung des neuen Verfahrens - aber auch Jonathan Lewis Erklärungen zum V1-Verfahren basierten auf Tests und möglicherweise werden die internen Details von V2 auch durch Tests nicht ganz klar.

Mir genügt an dieser Stelle eine phänomenologische Betrachtung der Verfahren und ihrer Leistungsfähigkeit. Dazu folgender Test:

create table test_sort
as
select rownum rn
     , mod(rownum , 10) col1
     , lpad('*', 50, '*') col2
  from dual
connect by level < 1000000;

exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test_sort')

create index test_sort_v2_idx on test_sort(col2, col1, rn);

Index wurde erstellt.

-- Abgelaufen: 00:00:08.50

drop index test_sort_v2_idx;

alter session set "_newsort_enabled"=false;

create index test_sort_v1_idx on test_sort(col2, col1, rn);

Index wurde erstellt.

-- Abgelaufen: 00:00:16.50

Mit V2 lief die Indexerzeugung demnach in der halben Zeit. Aber etwas mehr kann man dazu dann vermutlich doch noch sagen. Aus v$sesstat kann ich in diesem Fall nicht besonders viel ableiten:

NAME                                              V2        V1     Diff
-----------------------------------------------------------------------
file io wait time                             187373      9513  -177860
session logical reads                          25270     22605    -2665
sorts (memory)                                    57         5      -52
sorts (rows)                                 1000050   1000002      -48
sorts (disk)                                       0         1        1
workarea executions - onepass                      0         2        2
DB time                                          850      1651      801
physical reads direct temporary tablespace         0      9893     9893
physical writes direct temporary tablespace        0      9893     9893
physical read total bytes                     221184  81043456 80822272
physical write total bytes                  80805888 161849344 81043456

Die Event-Auswahl ist eher willkürlich, deutlich wird nur, dass die V1-Operation (bei sonst gleichen Session-Settings und in einer sonst ungenutzten Instanz) eine Sortierung auf die Platte verschieben muss. Deshalb noch ein 10032-Trace zum Thema:

alter session set events '10032 trace name context forever, level 1'; 

-- V2
---- Sort Statistics ------------------------------
Input records                             999999
Output records                            999999
Total number of comparisons performed     9841519
  Comparisons performed by in-memory sort 9841519
Total amount of memory used               91718656
Uses version 2 sort
---- End of Sort Statistics -----------------------

-- V1
---- Sort Statistics ------------------------------
Initial runs                              4
Number of merges                          1
Input records                             999999
Output records                            999999
Disk blocks 1st pass                      9893
Total disk blocks used                    9895
Total number of comparisons performed     19891137
  Comparisons performed by in-memory sort 18667731
  Comparisons performed during merge      1223406
Temp segments allocated                   1
Extents allocated                         78
Uses version 1 sort
Uses asynchronous IO
    ---- Run Directory Statistics ----
Run directory block reads (buffer cache)  5
Block pins (for run directory)            1
Block repins (for run directory)          4
    ---- Direct Write Statistics -----
Write slot size                           253952
Write slots used during in-memory sort    4
Number of direct writes                   321
Num blocks written (with direct write)    9893
Block pins (for sort records)             9893
Cached block repins (for sort records)    3
Waits for async writes                    301
    ---- Direct Read Statistics ------
Size of read slots for output             57344
Number of read slots for output           21
Number of direct sync reads               266
Number of blocks read synchronously       290
Number of direct async reads              1427
Number of blocks read asynchronously      9603
Waits for async reads                     314
---- End of Sort Statistics -----------------------

Die Statistiken für die V2-Sortierung vergleichsweise übersichtlich - möglicherweise wäre da ein höheres Trace-Level aussagekräftiger. Deutlich wird eigentlich nur, dass V2 deutlich weniger Vergleiche durchführt (10M statt 20M) und dass für V2 in diesem Fall keine onepass-Operation erforderlich ist. Aber in jedem Fall ist V2 offenbar sehr viel effektiver als V1 - und das genügt mir für den Moment...

Parallel Hints

Angeregt durch das Vorbild der Herren Lewis und Nanda beabsichtigt Randolf Geist, eine neue Serie mit kurzen Artikeln zu Grundlagenwissen zu schreiben. Das scheint mir aus mehreren Gründen eine gute Idee zu sein.

Im ersten Teil der Serie wird das unterschiedliche Verhalten von Parallel DML und Parallel Query hinsichtlich der Wirkung von ALTER SESSION-Kommandos und HINTs erläutert:

• If you haven't enabled Parallel DML on session level (ALTER SESSION ENABLE/FORCE PARALLEL DML) you are not able to force parallel DML execution on statement level using explicit PARALLEL hints for the DML operation. (This one is probably known)
• If you disable Parallel Query on session level (ALTER SESSION DISABLE PARALLEL QUERY) explicit PARALLEL hints on statement level for a query operation will overrule this and therefore still use Parallel Query. The session setting only overrules the object level parallel settings, not the PARALLEL hint (This is probably less known)

Interested Transaction Lists

Arup Nanda erläutert in seinem Blog die Rolle der Interested Transaction Lists (ITL), in denen in den Datenblöcken die Transaktionen verzeichnet sind, die Locks auf Rows dieser Blöcke halten. Interessant ist auch der Nachtrag zum gleichen Thema.

Automatisches PGA Management

In einem Thread im Oracle-Forum von XING hat Randolf Geist vor kurzem auf eine Präsentation von Joze Senegacnik hingewiesen, in der erläutert wird, wieviel Speicher in 10.2 bei automatischem Workarea-Management für einzelne Workarea-Operationen verwendet werden kann. Wichtig ist dabei vor allem, dass die in älteren Releases gültige Regel, dass eine (nicht parallelisierte) Operation nicht mehr als 5% der Gesamtgröße der PGA erreichen durfte, für 10.2 nicht mehr gilt.

Für 9i und 10.1 galt als Grenzwert für die Speicherzuweisung für eine einzelne SQL-Operation:

• serielle Operationen: min(5% PGA_AGGREGATE_TARGET, 100MB) (_smm_max_size hidden parameter)
• parallele Operationen: 30% PGA_AGGREGATE_TARGET / DOP (_smm_px_max_size)

Für10.2 hat Joze Senegacnik folgende Werte ermittelt: "In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) is limited to:

• for P_A_T <= 500MB the parameter _smm_max_size = 20% of P_A_T
• for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
• for P_A_T betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of P_A_T
• for P_A_T > 2560MB (2,5GB) the parameter _smm_max_size = 262,060 MB (~0,25GB)
• I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4GB.
• The maximum value for parallel operations changed from 30% to 50%"