Freitag, Januar 14, 2011

Histogramme - 4

Bei meinen Histogramm-Tests bin ich noch an einem Detail hängengeblieben, nämlich an den ENDPOINT_NUMBER-Angaben in USER_HISTOGRAMS (oder der anscheinend identischen USER_TAB_HISTOGRAMS). Während man den ENDPOINT_VALUE für frequency histograms relativ leicht als einen distinkten Wert der jeweiligen Spalte wiedererkennen kann, sind die ENDPOINT_NUMBER-Angaben interpretationsbedürftiger:

create table test
as
select case when rownum <= 10000 then mod(rownum, 10) else 100 end rn
     , lpad('*', 100, '*') pad
  from dual
connect by level <= 1000000;

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

  RN   COUNT(*)
---- ----------
   0       1000
   1       1000
   2       1000
   3       1000
   4       1000
   5       1000
   6       1000
   7       1000
   8       1000
   9       1000
 100     990000

Also 10 Werte mit jeweils 1.000 Vorkommen und einer, der 990.000 mal erscheint. Wenn man dazu USER_HISTOGRAMS befragt, sieht man Werte, die dazu in irgendeiner Relation zu stehen scheinen, aber die Art dieser Relation erschliesst sich nicht unmittelbar - zumindest nicht für mich:

select column_name
     , endpoint_value
     , endpoint_number
  from user_histograms
 where table_name = 'TEST'
   and column_name = 'RN';

COLUMN_NAME                              ENDPOINT_VALUE ENDPOINT_NUMBER
---------------------------------------- -------------- ---------------
RN                                                    0              65
RN                                                    1             126
RN                                                    2             171
RN                                                    3             235
RN                                                    4             292
RN                                                    5             348
RN                                                    6             400
RN                                                    7             462
RN                                                    8             532
RN                                                    9             586
RN                                                  100            5482 
 

Die Antwort auf die Frage nach der Bedeutung dieser Werte kennt einmal mehr Jonathan Lewis: die ENDPOINT_NUMBER ist eine kumulierte Anzahl der Sätze pro Wert. Dankenswerterweise liefert er auf gleich eine Query mit deren Hilfe man die kumulierten Zahlen wieder in simple Gruppierungszählungen zurückverwandeln kann (über Analytics):

select
   endpoint_value                          column_value,
   endpoint_number - nvl(prev_endpoint,0)  frequency
from       (
   select
           endpoint_number,
           lag(endpoint_number,1) over(
                   order by endpoint_number
           )                               prev_endpoint,
           endpoint_value
   from
           user_tab_histograms
   where
           table_name  = 'TEST'
   and     column_name = 'RN'
   )
order by
   endpoint_number;

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         65
           1         61
           2         45
           3         64
           4         57
           5         56
           6         52
           7         62
           8         70
           9         54
         100       4896 
 

Vergleicht man diese Ergebnisse mit den Werten der gruppierenden Query oben, dann ergibt sich, dass die Frequency jeweils ca. 0,5% der absoluten Werte beträgt. Das wiederum lässt vermuten, dass hier ein Sampling im Spiel ist, und um das zu prüfen, habe ich ein kleines Script test.sql geschrieben, das die Statistiken mit unterschiedlicher estimate_percent-Angabe erhebt und anschließend die Query von Jonathan Lewis und eine Abfrage der SAMPLE_SIZE aus USER_TABELS durchführt:

-- test.sql 
exec dbms_stats.gather_table_stats (ownname=>user, tabname=>'test', method_opt=>'FOR ALL COLUMNS SIZE 11', estimate_percent=> &sample_pct)

select
   endpoint_value                          column_value,
   endpoint_number - nvl(prev_endpoint,0)  frequency
from       (
   select
           endpoint_number,
           lag(endpoint_number,1) over(
                   order by endpoint_number
           )                               prev_endpoint,
           endpoint_value
   from
           user_tab_histograms
   where
           table_name  = 'TEST'
   and     column_name = 'RN'
   )
order by
   endpoint_number;

select table_name
     , sample_size
  from user_tab_columns
 where table_name = 'TEST'
   and column_name = 'RN';

Hier die Ergebnisse für verschiedene Sampling-Angaben:

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 100

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:03.56

COLUMN_VALUE  FREQUENCY
------------ ----------
           0      10000
           1      10000
           2      10000
           3      10000
           4      10000
           5      10000
           6      10000
           7      10000
           8      10000
           9      10000
         100     900000

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                               1000000

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 10

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.90

COLUMN_VALUE  FREQUENCY
------------ ----------
           0        964
           1        985
           2       1054
           3        984
           4        995
           5        956
           6        978
           7        986
           8       1032
           9       1003
         100      89937

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                 99874

SQL> @ test
Geben Sie einen Wert für sample_pct ein: 1

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.48

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         88
           1        112
           2         98
           3         91
           4         91
           5        104
           6         94
           7        120
           8        102
           9        106
         100       9044

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                 10050

SQL> @ test
Geben Sie einen Wert für sample_pct ein: dbms_stats.auto_sample_size

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.73

COLUMN_VALUE  FREQUENCY
------------ ----------
           0         58
           1         61
           2         43
           3         55
           4         48
           5         55
           6         38
           7         47
           8         66
           9         51
         100       4925

11 Zeilen ausgewählt.

Abgelaufen: 00:00:00.00

TABLE_NAME                     SAMPLE_SIZE
------------------------------ -----------
TEST                                  5447
 

Mit einem 100%-Sample kommt Jonathan Lewis' Analysequery also auf die gleichen Ergebnisse, die auch das GROUP BY Statement für das Auftreten der distinkten Werte liefert. Die anderen %-Samples liefern jeweils die erwarteten Prozentsätze. Mit der Angabe dbms_stats.auto_sample_size erhält man die Werte, die ohne Angabe von estimate_percent erscheinen - und das überrascht nicht, da diese Angabe laut Doku den internen Default darstellt.

Nachtrag 14.04.2012: die Sample Size von ca. 5.500 Sätzen ist der (nicht ganz unproblematische) Standard-Wert für die Histogrammerstellung mit auto_sample_size, wozu ich zuletzt hier ein paar Details aufgeführt habe.

Keine Kommentare:

Kommentar veröffentlichen