-- Fall 1
-- Löschung und Neuanlage einer partitionierten Tabelle
-- mit einem lokalen Index
drop table test_part_index_cost;
create table test_part_index_cost
( id number
, salesdate date
, col1 number
, padding varchar2(100)
)
partition by range (salesdate)
(
partition p201207 values less than ( to_date('01.08.2012','dd.mm.yyyy'))
, partition p201208 values less than ( to_date('01.09.2012','dd.mm.yyyy'))
, partition p201209 values less than ( to_date('01.10.2012','dd.mm.yyyy'))
, partition p201210 values less than ( to_date('01.11.2012','dd.mm.yyyy'))
, partition p201211 values less than ( to_date('01.12.2012','dd.mm.yyyy'))
, partition p201212 values less than ( to_date('01.01.2013','dd.mm.yyyy'))
);
-- Füllung mit langweiligen Testdaten:
-- Id: eindeutig
-- col1: 100 Werte ohne Clusterung
-- padding: ein Haufen Sterne, die die Sätze breiter machen
-- für jeden Tag des Halbjahrs werden 5000 Sätze erzeugt
insert into test_part_index_cost
with
basedata as (
select rownum id
, to_date('01.07.2012', 'dd.mm.yyyy') + trunc((rownum - 1)/5000) salesdate
, mod(rownum, 100) col1
, lpad('*', 50, '*') padding
from dual
connect by level <= (184 * 5000)
)
select *
from basedata
where salesdate <= to_date('31.12.2012', 'dd.mm.yyyy')
;
exec dbms_stats. gather_table_stats(user, 'test_part_index_cost')
-- Index-Anlage mit PCTFREE 90 (um einen etwas größeren Index zu erhalten)
create index ix_test_part_index_cost on test_part_index_cost(col1) local pctfree 90;
-- Fall 2
-- Anlage einer Tabelle ohne Partitionierung
drop table test_index_cost;
create table test_index_cost
( id number
, salesdate date
, col1 number
, padding varchar2(100)
);
-- Füllung mit den gleichen Daten, mit denen auch
-- die partitionierte Tabelle gefüllt wurde
insert into test_index_cost
with
basedata as (
select rownum id
, to_date('01.07.2012', 'dd.mm.yyyy') + trunc((rownum - 1)/5000) salesdate
, mod(rownum, 100) col1
, lpad('*', 50, '*') padding
from dual
connect by level <= (184 * 5000)
)
select *
from basedata
where salesdate <= to_date('31.12.2012', 'dd.mm.yyyy')
;
exec dbms_stats. gather_table_stats(user, 'test_index_cost')
create index ix_test_index_cost on test_index_cost(col1) pctfree 90;
-- Test-Zugriffe mit Erhebung von Plan-Statistiken
select /*+ gather_plan_statistics */ count(*)
from test_part_index_cost
where col1 = 1;
select /*+ gather_plan_statistics */ count(*)
from test_index_cost
where col1 = 1;
Zusätzlich zur Auswertung mit Hilfe von dbms_xplan habe ich außerdem noch ein CBO-Trace (Event 10053) erzeugt. Die Angaben im CBO-Trace sind dabei für beide Fälle sehr ähnlich. Geringfügige Abweichungen gibt es zunächst bei den Basisstatistiken für Tabelle und Index:
-- partitionierte Tabelle
Table Stats::
Table: TEST_PART_INDEX_COST Alias: TEST_PART_INDEX_COST (Using composite stats)
#Rows: 920000 #Blks: 9528 AvgRowLen: 66.00
Index Stats::
Index: IX_TEST_PART_INDEX_COST Col#: 3
USING COMPOSITE STATS
LVLS: 2 #LB: 19990 #DK: 100 LB/K: 32.00 DB/K: 1533.00 CLUF: 919803.00
-- nicht partitionierte Tabelle
Table Stats::
Table: TEST_INDEX_COST Alias: TEST_INDEX_COST
#Rows: 920000 #Blks: 10097 AvgRowLen: 66.00
Index Stats::
Index: IX_TEST_INDEX_COST Col#: 3
LVLS: 2 #LB: 19988 #DK: 100 LB/K: 199.00 DB/K: 9198.00 CLUF: 919803.00
Die Level-Angabe LVLS ist identisch, ebenso die Anzahl distinkter Keys (#DK) und der Clustering Factor (CLUF), was nicht überrascht, weil die Tabellen mit dem gleichen Verfahren gefüllt wurden und der CF ein Maß für die Sortierung der Tabelle in Hinblick auf den Index darstellt. Die Abweichungen der "per Key"-Werte (LB/K, DB/K) ergeben sich meiner Interpretation nach aus der Verwendung der "composite stats" für den partitionierten Index: sie entsprechen den Angaben der Einzelpartitionen. Wenn man die Index-Statistiken explizit global erhebt, erhält man für die "per Key"-Angaben Werte, die denen des nicht-partitionierten Falls entsprechen.
Wenn man die LVLS- und die #LB-Angabe des nicht-partitionierten Index in die Standard-Formel für das Costing von Indizes
also: blevel + (ix_sel * leaf_blocks) + (ix_sel_with_filters * clustering_factor)einsetzt erhält man, wenn man die ix_sel als 1% einsetzt und den letzten Teil der Formel ignoriert, weil der Tabellen-Zugriff in diesem Fall entfällt:
2 + ceil(0.01 * 19988) = 202Im CBO-Trace findet man dazu folgende Angabe:
Access Path: index (AllEqRange)
Index: IX_TEST_INDEX_COST
resc_io: 202.00 resc_cpu: 3278531
ix_sel: 0.010000 ix_sel_with_filters: 0.010000
Cost: 202.20 Resp: 202.20 Degree: 1
Die resc_io-Angabe entspricht also exakt den Erwartungen. Hier auch noch der Plan und die Ausführungsstatistiken zur Query (jeweils um die Time-Angaben gekürzt):
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 202 (100)|
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | INDEX RANGE SCAN| IX_TEST_INDEX_COST | 9200 | 27600 | 202 (0)|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=1)
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 | 203 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 203 |
|* 2 | INDEX RANGE SCAN| IX_TEST_INDEX_COST | 1 | 9200 | 9200 | 203 |
-------------------------------------------------------------------------------------
Demnach sind die 202 also auch eine sehr gute Schätzung der tatsächlich anfallenden Arbeit von 203 LIOs.
Nun aber zur partitionierten Tabelle und zum partitionierten Index. Das Einsetzen der LVLS und #LB aus den globalen Index-Statistiken bringt für diesen Fall das gleiche Ergebnis wie für den nicht partitionierten Index:
2 + ceil(0.01 * 19990) = 202Tatsächlich liegen die Kosten aber bei 212, also um genau 10 höher:
Access Path: index (AllEqRange)
Index: IX_TEST_PART_INDEX_COST
resc_io: 212.00 resc_cpu: 3349745
ix_sel: 0.010000 ix_sel_with_filters: 0.010000
Cost: 212.20 Resp: 212.20 Degree: 1
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 212 (100)| | |
| 1 | SORT AGGREGATE | | 1 | 3 | | | |
| 2 | PARTITION RANGE ALL| | 9200 | 27600 | 212 (0)| 1 | 6 |
|* 3 | INDEX RANGE SCAN | IX_TEST_PART_INDEX_COST | 9200 | 27600 | 212 (0)| 1 | 6 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=1)
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 | 220 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 220 |
| 2 | PARTITION RANGE ALL| | 1 | 9200 | 9200 | 220 |
|* 3 | INDEX RANGE SCAN | IX_TEST_PART_INDEX_COST | 6 | 9200 | 9200 | 220 |
---------------------------------------------------------------------------------------------
Woher kommt diese Differenz? Meine Vermutung ist, dass diese zehn Kosten-Punkte für den Zugriff auf die fünf zusätzlichen Teil-Indizes entstehen, weil statt einer einzelnen Index-Struktur jetzt insgesamt sechs Indizes betrachtet werden müssen: also 5 zusätzliche Root-Block-Zugriffe und 5 zusätzliche Branch-Block-Zugriffe. Tatsächlich sehe ich die gleiche Differenz von 10 auch für den folgenden Fall (und nehme an, dass sie bei allen entsprechenden Szenarien auftritt):
select count(*) from test_index_cost where col1 < 3; ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 609 (1)| | 1 | SORT AGGREGATE | | 1 | 3 | | |* 2 | INDEX RANGE SCAN| IX_TEST_INDEX_COST | 27879 | 83637 | 609 (1)| ----------------------------------------------------------------------------- select count(*) from test_part_index_cost where col1 < 3; ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 619 (1)| | | | 1 | SORT AGGREGATE | | 1 | 3 | | | | | 2 | PARTITION RANGE ALL| | 27879 | 83637 | 619 (1)| 1 | 6 | |* 3 | INDEX RANGE SCAN | IX_TEST_PART_INDEX_COST | 27879 | 83637 | 619 (1)| 1 | 6 | -----------------------------------------------------------------------------------------------------
Ich will jetzt nicht behaupten, dass das ein ernsthafter Beweis für meine Annahme ist: dazu ist der Test doch etwas zu anekdotisch. Aber da es mir gerade an Ausdauer mangelt, die Untersuchung zu plausibilisieren, lasse ich es erst einmal so stehen, um die Untersuchung möglicherweise bei Gelegenheit fortzusetzen.
Nachtrag 18.07.2012: Um die Beweisführung ein wenig stichhaltiger zu machen, ein weiteres Beispiel mit einer Einschränkung auf eine Teilmenge der Partitionen, denn die bisherigen Ergebnisse hätten sich auch als Aggregierung der Kosten der Zugriffe auf die Partitions-Indizes erklären lassen, obwohl das CBO-Trace keinen Hinweis auf ein solches Vorgehen zu enthalten schien und nur die globalen Statistiken lieferte:
select /*+ index(t) */ count(*) from test_index_cost t where col1 = 1 and salesdate >= '01.10.2012';
Für die nicht partitionierte Tabelle hat die zusätzliche Einschränkung auf salesdate erwartungsgemäß keinen Einfluss auf die Kosten des Index-Zugriffs, die weiterhin mit 202 angegeben werden:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9405 (1)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX_COST | 4625 | 9405 (1)|
|* 3 | INDEX RANGE SCAN | IX_TEST_INDEX_COST | 9200 | 202 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALESDATE">=TO_DATE(' 2012-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("COL1"=1)
Wenn man die gleichen Einschränkungen für die partitionierte Tabelle angibt, erhält man folgende Ergebnisse:
select /*+ index(t) */ count(*)
from test_part_index_cost t
where col1 = 1
and salesdate >= '01.10.2012';
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9410 (1)| | |
| 1 | SORT AGGREGATE | | 1 | | | |
| 2 | PARTITION RANGE ITERATOR| | 4625 | 206 (0)| 4 | 6 |
|* 3 | INDEX RANGE SCAN | IX_TEST_PART_INDEX_COST | 4625 | 206 (0)| 4 | 6 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=1)
Hier entfällt der Tabellenzugriff, weil die Datumseinschränkung bereits durch die Partition Elimination sichergestellt ist. Für meine Theorie zum Costing ist aber vor allem der Wert 206 interessant, den ich als Basiskosten + Zugriff auf drei Partitionen statt auf einen einzelnen Index (also 2 zusätzliche Zugriffe jeweils auf Root- und Branch-Block) interpretiere: also 202 + (2 * 2) = 206. Offenbar handelt es sich aber nicht um die aggregierten Kosten des Zugriffs auf die drei relevanten lokalen Indizes, denn die dürften nur etwa halb so hoch sein (106).
Bei einer Beschränkung auf eine Partition ändert sich für den nicht partitionierten Fall (natürlich) nichts. Für den partitionierten Index hingegen sinken die Kosten auf 36:
------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Pstart| Pstop | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1586 (0)| | | | 1 | SORT AGGREGATE | | 1 | | | | | 2 | PARTITION RANGE SINGLE | | 50 | 1586 (0)| 4 | 4 | |* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TEST_PART_INDEX_COST | 50 | 1586 (0)| 4 | 4 | |* 4 | INDEX RANGE SCAN | IX_TEST_PART_INDEX_COST | 1550 | 36 (0)| 4 | 4 | ------------------------------------------------------------------------------------------------------------
In diesem Fall werden dann offenbar statt der globalen die Partitionsstatistiken herangezogen, also blevel + (ix_sel * leaf_blocks) = 2 + ceil(0,01 * 3368) = 36.
Bei Hotsos kann man als registrierter Nutzer ein Paper von Gary Propeck (Januar 2012) downloaden, das sich mit den Kosten des Zugriffs auf partitionierte Tabellen und lokale Indizes beschäftigt. Der hier von mir entwickelten Theorie zur Kostenberechnung für den Zugriff über lokale Indizes bin ich dort allerdings nicht begegnet (was natürlich auch ein Hinweis darauf sein könnte, dass sie nicht zutrifft ...).
Keine Kommentare:
Kommentar veröffentlichen