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.