Deterministische Funktionen

Dominic Brooks hat eine interessante Blog-Artikelserie zum Thema Deterministic Functions geschrieben.

Segment-Größe

In aller Regel lasse ich mir die Größe von Segmenten in MB anzeigen, da mich detailliertere Angaben nicht so sehr interessieren. Ein anderer Wert, den ich betrachte, sind häufig die Blocks. Bei näherem Hinsehen fällt mir aber gerade auf, dass ich mir nicht völlig sicher bin, was die Angaben verschiedener Quellen bedeuten. Dazu ein Test:

-- Anlage der Testtabelle
create table bi_test4
as
select rownum rn
     , mod(rownum, 2) col1
     , mod(rownum, 100) col2
     , mod(rownum, 100000) col3
     , lpad('*', 100, '*') col4
  from dual
connect by level <= 1000000;

-- Anlage von Statistiken
exec DBMS_STATS.GATHER_TABLE_STATS(user, 'bi_test4', cascade => true, estimate_percent => 100)

Die Größenangaben nehme ich üblicherweise aus USER_SEGMENTS. Laut Dokumentation bedeutet die Block-Angabe "Size, in Oracle blocks, of the segment":

select BYTES
     , BLOCKS
     , EXTENTS
  from user_segments
 where segment_name = 'BI_TEST4'

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
 142606336       8704         87

Wenn ich mir aber die Angaben aus USER_TABLES ansehe, finde ich diese 8704 Blocks nicht wieder:

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST4';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8413            0          0          0         116

Laut Doku sind die BLOCKS hier die "Number of used data blocks in the table". Zu den EMPTY_BLOCKS heisst es: "Number of empty (never used) data blocks in the table. This column is populated only if you collect statistics on the table using the ANALYZE statement." Na gut, dann fragen wir eben ANALYZE, obwohl ich davon eigentlich komplett abgekommen war, da ANALYZE bei der Statistikerhebung nicht mehr das Mittel der Wahl ist. Aber auch dazu sagt die Dokumentation etwas:

Oracle recommends using the more versatile DBMS_STATS package for gathering optimizer statistics, but you must use the ANALYZE statement to collect statistics unrelated to the optimizer, such as empty blocks, average space, and so forth.

ANALYZE liefert im gegebenen Fall Folgendes (und benötigt dazu mehr Zeit als DBMS_STATS.GATHER_TABLE_STATS):

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST4';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8413          291       1662          0         119

In der Summe komme ich damit wieder auf einen bekannten Wert: 8413 + 291 = 8704. Also sind die Blocks aus USER_TABLES diejenigen unterhalb der HighWaterMark? Klingt plausibel. Aber es gibt noch eine Meinung zum Thema, nämlich die des DBMS_SPACE-Packages, das Tom Kyte in seiner show_space-Procedure verwendet:

SQL> exec show_space('BI_TEST4')
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           8,326
Total Blocks............................           8,704
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             291
Unused Bytes............................       4,767,744
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         218,368
Last Used Block.........................             221

Die 8704 werden dadurch ein weiteres Mal bestätigt, so dass ich davon ausgehe, dass es sich dabei um die tatsächliche Gesamtzahl der Blocks handelt. Ebenfalls bestätigt werden die Blocks oberhalb der HWM: ANALYZE und show_space kommen beide auf 291. Was ich aber noch nicht verstehe, ist die Zählung der gefüllten Blocks in show_space. Da würde ich annehmen, dass die Summe aus Unformatted Blocks + FS1 Blocks + ... + FS4 Blocks + Full Blocks wieder die 8413 aus USER_TABLES wäre - aber hier sind es 8326. Es fehlen also 87 Blocks. Mal sehen, ob ich die noch irgendwo finde...

Nachtrag: bei genauerer Durchsicht des bereits verlinkten AskTom-Threads findet sich ein Hinweis auf die Differenz - anscheind ergeben sich Abweichungen durch direct path Operationen. Deshalb ein weiterer Test:

create table bi_test5
as
select *
  from bi_test4
 where 1 = 0;

-- INSERT ohne APPEND-Hint statt CTAS, um direct path zu vermeiden
insert into bi_test5
select *
  from bi_test4;

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'bi_test5', estimate_percent => 100)
analyze table BI_TEST5 compute statistics;

select NUM_ROWS
     , BLOCKS
     , EMPTY_BLOCKS
     , AVG_SPACE
     , CHAIN_CNT
     , AVG_ROW_LEN
  from user_tables
 where table_name = 'BI_TEST5';

NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
-------- ---------- ------------ ---------- ---------- -----------
 1000000       8617           87       1775          0         119

SQL> exec show_space('BI_TEST5')
Unformatted Blocks .....................             240
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              51
Full Blocks        .....................           8,324
Total Blocks............................           8,704
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         227,072
Last Used Block.........................             512

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select 8324 + 51 + 1 + 1 + 240 from dual;

8324+51+1+1+240
---------------
           8617

Das wären dann wieder die Blocks aus user_tables - dafür passen die "Unused Blocks" aber nicht mehr zu den EMPTY_BLOCKS, die ANALYZE ermittelt hat. Und wieder bleibt eine Differenz von 87, die aber vermutlich gar nichts mit der anderen 87 zu tun hat.

Global Hints

Dion Cho zeigt in seinem Blog, wie man Global Hints neben einfachen Hints in verschachtelten Queries verwenden kann. In der Regel ist anscheinend die Verwendung von benamten Query-Blocks über den QB_NAME-Hint die geeignetste Lösung.

Reihenfolge von Join-Bedingungen

Jonathan Lewis beschreibt in seinem Blog einen Fall, in dem die Reihenfolge der Bedingungen in einem multi-column-Join massive Auswirkung auf den gewählten Zugriff hat.

Join-Order für HASH JOIN

Bei Jonathan Lewis findet man die Quiz-Frage nach der Anzahl der möglichen Ausführungspläne für einen HASH-Join mit vier Tabellen, bei dem die Reihenfolge der JOIN-Operationen über den LEADING-Hint vorgegeben ist. Die korrekte Antwort "acht" ergibt sich daraus, dass der cbo für jeden einzelnen JOIN die Reihenfolge der beiden Tabellen über eine Operation "swap join inputs" umkehren kann.