Details zur AUTO_SAMPLE_SIZE in 11g

Hong Su erläutert im Blog der CBO Entwickler das Vorgehen bei der Statistikerfassung mit AUTO_SAMPLE_SIZE in 11g. Insbesondere erwähnt er folgende Punkte:

• in 11g führt AUTO_SAMPLE_SIZE automatisch zu einem Full Table Scan, der die Anzahl, den Minimal- und den Maximalwert zu jedem Attribut ermittelt. Vor 11g wurden im gleichen Fall Queries mit Sampling durchgeführt, wobei das Sample sukzessive vergrößert wurde, sofern das Ergebnis bestimmten (auf internen Metriken basierenden) Anforderungen entsprach.
• im Rahmen des FTS wird ein Hash-basierter Algorithmus verwendet, um die NDV-Angabe zu bestimmen. Vor 11g wurde dazu ein entsprechendes COUNT(DISTINCT ...) in der sample-basierten Ermittlungs-Query verwendet. Das hash-basierte Verfahren liefert eine Genauigkeit von nahezu 100%.
• Auch weitere statistische Werte werden im Rahmen des FTS mit dem gleichen Verfahren und hoher Genauigkeit bestimmt (number of NULLs, AVG column length).
• Die Erfassung von Histogrammen und Index-Statistiken erfolgt weiterhin über Sampling, allerdings wird die Anzahl der Sample-Versuche gegenüber dem alten Verfahren deutlich reduziert. Für die Index-Statistiken werden dabei die NDV-Werte der entsprechenden Columns (im Fall einspaltiger Indizes) bzw. ColumnGroups (i.e. extended statistics; für mehrspaltige Indizes) übernommen.

Im Fall der Histogramme sind allerdings die Samples bekanntlich erschütternd klein (5500 Sätze), was im Zusammenspiel mit der Annahme, dass ein im Histogramm unbekannter Wert die halbe Auftrittswahrscheinlichkeit hat wie der am seltensten im Histogramm erscheinende Wert, zu unschönen Ergebnissen führen kann. Da dieser Satz doch mal wieder etwas sperrig geworden ist, hier noch das zugehörige Beispiel (das ich in ähnlicher Form sicher schon mal hier irgendwo eingebaut hatte):

-- 11.2.0.1
-- eine Tabelle mit 1M rows, davon genau 1 Satz mit col1 = 0
-- für alle anderen Sätze ist col1 = 1
create table t
as
select case when mod(rownum, 1000000) = 500000 then 0 else 1 end col1
  from dual
connect by level <= 1000000;

-- Statistikerfassung mit AUTO_SAMPLE_SIZE
begin
dbms_stats.gather_table_stats(
    user
  , 'T'
  , estimate_percent => dbms_stats.auto_sample_size
  , method_opt => 'for all columns size auto'
);
end;
/

select column_name
     , num_distinct
     , num_buckets
     , sample_size
  from dba_tab_cols
 where table_name = 'T';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ----------- -----------
COL1                                      2           1        5525

select column_name
     , endpoint_number
     , endpoint_value
  from dba_histograms
 where table_name = 'T';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
COL1                                      5525              1

explain plan for
select *
  from t
 where col1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   999K|  2929K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   999K|  2929K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

explain plan for
select *
  from t
 where col1 = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|  1464K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   500K|  1464K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=0)

explain plan for
select *
  from t
 where col1 = 42;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500K|  1464K|   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |   500K|  1464K|   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=42)

Hier ist der NUM_DISTINCT-Wert korrekt mit 2 angegeben, aber da der CBO im Histogramm nur einen Wert (nämlich 1) findet, nimmt er an, dass jeder unbekannte Wert (die seltene 0 genau wie die abwegige 42) halb so oft erscheint wie der seltenste (und in diesem Fall einzige) Wert - also: 999K/2 = 500K.

In diesem extremen Fall liefert erst estimate_percent => 100 mit Sicherheit korrekte Angaben (da jedes Sample Gefahr läuft, den seltenen Wert zu verpassen):

begin
dbms_stats.gather_table_stats(
    user
  , 'T'
  , estimate_percent => 100
  , method_opt => 'for all columns size auto'
);
end;
/

select column_name
     , num_distinct
     , num_buckets
     , sample_size
  from dba_tab_cols
 where table_name = 'T';

COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE
------------------------------ ------------ ----------- -----------
COL1                                      2           2     1000000

select column_name
     , endpoint_number
     , endpoint_value
  from dba_histograms
 where table_name = 'T';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
COL1                                         1              0
COL1                                   1000000              1

explain plan for
select *
  from t
 where col1 = 0;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |   436   (3)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     3 |   436   (3)| 00:00:06 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=0)

Dynamic Sampling wäre in diesem Fall natürlich auch eine Alternative.