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.