Zunächst lege ich eine Tabelle mit korrelierten Spaltenwerten an:
create table test_extended_stats
as
select rownum id
, mod(rownum, 100) col1
, mod(rownum, 100) col2
from dual
connect by level <= 100000;
exec dbms_stats.gather_table_stats(user, 'test_extended_stats')
Die Werte für col1 und col2 sind vollständig korreliert, was der cbo natürlich nicht wissen kann. Deshalb kann die cardinality für die Einzelwerte akkurat geschätzt werden - aber nicht für die Kombination der Werte:
set autot trace
select count(*) from test_extended_stats where col1 = 1;
COUNT(*)
----------
1000
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 65 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_EXTENDED_STATS | 1000 | 3000 | 65 (2)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
select count(*) from test_extended_stats where col2 = 1;
COUNT(*)
----------
1000
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 66 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| TEST_EXTENDED_STATS | 1000 | 3000 | 66 (4)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL2"=1)
select count(*) from test_extended_stats where col1 = 1 and col2 = 1;
COUNT(*)
----------
1000
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 66 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| TEST_EXTENDED_STATS | 10 | 60 | 66 (4)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1 AND "COL2"=1)
Wie erwartet erkennt der cbo, dass jeder Wert für col1 und col2 jeweils 1000 mal erscheint: die Selektivität ist also 1000/100000 = 0,01 und die cardinality folglich 100000 * 0,01 = 1000. Für die Kombination der Spalten werden die Wahrscheinlichkeiten dann als unabhängig betrachtet und es ergibt sich für die cardinality: 100000 * 0,01 * 0,01 = 10. Und das ist dann natürlich falsch. Das ist ein idealtypischer Anwendungsfall für extended statistics, die solche Korrelationseffekte über die interne Anlage einer virtuellen Spalte abbilden:
select dbms_stats.create_extended_stats(null, 'TEST_EXTENDED_STATS', '(col1, col2)') from dual;
DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
--------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH
select count(*) from test_extended_stats where col1 = 1 and col2 = 1;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 66 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| TEST_EXTENDED_STATS | 10 | 60 | 66 (4)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1 AND "COL2"=1)
Das funktioniert also zunächst noch nicht, was erst einmal daran liegt, dass für die neue virtuelle Spalte noch keine Statistiken existieren:
select column_name
, sample_size
, num_distinct
, last_analyzed
, histogram
from dba_tab_cols
where table_name = 'TEST_EXTENDED_STATS'
order by column_name;
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
---------------------------------------- ----------- ------------ ---------- ---------
COL1 100000 100 21.03.2012 NONE
COL2 100000 100 21.03.2012 NONE
ID 100000 100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH NONE
Dann eben ein weiterer dbms_stats-Aufruf:
exec dbms_stats.gather_table_stats(user, 'test_extended_stats')
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
---------------------------------------- ----------- ------------ ---------- ---------
COL1 5498 100 21.03.2012 FREQUENCY
COL2 5498 100 21.03.2012 FREQUENCY
ID 100000 100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 100000 100 21.03.2012 NONE
select count(*) from test_extended_stats where col1 = 1 and col2 = 1;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 66 (4)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| TEST_EXTENDED_STATS | 1000 | 6000 | 66 (4)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1 AND "COL2"=1)
Und schon ist die cardinality korrekt. Ein paar Aspekte sind für mich bemerkenswert. Zunächst die sample_size, die gewissen Schwankungen zu unterliegen scheint: ursprünglich wurden für col1 und col2 alle 100.000 Sätze betrachtet, nach der Anlage der extended statistics aber nur noch ca. 5% davon. Da keine Anpassungen durchgeführt wurden, dürfte hier für estimate_percent der default-Wert vorliegen und laut Dokumentation ist das auto_sample_size:
select dbms_stats.get_param('ESTIMATE_PERCENT') from dual;
DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE
Abgesehen von Greg Rahns Plädoyer für die Verwendung der auto_sample_size, die eine schnelle und exakte Ermittlungen der Statistiken gewährleiste, habe ich nicht allzu viele Aussagen zum Verhalten dieser Option gefunden. Zumindest scheint sie auf Basis der gleichen Daten nicht unbedingt die gleiche sample-Größe hervorzurufen - und ich habe zuletzt ein paar undurchsichtige Fälle beobachtet, in denen ein gather_table_stats mit dem default-sample keine geeignete Statistikbasis lieferte. Aber diese Beobachtung bleibt erst mal anekdotisch, da ich sie bislang nicht in einem Test nachstellen kann. Eine andere Frage ist, warum im gegebenen Beispiel nur für col1 und col2 ein Histogramm erzeugt wurde, nicht aber für die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH. Grundlage für die Anlage von Histogrammen ist dabei die Verwendung der Spalten in WHERE-Bedingungen, die in sys.column_usage$ protokolliert wird (leider gibt es dazu offenbar keine externalisierte data dictionary View, wie man in Christian Antogninis Troubleshooting Oracle Performance, S. 145ff. nachlesen kann). Ein Blick in col_usage$ zeigt, dass dort tatsächlich nur col1 und col2 aufgeführt sind:
-- die Mischung der sys-Objekte mit dba_objects ist dabei meiner Faulheit geschuldet,
-- sollte am Ergebnis aber nichts ändern ...
select c.name column_name
, u.equality_preds
, u.timestamp
from sys.col_usage$ u
, sys.col$ c
where c.obj# = u.obj#
and c.intcol# = u.intcol#
and u.obj# in (select object_id
from dba_objects
where object_name = 'TEST_EXTENDED_STATS');
COLUMN_NAME EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1 1 21.03.2012
COL2 1 21.03.2012
Anscheinend läuft die Aktualisierung von col_usage$ mit einer gewissen Verzögerung, denn es dauerte in meinem Test ein paar Minuten, ehe dort Einträge erschienen. Ein dbms_stats-Aufruf führt aber offenbar zur unmittelbaren Aktualisierung der col_usage$-Angaben. Aber warum wird in col_usage$ keine Verwendung für die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH protokolliert? Dazu noch mal ein etwas größeres Test-Script:
REM test_col_usage.sql
-- Tabelle löschen
drop table test_extended_stats;
-- Tabelle neu anlegen
create table test_extended_stats
as
select rownum id
, mod(rownum, 100) col1
, mod(rownum, 100) col2
from dual
connect by level <= 100000;
-- extended statistics anlegen
select dbms_stats.create_extended_stats(null, 'TEST_EXTENDED_STATS', '(col1, col2)') from dual;
-- Statistiken nach Anlage der virtuellen Spalte erfassen
-- in Lauf 1 auskommentiert, in Lauf 2 aktiv
-- exec dbms_stats.gather_table_stats(user, 'test_extended_stats')
-- Query mit Einschränkung auf col1 und col2 ausführen
select count(*) from test_extended_stats where col1 = 1 and col2 = 1;
-- dba_tab_cols vor dem dbms_stats-Aufruf
select column_name
, sample_size
, num_distinct
, last_analyzed
, histogram
from dba_tab_cols
where table_name = 'TEST_EXTENDED_STATS'
order by column_name;
-- Statistiken erzeugen
exec dbms_stats.gather_table_stats(user, 'test_extended_stats')
-- column usage
select c.name column_name
, u.equality_preds
, u.timestamp
from sys.col_usage$ u
, sys.col$ c
where c.obj# = u.obj#
and c.intcol# = u.intcol#
and u.obj# in (select object_id from dba_objects where object_name = 'TEST_EXTENDED_STATS');
-- dba_tab_cols nach dem dbms_stats-Aufruf
select column_name
, sample_size
, num_distinct
, last_analyzed
, histogram
from dba_tab_cols
where table_name = 'TEST_EXTENDED_STATS'
order by column_name;
Lauf 1 bringt folgende Ausgabe:
SQL> @ C:\temp\test_col_usage.sql
Tabelle wurde gelöscht.
Tabelle wurde erstellt.
DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
----------------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH
COUNT(*)
----------
1000
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1 NONE
COL2 NONE
ID NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH NONE
PL/SQL-Prozedur erfolgreich abgeschlossen.
COLUMN_NAME EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1 1 21.03.2012
COL2 1 21.03.2012
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1 5620 100 21.03.2012 FREQUENCY
COL2 5620 100 21.03.2012 FREQUENCY
ID 100000 100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 100000 100 21.03.2012 NONE
Für Lauf 2 hingegen mit dem unmittelbar nach der Anlage der extended statistics durchgeführten dbms_stats-Aufruf erscheint auch die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH in col_usage$ und folglich wird auch ein Histogramm erzeugt:
SQL> @ C:\temp\test_col_usage.sql
Tabelle wurde gelöscht.
Tabelle wurde erstellt.
DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
----------------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH
PL/SQL-Prozedur erfolgreich abgeschlossen.
COUNT(*)
----------
1000
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1 100000 100 21.03.2012 NONE
COL2 100000 100 21.03.2012 NONE
ID 100000 100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 100000 100 21.03.2012 NONE
PL/SQL-Prozedur erfolgreich abgeschlossen.
COLUMN_NAME EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1 1 21.03.2012
COL2 1 21.03.2012
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 1 21.03.2012
COLUMN_NAME SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1 5591 100 21.03.2012 FREQUENCY
COL2 5591 100 21.03.2012 FREQUENCY
ID 100000 100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH 5591 100 21.03.2012 FREQUENCY
Das Ergebnis lässt sich regelmäßig reproduzieren, aber eine schlüssige Erklärung dafür fehlt mir noch.
Hallo Martin,
AntwortenLöschenein paar Kommentare:
1. Du kannst die Extended Statistics direkt in DBMS_STATS.GATHER_TABLE_STATS einbauen im METHOD_OPT-Parameter, also in Deinem Falle:
... METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS (COL1, COL2) SIZE AUTO'...
Das spart den extra Aufruf für das Generieren der Extended Statistics und verhindert das Problem mit nicht existierenden Statistiken
2. Die 5% sind ein Problem, auf das ich schon verschiedentlich hingewiesen habe und haben nichts mit den Extended Statistics zu tun, sondern mit der Tatsache, dass ein Histogramm erstellt wird.
Bei der Verwendung von AUTO_SAMPLE_SIZE verwendet 11g ja den neuen NDV APPROXIMATE-Algorithmus, der grundsätzlich alle Daten liest (100%), aber nicht den Overhead der Aggregation mit sich bringt.
Das gibt also sehr akkurate Statistiken in kurzer Zeit.
Wenn aber ein Histogramm erstellt wird, verwendet AUTO_SAMPLE_SIZE für den zusätzlichen Pass zum Erstellen des Histogramms nur noch ca. 5500 Zeilen, also je nach Tabellengröße ein sehr kleines Sampling. Das führt dazu, dass die Histogramme doch recht ungenau sein können und wichtige Informationen "übersehen", und auch zu so merkwürdigen Effekten, dass in der Spalten-Statistiken z.B. 3 DISTINCT values stehen, das Histogramm aber nur einen Wert beinhaltet, also im Grunde inkonsistente Informationen. Oracle versucht hier m.E. zu sehr die Performance der Statistik-Erstellung in den Vordergrund zu stellen. Mir wären höherwertige Histogramme lieber.
Leider führt der einzige Workaround für höherwertige Histogramme nur über ein ESTIMATE_PERCENT ungleich AUTO_SAMPLE_SIZE, und das schaltet dann den neuen NDX APPROXIMATE Algorithmus wieder ab...
Insofern kann es Sinn machen, die Statistiken in zwei Schritten zu erstellen: Für alle Spalten ohne Histogramme per AUTO_SAMPLE_SIZE, und ein zweites Mal nur für die Spalten mit Histogrammen mit explizitem ESTIMATE_PERCENT.
Das ist natürlich auch nicht optimal, da dann SIZE AUTO keinen Sinn mehr macht (man muss dann schon wissen, welche Spalten Histogramme haben sollen), und das Segment muss mindestens zweimal gelesen werden...
3. Vielen Dank für den Hinweis mit dem fehlenden Spalten-Monitoring für die erweiterten Statistiken, das ist mir bisher nicht aufgefallen.
Randolf
Hallo Randolf,
AntwortenLöschenvielen Dank für die Hinweise. Nr. 1 macht die Anlage deutlich übersichtlicher und Nr. 2 erklärt einiges.
Martin