Index Join

Mag sein, dass es so aussieht, als wollte ich alle Beiträge von Jonathan Lewis verlinken, aber hier behandelt er schon wieder ein sehr interessantes Thema: die Möglichkeit, Indizes zu joinen, die über Index Fast Full Scans eingelesen wurden. Spannend wird es, wenn er diese Technik verwendet, um rowids zu ermitteln, auf die man dann in einer rahmenden Query zugreift.

Histogramme - 2

Das Schöne an der praktischen Überprüfung von Annahmen ist, dass man schnell erkennen kann, wenn man sich gründlich täuscht. Ich hatte angenommen, dass ein frequency histogram einen bucket für jeden distinkten Wert enthalten würde - aber für meinen Testfall ergaben sich ganz andere Ergebnisse:

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 100')

select column_name
     , ENDPOINT_VALUE
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN'
 order by column_name;

COLUMN_NAME                    ENDPOINT_VALUE
------------------------------ --------------
RN                                         36
RN                                        100

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN'

COLUMN_NAME                              NUM_BUCKETS NUM_DISTINCT HISTOGRAM
---------------------------------------- ----------- ------------ ---------
RN                                                 2          100 FREQUENCY

Also nur zwei Buckets trotz 100 distinkten Werten. Noch lustiger wird der Fall, wenn ich die Statistikerzeugung noch einmal durchführe:

exec DBMS_STATS.GATHER_TABLE_STATS(user, 'TEST', METHOD_OPT => 'FOR ALL COLUMNS SIZE 100')

select column_name
     , ENDPOINT_VALUE
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN'
 order by column_name;

COLUMN_NAME                              ENDPOINT_VALUE
---------------------------------------- --------------
RN                                                  100

select column_name
     , NUM_BUCKETS
     , NUM_DISTINCT
     , HISTOGRAM
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                              NUM_BUCKETS NUM_DISTINCT HISTOGRAM
---------------------------------------- ----------- ------------ ---------
RN                                                 1          100 FREQUENCY

Diesmal wurde also nur ein bucket erzeugt - das Verhalten ist also nicht deterministisch! Um den Fall noch obskurer zu machen, lieferte mir Autotrace anschließend nur noch absurde Ergebnisse, die darauf hindeuteten, dass die Entscheidung für Index-Zugriff oder FTS nun völlig willkürlich erfolgte. An dieser Stelle konnte dann SQL_TRACE zumindest belegen, dass AUTOTRACE Unfug erzählte (was ja gelegentlich vorkommt). Außerdem sahen die Zugriffspläne in SQL_TRACE jetzt so aus, als wären funktionstüchtige Histogramme im Spiel:

select /* test */ count(pad) 
from
 test where rn = 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.06          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 174  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS BY INDEX ROWID TEST (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)
      1    INDEX RANGE SCAN TEST_IDX (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 202123)

select /* test */ count(pad) 
from
 test where rn = 10


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 174  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
      1   TABLE ACCESS BY INDEX ROWID TEST (cr=3 pr=0 pw=0 time=0 us cost=2 size=104 card=1)
      1    INDEX RANGE SCAN TEST_IDX (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 202123)

select /* test */ count(pad) 
from
 test where rn = 100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.22       0.23         15       7474          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.22       0.23         15       7474          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 174  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=7474 pr=15 pw=0 time=0 us)
 999901   TABLE ACCESS FULL TEST (cr=7474 pr=15 pw=0 time=0 us cost=2285 size=103971816 card=999729)

card=1 für die Fälle 1 und 10 und card=999729 für den Wert 100 - also fast völlig zutreffende Annahmen. Aber warum sehen die Histogramme in user_tab_columns und user_histograms so ganz anders aus, als ich erwartet hatte? Die Erklärung lieferte dann mal wieder Tom Kyte:

This happens when you have some values that utterly dominate the other values - as you do - that one really high value can be used to infer the other buckets.

Das Vorliegen eines extrem häufigen Werts (hier die 100) bringt dbms_stats also zu einer völlig anderen Strategie: statt eines buckets pro Wert wird jetzt insgesamt nur ein bucket (oder zwei buckets) angelegt, aber offenbar genügt diese Information, um zu sinnvollen Zugriffen zu kommen. Wenn kein "predominant value" exisitiert, werden die Histogramme übrigens so erzeugt, wie ich es grundsätzlich erwartet hatte - aber das ist eine andere Geschichte, die ein andermal erzählt werden soll...

Histogramme - 1

Da ich fast so viel vergesse, wie ich lerne (oder auch etwas mehr), sollte ich möglichst viele Dinge aufschreiben. Ich vergesse sie dann zwar immer noch, kann sie später aber möglicherweise leichter wieder ausgraben. Ein Beitrag zu solcher zukünftiger Archäologie soll auch der folgende sein.

Der Optimizer geht davon aus, dass Daten gleichmäßig verteilt sind: wenn es also 1000 Sätze und 100 verschiedene Werte gibt, erwartet der cbo, dass jeder Wert 10 mal erscheint. Oft ist das eine recht sinnvolle Annahme, aber bei Daten mit einer extrem ungleichmäßigen Verteilung (skew) kann sie zu unglücklichen Entscheidungen führen. Dazu ein Beispiel (erzeugt mit 11.1.0.7):

-- Tabelle mit sehr ungleicher Datenverteilung: 
drop table test;
create table test
as
select case when rownum < 100 then rownum else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

