Was passt in einen Block hinein?

Seit Jahren arbeite ich mit dem Basisverständnis, dass ein Oracle-Block neben den Tabellendaten allerlei Overhead enthält, darunter transaction header, table directory, row directory etc. - und Entsprechendes kann man z.B. auch hier lesen (wobei dort allerdings keine Angaben zur Version erscheinen, aber ich nehme an, dass sich an diesen Dingen seit Aeonen nichts mehr geändert hat). Im Rahmen meiner Tests zum Befehl Alter Table Shrink ist mir dann aber aufgefallen, dass meine Blocks deutlich weniger Sätze aufnehmen, als ich erwartet hatte. Dazu folgender Test:

-- Blockgröße 16 K
SQL> sho parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_block_size                        integer     16384

-- Anlage einer Testtabelle
SQL> create table t1
  2  as
  3  select rownum col1
  4    from dual
  5  connect by level < 10000;

Tabelle wurde erstellt.

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col1) colsize
  6            from t1)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       224       1327         3869
       225       1327         3968
       226       1327         3968
       227       1327         3967
       228       1327         3968
       229       1327         3968
       230       1327         3968
       231        710         2123

8 Zeilen ausgewählt.

Die komplett gefüllten Blocks enthalten also jeweils 1327 Sätze, aber die Nettodaten der einzigen Tabellenspalte umfassen jeweils nur ca. 4K - also nur ein Viertel der Blocksize!

Eine Änderung der PCTFREE-Einstellung von 10 auf 0 verändert das Ergebnis im erwarteten Umfang von ~ 10%:

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col2) colsize
  6            from t2)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       236       1475         4312
       237       1475         4410
       238       1475         4410
       239       1475         4410
       240       1475         4411
       241       1475         4410
       242       1149         3436

7 Zeilen ausgewählt.

Man kann sich den Inhalt eines Blocks ansehen, indem man einen Blockdump erstellt:

SQL> alter system dump datafile 4 block 224;
System wurde geändert.

Der Dump wird im user_dump_dest abgelegt und enthält diverse Repräsentationen der Blockinhalte, darunter eine hexadezimale Darstellung, in der man die Datenverteilung relativ gut erkennen kann. Dort sieht man im gegebenen Fall auch relativ große Lücken, die ich mir aktuell noch nicht erklären kann.

Deshalb noch ein dritter Versuch mit String-Werten fester Breite:

SQL> r
  1  create table t3
  2  as
  3  select 'AAAAAAAA' col3
  4    from dual
  5* connect by level < 10000

Tabelle wurde erstellt.

SQL> r
  1  select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col3) colsize
  6            from t3)
  7   group by blockid
  8*  order by blockid

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       248       1043         8344
       249       1043         8344
       250       1043         8344
       251       1043         8344
       252       1043         8344
       253       1043         8344
       254       1043         8344
       255       1043         8344
       256       1043         8344
       257        612         4896

10 Zeilen ausgewählt.

Hier entspricht die vsize sehr genau den Erwartungen, aber die Blocks sind offenbar trotzdem nur halb gefüllt. Im Blockdump sieht man nach einem Blockheader von ca. 2000 Zeichen folgende Muster:

...
011CD8720 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8730 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD8740 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
011CD8750 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8760 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD8770 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
011CD8780 41414141 0801002C 41414141 41414141  [AAAA,...AAAAAAAA]
011CD8790 0801002C 41414141 41414141 0801002C  [,...AAAAAAAA,...]
011CD87A0 41414141 41414141 0801002C 41414141  [AAAAAAAA,...AAAA]
...

Offenbar ist die Speicherung für die Sätze weniger kompakt und die (row) directory Informationen sind umfangreicher, als ich das angenommen hätte. (Die verlinkte Seite erklärt "The row directory uses 2 bytes per stored row.", aber sicher zuordnen kann ich diese Struktur im Blockdump nicht)

Erwartungsgemäß lassen sich wenige große Sätze besser in den Block unterbringen:

SQL> create table t4
  2  as
  3  select rpad('*', 1000, '*') col4
  4    from dual
  5  connect by level <= 32;

Tabelle wurde erstellt.

SQL> select blockid
  2       , count(*)
  3       , sum(colsize)
  4    from (select dbms_rowid.rowid_block_number(rowid) blockid
  5               , vsize(col4) colsize
  6            from t4)
  7   group by blockid
  8   order by blockid;

   BLOCKID   COUNT(*) SUM(COLSIZE)
---------- ---------- ------------
       328         14        14000
       329         14        14000
       330          4         4000

Für kleine Rows ist der Overhead des row directory denmach recht signifikant, für größere spielt er eine weniger wichtige Rolle.