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...