Ein Exkurs im Rahmen einer Anfrage im OTN Forum hat mir mal wieder gezeigt, wie viele Lücken mein Wissen über die Kalkulationsregeln des CBO hat. Die eigentliche Ausgangsfragestellung lasse ich dabei ausgeklammert, aber im Rahmen der Analyse wurde der Vorschlag gemacht, die column statistics einer Index-Spalte zu löschen. Die Frage ist jetzt, wie der CBO die Cardinality des Zugriffs bestimmt. Dazu das Beispiel, das ich auch im OTN-Thread beigesteuert habe, und in dem ich die cardinalities für IN-list-Zugriffe mit einem, zwei und zwanzig Elementen jeweils mit und ohne column statistics untersuche:
-- 11.1.0.7
drop table t;
create table t
as
select 'testtesttesttesttest' || rownum id
, mod(rownum, 5) col2
, lpad('*', 50, '*') padding
from dual
connect by level <= 100000;
alter table t add constraint t_pk primary key (id, col2);
exec dbms_stats.gather_table_stats(user, 'T')
-- with column statistics
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
);
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 80 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 80 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_PK | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='testtesttesttesttest1')
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
, 'testtesttesttesttest2'
);
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 160 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 | 160 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 2 | | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest2')
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
, 'testtesttesttesttest2'
, 'testtesttesttesttest3'
, 'testtesttesttesttest4'
, 'testtesttesttesttest5'
, 'testtesttesttesttest6'
, 'testtesttesttesttest7'
, 'testtesttesttesttest8'
, 'testtesttesttesttest9'
, 'testtesttesttesttest10'
, 'testtesttesttesttest11'
, 'testtesttesttesttest12'
, 'testtesttesttesttest13'
, 'testtesttesttesttest14'
, 'testtesttesttesttest15'
, 'testtesttesttesttest16'
, 'testtesttesttesttest17'
, 'testtesttesttesttest18'
, 'testtesttesttesttest19'
, 'testtesttesttesttest20'
);
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1600 | 26 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 20 | 1600 | 26 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 20 | | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest10'
OR "ID"='testtesttesttesttest11' OR "ID"='testtesttesttesttest12' OR
"ID"='testtesttesttesttest13' OR "ID"='testtesttesttesttest14' OR
"ID"='testtesttesttesttest15' OR "ID"='testtesttesttesttest16' OR
"ID"='testtesttesttesttest17' OR "ID"='testtesttesttesttest18' OR
"ID"='testtesttesttesttest19' OR "ID"='testtesttesttesttest2' OR
"ID"='testtesttesttesttest20' OR "ID"='testtesttesttesttest3' OR
"ID"='testtesttesttesttest4' OR "ID"='testtesttesttesttest5' OR
"ID"='testtesttesttesttest6' OR "ID"='testtesttesttesttest7' OR
"ID"='testtesttesttesttest8' OR "ID"='testtesttesttesttest9')
-- without column statistics
exec dbms_stats.delete_column_stats(user, 'T', 'ID')
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
);
select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 80000 | 86 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 80000 | 86 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_PK | 400 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='testtesttesttesttest1')
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
, 'testtesttesttesttest2'
);
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 80000 | 87 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 80000 | 87 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 400 | | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest2')
explain plan for
select *
from t
where id in ('testtesttesttesttest1'
, 'testtesttesttesttest2'
, 'testtesttesttesttest3'
, 'testtesttesttesttest4'
, 'testtesttesttesttest5'
, 'testtesttesttesttest6'
, 'testtesttesttesttest7'
, 'testtesttesttesttest8'
, 'testtesttesttesttest9'
, 'testtesttesttesttest10'
, 'testtesttesttesttest11'
, 'testtesttesttesttest12'
, 'testtesttesttesttest13'
, 'testtesttesttesttest14'
, 'testtesttesttesttest15'
, 'testtesttesttesttest16'
, 'testtesttesttesttest17'
, 'testtesttesttesttest18'
, 'testtesttesttesttest19'
, 'testtesttesttesttest20'
);
select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 80000 | 102 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1000 | 80000 | 102 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_PK | 400 | | 21 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"='testtesttesttesttest1' OR "ID"='testtesttesttesttest10'
OR "ID"='testtesttesttesttest11' OR "ID"='testtesttesttesttest12' OR
"ID"='testtesttesttesttest13' OR "ID"='testtesttesttesttest14' OR
"ID"='testtesttesttesttest15' OR "ID"='testtesttesttesttest16' OR
"ID"='testtesttesttesttest17' OR "ID"='testtesttesttesttest18' OR
"ID"='testtesttesttesttest19' OR "ID"='testtesttesttesttest2' OR
"ID"='testtesttesttesttest20' OR "ID"='testtesttesttesttest3' OR
"ID"='testtesttesttesttest4' OR "ID"='testtesttesttesttest5' OR
"ID"='testtesttesttesttest6' OR "ID"='testtesttesttesttest7' OR
"ID"='testtesttesttesttest8' OR "ID"='testtesttesttesttest9')
Meine vorläufige Interpretation dazu lautet:
- mit (akkuraten) column statistics kann der CBO die cardinalities in allen Fällen (1, 2, 20) exakt bestimmen.
- nach der Löschung der Statistiken der in der condition verwendeten Spalte wird in allen Fällen eine cardinality von 400 angenommen (wobei die cost Angaben sich sukzessive erhöhen, wenn sich die Länge der IN-list vergrößert).
- die 400 bleiben übrigens auch erhalten, wenn man auch die Statistiken der zweiten PK-Spalte löscht und sogar, wenn man die zweite Spalte nicht in den PK aufnimmt (die zugehörigen Tests habe ich hier unterschlagen, da sie mit geringfügigem Aufwand aus dem bestehenden Test erzeugt werden können). In diesem Fall wird allerdings aus dem range scan ein unique scan und die cardinality ist für die Variante mit einem Element korrekt mit 1 bestimmt - offenbar erkennt der CBO in diesem Fall, dass ein unique scan auf einen Wert auch nur (höchstens) einen Satz liefern kann.
- Anscheinend ist dieser Faktor 400/100000 = 0,004 ein fixierter Wert für diesen Fall.
- wenn man den Index komplett entfernt, kommt der CBO im Test stabil auf eine cardinality von 1000 also 0,001.
Ich vermute, dass diese Annahmen des CBO irgendwo dokumentiert und bekannt sind, aber den einzigen Hinweis, den ich auf Anhieb finden konnte, liefert Richard Foote, der einen 0,4% bzw. 1% guess des CBO erwähnt, aber nicht näher erläutert. Möglicherweise trage ich hier die Details noch nach, sollte ich sie irgendwo finden.
Keine Kommentare:
Kommentar veröffentlichen