Dieser Tage habe ich bei der Zusammenführung von Fakten-Daten aus zwei unterschiedlichen Quellsystemen einige recht interessante Entscheidungen des Optimizers beobachtet, die ich erst jetzt beim Aufbau entsprechender Test-Beispiele verstanden habe (oder glaube, verstanden zu haben). Die entscheidende Vorraussetzung war, dass die identisch strukturierten Daten der beiden Quellen über UNION ALL-Views zusammengeführt werden sollten, um einerseits die bestehende ETL-Logik nicht ändern zu müssen und andererseits eine klare physikalische Trennung der Daten zu erlauben. Dass diese Lösung nicht die einzige Möglichkeit war - und vielleicht auch nicht in jeder Hinsicht die günstigste - war dabei von vornherein klar.
Die Einführung der UNION ALL-Views für die Fakten führte dazu, dass eine ganze Reihe bis dahin sehr harmloser NESTED LOOPs Joins zwischen den Fakten und den zugehörigen Dimensionstabellen durch zwei komplexere Operationen ersetzt wurden. Dazu ein Beispiel (mit 11.1.0.7). Zunächst die Ausgangssituation:
-- Löschung der Testobjekte
drop table fact;
drop table fact2;
drop table dim;
-- Anlage Dimensionstabelle und Index
create table dim
as
select rownum id
, mod(rownum, 10) col1
, lpad('*', 100, '*') padding
from dual
connect by level <= 10000;
exec dbms_stats.gather_table_stats(user, 'dim')
create index dim_idx on dim(id);
-- Anlage Faktentabelle und Index
create table fact
as
select mod(rownum, 100) id
, mod(rownum, 1000) col1
, lpad('*', 100, '*') padding
from dual
connect by level <= 1000000;
exec dbms_stats.gather_table_stats(user, 'fact')
create index fact_idx on fact (id);
-- Join-Query
explain plan for
select count(fact.col1)
from dim
, fact
where dim.id = fact.id
and dim.id <= 10;
select * from table(dbms_xplan.display);
Plan hash value: 3293636826
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1315 (1)| 00:00:16 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 11102 | 119K| 1315 (1)| 00:00:16 |
|* 4 | INDEX RANGE SCAN | DIM_IDX | 10 | 40 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | FACT_IDX | 1110 | | 20 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| FACT | 1110 | 7770 | 1132 (1)| 00:00:14 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DIM"."ID"<=10)
5 - access("DIM"."ID"="FACT"."ID")
filter("FACT"."ID"<=10)
Hier ergibt sich ein NL-Join, bei dem der Index der Dimensionstabelle 10 Sätze liefert, die zu zehn Zugriffen auf den Index der Faktentabelle führen, aus dem dann jeweils 10000 entsprechende Werte gelesen werden. Die Fehlkalkulation des CBO, der statt 10000 nur 1110 rows schätzt, schiebe ich dabei auf das über Transitive Closure ergänzte Filter-Prädikat filter("FACT"."ID"<=10), aber das ist eine andere Geschichte, die ein andermal erzählt werden soll (oder auch nicht). Auch die Frage, ob der NL-Join im gegebenen Fall eine gute Wahl ist, sei ausgeklammert (bzw. kurz mit einem "möglicherweise nicht" beantwortet, da sich die Anzahl der LIOs durch die schlechte Clusterung der Tabellen-Daten hinsichtlich des Index gegenüber einem HASH_JOIN deutlich erhöht, währen die PIOs sinken). Die Struktur des NL ist dabei die der in 11g eingeführten NL-Optimierungen, über die Randolf Geist drei interessante Artikel geschrieben hat, die ich vor einiger Zeit hier verlinkt hatte. Nun zur Umwandlung der Faktentabelle in eine UNION ALL-View:
-- Anlage einer zweiten Faktentabelle (mit nur einem Satz)
create table fact2
as
select 1 id
, 4711 col1
, lpad('*', 100, '*') padding
from dual;
exec dbms_stats.gather_table_stats(user, 'fact2')
create index fact2_idx on fact2(id);
-- Anlage einer UNION ALL-View für die Fakten
create or replace view v_fact
as
select * from fact
union all
select * from fact2;
-- Join-Query
explain plan for
select count(v_fact.col1)
from dim
, v_fact
where dim.id = v_fact.id
and dim.id <= 10;
select * from table(dbms_xplan.display);
Plan hash value: 522046762
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 4304 (1)| 00:00:52 |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | HASH JOIN | | 111 | 3330 | 4304 (1)| 00:00:52 |
|* 3 | INDEX RANGE SCAN | DIM_IDX | 10 | 40 | 2 (0)| 00:00:01 |
| 4 | VIEW | V_FACT | 111K| 2818K| 4301 (1)| 00:00:52 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | FACT | 111K| 758K| 4299 (1)| 00:00:52 |
| 7 | TABLE ACCESS BY INDEX ROWID| FACT2 | 1 | 7 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | FACT2_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DIM"."ID"="V_FACT"."ID")
3 - access("DIM"."ID"<=10)
6 - filter("ID"<=10)
8 - access("ID"<=10)
Durch die Verwendung der UNION ALL-View kommt der CBO davon ab, die Verknüpfung von Dimension und Fakten per NL durchzuführen, und steigt stattdessen auf einen HASH JOIN um. Dabei verzichtet er auf den Index-Zugriff für die FACT-Tabelle. Mir war im ersten Moment nicht klar, ob der NL-Join in einem solchen Fall überhaupt noch möglich ist, aber das kann man ja ausprobieren:
explain plan for
select /*+ use_nl(dim v_fact) */ count(v_fact.col1)
from dim
, v_fact
where dim.id = v_fact.id
and dim.id <= 10;
select * from table(dbms_xplan.display);
Plan hash value: 802202492
Plan hash value: 802202492
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 11359 (1)| 00:02:17 |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
| 2 | NESTED LOOPS | | 111 | 3219 | 11359 (1)| 00:02:17 |
|* 3 | INDEX RANGE SCAN | DIM_IDX | 10 | 80 | 2 (0)| 00:00:01 |
| 4 | VIEW | V_FACT | 1 | 21 | 1136 (1)| 00:00:14 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| FACT | 1110 | 7770 | 1134 (1)| 00:00:14 |
|* 8 | INDEX RANGE SCAN | FACT_IDX | 1110 | | 22 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| FACT2 | 1 | 7 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | FACT2_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DIM"."ID"<=10)
6 - filter(10>="DIM"."ID")
8 - access("ID"="DIM"."ID")
filter("ID"<=10)
9 - filter(10>="DIM"."ID")
11 - access("ID"="DIM"."ID")
filter("ID"<=10)
Hier zerlegt der CBO die Join-Operation in zwei Teile und zieht die Dimensionseinschränkung über Predicate Pushdown in die UNION ALL View, also als Pseudo-Code etwa:
NL(dim * fact) + NL(dim * fact2)
In diesem Fall wird auch wieder ein Index-Zugriff über FACT_IDX gewählt.
Die Effizienz der unterschiedlichen Verfahren ist dabei ein anderes Thema (und auch das Costing des CBO): für mich waren erst einmal die vorhandenen Optionen interessant. Dass der CBO im Test die Verknüpfung von Dimension und Tabelle über NL favorisiert und bei der Verknüpfung von Dimension und UNION ALL-View ohne explizite Hints den HASH JOIN wählt, ist dabei ein interessantes Detail, das bereits darauf hindeutet, dass die Ersetzung der Tabellen durch die Views allerlei Überraschungen mit sich bringen kann (und die haben wir dann auch erlebt ...).
Keine Kommentare:
Kommentar veröffentlichen