-- Statistik-Erzeugung (ohne Histogramme)
exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'TEST', estimate_percent=>dbms_stats.auto_sample_size)

select column_name
     , ENDPOINT_VALUE
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN'
 order by column_name;

COLUMN_NAME                              ENDPOINT_VALUE
---------------------------------------- --------------
RN                                                    1
RN                                                  100

Die Werteverteilung sieht dabei folgendermaßen aus:

select RN
     , count(*)
  from test
 group by rn
 order by rn;

        RN   COUNT(*)
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
        10          1
...
       100     999901

Wenn man nun einen Index auf RN anlegt, bringt er für alle Werte außer RN=100 eine hohe Selektivität (da höchstens ein Satz geliefert wird):

create index test_idx on test(RN);
set autot trace

SQL> select count(pad) from test where rn = 1;

Abgelaufen: 00:00:00.01

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2866600405

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   104 |    85   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE              |          |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST     | 10000 |  1015K|    85   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | TEST_IDX | 10000 |       |    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("RN"=1)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der cbo wählt hier den Index-Zugriff und erwartet 10000 Rows (also die 1.000.000 Sätze / 100 unterschiedliche Werte). Der einzige Wert, für den diese Zugriffsentscheidung unglücklich wird, ist die 100:

SQL> select count(pad) from test where rn = 100;

Abgelaufen: 00:00:00.51

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2866600405

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   104 |    85   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE              |          |     1 |   104 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST     | 10000 |  1015K|    85   (0)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | TEST_IDX | 10000 |       |    10   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("RN"=100)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       8427  consistent gets
        961  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ full(test) */ count(pad) from test where rn = 100;

Abgelaufen: 00:00:00.15

Ausführungsplan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   104 |  2284   (1)| 00:01:34 |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST | 10000 |  1015K|  2284   (1)| 00:01:34 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("RN"=100)


Statistiken
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7474  consistent gets
          0  physical reads
          0  redo size
        346  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der Unterschied ist in diesem Fall nicht groß (7474 LIOs für den FTS gegenüber 8427 LIOs für den Index-Zugriff), aber immerhin bemerkbar (und ich habe keine Lust, jetzt noch ein extremeres Beispiel zu basteln). Seltsam sind übrigens die Rows-Schätzungen für Werte, die außerhalb der ENDPOINT_VALUE-Angaben der user_histograms liegen - und in der Tabelle nicht vorliegen:

RN      Rows
------------
0       9899
-1      9798
-10     8889
-97      101
-98        1
101     9899
102     9798
198      101
199        1

Man könnte die Arithmetik ermitteln, die dahinter steckt - aber das hat Jonathan Lewis in seinem cbo-Buch schon längst getan, und ich bin zu faul, das Rad hier noch mal zu erfinden.

Aber zurück zu den Histogrammen: davon gibt es zwei Sorten:

• frequency histograms: bei denen zu jedem distinkten Wert die Anzahl des Vorkommens angegeben wird (weshalb diese Histogramme nur verwendet werden können, wenn nicht mehr als 254 unterschiedliche Werte existieren, da das die maximale Anzahl von buckets ist)
• height-balanced histograms: die man sich als Säulendiagramm vorstellen kann (wie Christian Antognini sie in seinem großartigen Buch Troubleshooting Oracle Performance auch darstellt), wobei die Säule der aufeinandergestellten Anzahl-Angaben dann in gleichgroße Abschnitte zerlegt wird. Das wird mit einem Diagramm klarer, aber ich mag keins malen...

Für mein harmloses Beispiel ist die Unterscheidung ohnehin erst mal irrelevant. Aber hier wird mir der Nachmittag zu spät und der Eintrag zu lang, weshalb ich es erst einmal dabei belasse.

NULLIF()

Bei Jonathan Lewis habe ich gerade die NULLIF-Funktion entdeckt, mit der man “divide by zero”-Fehler vermeiden kann:

SQL> r
  1  with
  2  test_data as
  3  ( select rownum rn
  4         , rownum - 1 rn2
  5      from dual
  6   connect by level <= 5
  7  )
  8  select rn/rn2
  9*   from test_data
select rn/rn2
         *
FEHLER in Zeile 8:
ORA-01476: Divisor ist Null

SQL> r
  1  with
  2  test_data as
  3  ( select rownum rn
  4         , rownum - 1 rn2
  5      from dual
  6   connect by level <= 5
  7  )
  8  select rn/NULLIF(rn2, 0)
  9*   from test_data

RN/NULLIF(RN2,0)
----------------

               2
             1,5
      1,33333333
            1,25

NULLIF liefert NULL, wenn der erste Parameter dem zweiten Parameter (hier also 0) entspricht, sonst aber den ersten Parameter.

SSAS Memory Nutzung

Ich habe schon ein paar halbherzige Versuche unternommen, den Geheimnissen der Speichernutzung der Analysis Services auf den Grund zu gehen, war dabei aber nicht besonders erfolgreich. Deshalb freue ich mich sehr über Greg Galloways Erläuterungen zum Standby-Cache. Leider läuft das sysinternals-Tool RAMMap, auf das er verweist, nur auf Windows Server 2008 (and higher).

Adaptive Cursor Sharing

Randolf Geist liefert in seinem Blog eine umfassende Untersuchung zum Adaptive Cursor Sharing und insbesondere zur Frage, wie es sich im Zusammenhang mit PL/SQL-Optimierungen verhält.

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.