MDX-Optimierung ist in Ermangelung eines Hilfsmittels zur Darstellung von Ausführungsplänen aus meiner Sicht zum großen Teil ein wildes Ausprobieren verschiedener Syntaxvarianten - und ich muss gestehen, dass ich dieser Tage nicht mehr viel Freude daran habe. Gut zu wissen ist aber, dass wenigstens Mosha Pasumanskys MDX Studio, das dieser Tage von der Bildfläche verschwunden zu sein schien, Dank des entschlossenen Handelns von Marco Russo weiterhin zum Download bereitsteht. Mit dem MDX Studio bekommt man zumindest eine klarere Vorstellung davon, welche Wirkung das Ausprobieren der Syntaxvarianten auf die Ressourcennutzung des SSAS hat.
Freitag, September 28, 2012
Nested Loops und UNION ALL Views
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 ...).
Sonntag, September 23, 2012
Auditing für ALTER SYSTEM
Uwe Hesse zeigt in seinem Blog, wie man den AUDIT_TRAIL setzen muss, um ALTER SYSTEM-Kommandos (außer von SYS) zu protokollieren.
Samstag, September 22, 2012
Dimensionsdefinition
Die Theorie der Datenmodellierung kommt hier in der Regel zu kurz. Daher wenigstens mal ein Link zum Thema: Hilmar Buchta liefert in seinem Blog eine Liste mit Kriterien, mit denen man bestimmen kann, ob Attribute in einer oder in zwei Dimensionen zusammengefasst werden sollten.
Index Features 11g (nach Richard Foote)
Ein Exzerpt aus Richard Footes Präsentation zum gleichen Thema - der Herr Foote behauptet dort auch an einer 12c-Version zu arbeiten, die ich gerne frühzeitig zu Gesicht bekäme. Das Exzerpt ist wie immer ausgesprochen subjektiv und führt nicht alle wichtigen Punkte der Präsentation auf (sondern vor allem die, die mir nicht selbstverständlich erscheinen):
- Online Rebuild:
- EE Feature
- verhindert parallel execution
- benötigt ein table lock zum Beginn und zum Ende des Index-Aufbaus
- in 11g führen die locks nicht zur Beeinträchtigung folgender DML-Operationen (was in früheren Versionen der Fall war)
- ein rebuild ist dadurch in Produktivsystemen weniger problematisch (aber in der Regel immer noch nutzlos)
- Index Statistiken
- geben dem CBO Informationen zur Korrelation von Werten im Index (über die DISTINCT_KEYS-Angabe, was natürlich nur funktioniert, wenn alle Index-Spalten eingeschränkt werden, wie ich eben noch mal ausprobiert habe), früher wurden die Einzelwahrscheinlichkeiten der Spalten kombiniert (ich hatte vergessen, dass das erst in 11g eingeführt wurde)
- index monitoring kann den CBO davon abhalten, die index Statistiken bei der Planerstellung heranzuziehen!
- Invisible Indexes
- werden in 11.1 zum lookup nach child-rows bei der Löschung von parent rows verwendet (obwohl sie unsichtbar sind/sein sollten); in 11.2 ist das nicht mehr der Fall
- auch in diesem Fall verhält sich index monitoring seltsam, da diese Nutzung eine Index nicht registriert wird (so dass die Löschung eines solchen Index massive Folgen hat)
- werden in 11.1 als Informationsquelle für den CBO herangezogen (in 11.2 nicht mehr)
- können zur Unterstützung von PK/UK-Constraints verwendet werden: in diesem Fall werden sie aber nicht für den Zugriff berücksichtigt
- die Statistikerfassung für invisible indexes hat in 11.1 einen Bug (ORA-00904: : invalid identifier), der in 11.2 gefixed ist
- Bitmap Join Indizes
- in 11 für IOTs definierbar
- Creation On Demand Segments
- 11.2: "The first row creates all dependent segments, even if indexes are not populated"
- in 11.2 werden Quotas erst aktiv, wenn Daten eingefügt werden: Objekte ohne Storage können angelegt werden
- in 11.2 wird das Segment eines Index, der als unusable markiert ist, gelöscht. In 11.1.0.7 war das noch nicht der Fall (auch das habe ich gerade ausprobiert)
- für nicht partitionierte Indizes einer partitionierten Tabelle bleibt das Segment auch in 11.2 erhalten
- man kann sich dieses Feature zu nutze machen, um nur die relevanten Teile eines Index als usable zu behandeln (Beispiel: Processed-Kennzeichen); das Verfahren erinnert an Tom Kytes alten Trick der FBIs, die nur die relevanten Daten berücksichtigen
- IGNORE_ROW_ON_DUPKEY_INDEX
- ein Hint zum stillschweigenden Übergehen von Duplikatsätze, die sonst ORA-00001-Index-Fehler hervorgerufen hätten. Meinen Kommentar dazu und einen Link auf den Blog-Artikel des Herrn Foote gibt's hier.
- funktioniert nicht bei UPDATE und nur mit Indizes, die als UNIQUE definiert sind
- ANALYZE VALIDATE STRUCTURE FAST
- "more efficient VALIDATE STRUCTURE command option", aber eine Mogelpackung
- FBIs
- ihre virtual columns sind erst sein 11 im dictionary sichtbar
- in 11 kann man virtual columns auch ohne index haben
Dienstag, September 18, 2012
Exchange Partition Bug
Randolf Geist hat dieser Tage einen seltsamen (und vermutlich schon ziemlich alten) Bug beschrieben, der in der Kombination von Partition Exchange und virtuellen Spalten auftritt und zu einer Verschiebung der column statistics in user_tab_cols führt. Das Problem ist in 11g relevanter geworden, da auch die extended statistics virtuelle Spalten verwenden, lässt sich aber relativ leicht umgehen.
Statistikerhebung für Tabellengruppe
Maria Colgan erläutert im Blog der CBO-Entwickler, wie man die Erhebung von Statistiken mit DBMS_STATS.GATHER_SCHEMA_STATS durch Verwendung des Parameters obj_filter_list auf eine Untergruppe einschränken kann (wobei das Verfahren sehr übersichtlich ist).
Sonntag, September 16, 2012
SYS_OP_MAP_NONNULL
Ein paar Links zur (undokumentierten und daher in Produktionssystemen ungeeigneten) Funktion SYS_OP_MAP_NONNULL, die im Zusammenhang mit NULL-Vergleichen recht häufig erwähnt wird:
- Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives: Eddie Awads Erläuterungen zur Funktion und alternativen Verfahren. In den Kommentaren wird darüber diskutiert, ob das Mapping von SYS_OP_MAP_NONNULL mit einem existierenden RAW-Wert kollidieren könnte (Jonathan Lewis sagt: nein)
- AskTom: Mit Tom Kytes Warnung: "it's undocumented, and therefore poses a risk of going away or changing functionality. That is enough said that should make people just 'stop reading'"
- Oracle Scratchpad: worin Jonathan Lewis einen Fall (i.e. Bug) beschreibt, in dem der Fast Refresh einer MV aufgrund der internen Verwendung von SYS_OP_MAP_NONNULL scheitert.
Samstag, September 15, 2012
KEEP Klausel
Rob van Wijk, den ich als einen der besten Kenner von Oracles SQL-Repertoire betrachte, erläutert in seinem Blog die Rolle der (zu den Funktionen FIRST und LAST gehörenden) KEEP clause, die die Dokumentation folgendermaßen erklärt:
When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.
Anders ausgedrückt gestattet es die KEEP clause nach einer anderen Spalte als der Ergebnisspalte zu sortieren. Hier ein simples Beispiel mit EMP, in dem ich pro Department den Mitarbeiter mit der längsten Unternehmenszugehörigkeit anzeigen lasse:
select deptno
, min(hiredate) hiredate
, min(ename) keep(dense_rank first order by hiredate) first_ename
, min(job) keep(dense_rank first order by hiredate) first_job
from emp
group by deptno
DEPTNO HIREDATE FIRST_ENAM FIRST_JOB
------ -------- ---------- ---------
10 09.06.81 CLARK MANAGER
20 17.12.80 SMITH CLERK
30 20.02.81 ALLEN SALESMAN
Damit lassen sich unter Umständen überflüssige Self-Joins oder Subqueries mit einer Rank-Analytic vermeiden, also im Beispiel etwa:
with
basedata as (
select t.*
, row_number() over(partition by t.deptno order by t.hiredate) rn
from emp t
)
select deptno
, hiredate
, ename
, job
from basedata
where rn = 1
DEPTNO HIREDATE ENAME JOB
------ -------- ---------- ---------
10 09.06.81 CLARK MANAGER
20 17.12.80 SMITH CLERK
30 20.02.81 ALLEN SALESMAN
Der Vorteil der KEEP clause ist dabei nicht nur die kompaktere Syntax, sondern auch eine bessere Performance, was der Herr van Wijk anhand von Beipielen belegt. Neben der Verwendung als Aggregat-Funktionen gibt es übrigens auch eine analytische Variante der Funktionen.
Freitag, September 14, 2012
Bloom Filter
In dieser Woche habe ich ein paar Bloom-Filter in einem Execution Plan entdeckt und konnte auf Anhieb nur noch erinnern, dass sie false positives gestatten, aber keine false negatives. Eine schöne Darstellung ihrer Verwendung bei Oracle (samt weiter führenden Links) liefert Julian Dontcheff in seinem Blog. Einer der Links führt zu einem Paper von Christian Antognini, der folgende Definition angibt: "A bloom filter is a data structure used to support membership queries. Simply put, a bloom filter is used to test whether an element is member of a given set or not".
Nachtrag 07.08.2013: ein kompaktes Beispiel mit Bloom Filter findet man bei Jonathan Lewis.
PIVOT Klausel
Die PIVOT-Klausel definiert Oracle in der Dokumentation für 11.2 folgendermaßen:
The pivot_clause lets you write cross-tabulation queries that rotate rows into columns, aggregating data in the process of the rotation. The output of a pivot operation typically includes more columns and fewer rows than the starting data set.
Dieser Tage hat Steward Bryson erklärt, dass die Klausel ihn bei ihrer Einführung enttäuscht hätte, da sie nur mir aggregate functions arbeitet, aber dass man diese Einschränkung in manchen Fällen durch Verwendung von Aggregaten, die auch mit Strings arbeiten, wie etwa MAX umgehen kann. Mich stört an PIVOT eher die sperrige Syntax, die aus meiner Sicht alles andere als intuitiv ist. Tom Kyte hat vor einiger Zeit ein paar Varianten zur Pivotisierung zusammengestellt, worin auch das folgende Beispiel mit Scotts EMP-Tabelle enthalten ist:
select deptno, clerk, salesman,
manager, analyst, president
from (select deptno, job, sal
from emp )
pivot( sum(sal) for job in
( 'CLERK' as clerk,
'SALESMAN' as salesman,
'MANAGER' as manager,
'ANALYST' as analyst,
'PRESIDENT' as president ) )
order by deptno
/
DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT
---------- ------- -------- ------- ------- ---------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
Gerade die ALIAS-Passage erscheint mir dabei ziemlich umständlich, und der Vorteil gegenüber der traditionellen Variante mit DECODE (oder CASE) und GROUP BY (die der Herr Kyte ebenfalls vorstellt; außerdem führt er auch noch eine obskurere Methode mit COLLECT auf) ist aus meiner Sicht nicht mehr sehr groß. Da wäre vielleicht eine übersichtlichere Definition möglich gewesen.
Im Execution Plan erscheint bei Verwendung einer PIVOT Klausel ein Step SORT GROUP BY PIVOT, statt eines einfachen GROUP BY:
SQL> set autot on
SQL> r
1 select deptno, clerk, salesman,
2 manager, analyst, president
3 from (select deptno, job, sal
4 from emp )
5 pivot( sum(sal) for job in
6 ( 'CLERK' as clerk,
7 'SALESMAN' as salesman,
8 'MANAGER' as manager,
9 'ANALYST' as analyst,
10 'PRESIDENT' as president ) )
11* order by deptno
DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT
---------- ---------- ---------- ---------- ---------- ----------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
Ausführungsplan
----------------------------------------------------------
Plan hash value: 1018027214
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY PIVOT| | 3 | 45 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
SQL> select deptno,
2 sum( decode( job, 'CLERK', sal ) ) clerk,
3 sum( decode( job, 'SALESMAN', sal ) ) salesman,
4 sum( decode( job, 'MANAGER', sal ) ) manager,
5 sum( decode( job, 'ANALYST', sal ) ) analyst,
6 sum( decode( job, 'PRESIDENT', sal ) ) president
7 from scott.emp
8 group by deptno
9 order by deptno
10 /
DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT
---------- ---------- ---------- ---------- ---------- ----------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
Ausführungsplan
----------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 3 | 45 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Ob das für Performance und Ressourcennutzung einen Unterschied macht, könnte ich mir gelegentlich mal anschauen (in diesem einfachen Beispiel deutet der Plan zunächst nicht darauf hin).
Predicate Pushdown
In dieser Woche bin ich einem Fall begegnet, in dem ein Predicate Pushdown aufgrund falscher Cardinality-Schätzungen für einen vorangehenden Join zu einer recht unglücklichen Nested Loops Operation führte. Zu Erinnerungszwecken daher hier die kurze Definition aus Maria Colgans (oder Rafi Ahmeds?) einführendem Artikel zum Thema:
In the join predicate pushdown transformation, the view remains a separate query block, but it contains the join predicate, which is pushed down from its containing query block into the view. The view thus becomes correlated and must be evaluated for each row of the outer query block. These pushed-down join predicates, once inside the view, open up new index access paths on the base tables inside the view; this allows the view to be joined with index-based nested-loop join method, thereby enabling the optimizer to select an efficient execution plan.
Bei einer korrekten Schätzung der Schleifen-Iterationen kann das natürlich sehr nützlich sein.
Mittwoch, September 12, 2012
Join Cardinality und explizite Typ-Konvertierung
Gerade habe ich im OTN-Forum einen Fall vorgestellt, bei dem der CBO zu einer seltsamen Fehleinschätzung der Join Cardinality kommt (und der den Kern eines Problems darstellt, dem ich gestern bei einer großen DWH-Query begegnet bin):
Zunächst der harmlose Versuchsaufbau:
drop table t1; drop table t2; create table t1 as select rownum col1 from dual connect by level <= 1000; create table t2 as select to_char(rownum) col1 from dual connect by level <= 1000; exec dbms_stats.gather_table_stats(user, 't1') exec dbms_stats.gather_table_stats(user, 't2')
Also zwei Tabellen, die jeweils die Werte 1 bis 1000 enthalten und deren einziger Unterschied der Datentyp ist: einmal number, einmal varchar2. Wenn ich jetzt versuche, diesen Unterschied (man könnte es wohl auch Modellierungsfehler nennen) in einer inline View zu beheben, bekomme ich ein überraschendes Ergebnis:
explain plan for
select t.col1
, t2.col1
from (select /*+ no_merge */
to_char(col1) col1
from t1) t
, t2
where t.col1 = t2.col1;
select * from table(dbms_xplan.display('', ''));
Plan hash value: 1373845765
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 25 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 1000 | 3000 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1000 | 22000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 1000 | 3000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."COL1"="T2"."COL1")
Der CBO erwartet in Step 1 also nur einen Satz! Mit der Standardformel für die Berechnung der Join Cardinality (ohne Berücksichtigung von NULL-Werten und Filterungen) bekomme ich Folgendes:
- join selectivity = 1 / greater(num_distinct(t1.col1), num_distinct(t2.col1))
- join cardinality = join selectivity * cardinality t1 * cardinality t2
- So I get 1/1000 * 1000 * 1000 = 1000
Und dieses - korrekte - Ergebnis von 1000 bekomme ich auch in nahezu jedem anderen Fall:
-- ohne no_merge
select t.col1
, t2.col1
from (select to_char(col1) col1
from t1) t
, t2
where t.col1 = t2.col1
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 6000 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 6000 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 1000 | 3000 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 1000 | 3000 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."COL1"=TO_CHAR("COL1"))
-- ohne explizite Typ-Umwandlung in der inline View
select t.col1
, t2.col1
from (select /*+ no_merge */
col1
from t1) t
, t2
where t.col1 = t2.col1
Plan hash value: 1373845765
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 16000 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 1000 | 3000 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1000 | 13000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 1000 | 3000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."COL1"=TO_NUMBER("T2"."COL1"))
-- mit zusätzlicher expliziter Typ-Umwandlung im Join
select t.col1
, t2.col1
from (select /*+ no_merge */
to_char(col1) col1
from t1) t
, t2
where to_char(t.col1) = t2.col1
Plan hash value: 1373845765
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 25000 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1000 | 25000 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T2 | 1000 | 3000 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1000 | 22000 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 1000 | 3000 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."COL1"=INTERNAL_FUNCTION("T"."COL1"))
In allen diesen Fällen erfolgt (laut access-Prädikat der Join-Operation) eine zusätzlich Typ-Umwandlung, die den CBO offenbar von der Fehleinschätzung der Cardinality abbringt. Aber warum der CBO durch die explizite Typumwandlung so massiv gestört wird, bleibt mir zunächst ein Rätsel.
Nachtrag: ein CBO-Trace zeigt für die Varianten mit und ohne to_char in der inline-View sehr ähnliche Ergebnisse. Zu den signifikanten Unterschieden gehören die folgenden Angaben:
-- mit to_char Join Card: 0.000000 = = outer (1000.000000) * inner (1000.000000) * sel (0.000000) Join Card - Rounded: 1 Computed: 0.00 -- ohne to_char Join Card: 1000.000000 = = outer (1000.000000) * inner (1000.000000) * sel (0.001000) Join Card - Rounded: 1000 Computed: 1000.00
Aber woher die sel-Angabe (0.000000) im ersten Fall kommt, sehe ich nicht.
Nachtrag 14.09.2012: in der OTN-Diskussion wurde klar, dass das Problem der unbrauchbaren Join-Cardinality-Schätzungen bei Verwendung inadäquater Datentypen auch andere Join-Syntax-Varianten betreffen kann, was den Fall noch unerfreulicher macht - und die Bedeutung einer soliden Modellierung unterstreicht.
Sonntag, September 09, 2012
Interval Partitioning und DDL-Trigger
Jonathan Lewis zeigt in seinem Blog eine weitere Besonderheit von interval partitions: die Anlage einer neuen interval Partition wird von Oracle intern nicht als DDL klassifiziert, so dass ein entsprechender (AFTER) DDL-Trigger auf die Anlage nicht reagiert; um die Sache etwas unplausibler zu machen, feuert allerdings ein entsprechender BEFORE DDL-Trigger bei der Löschung einer interval partition.
In Oracle 11.2.0.3 wird die Anlage einer neuen interval partition in der Alert.log protokolliert (TABLE xxx: ADDED INTERVAL PARTITION sys...), aber in meiner Test-Datenbank der Version 11.2.0.1 sehe ich noch keine entsprechenden Einträge.
Freitag, September 07, 2012
Entscheidungsgrundlagen für Direct Path Reads für FTS
Tanel Poder erklärt in seinem Blog, dass die für die Verwendung des direct path read Zugriffs im Rahmen von FTS-Operationen entscheidende Information der Anzahl von Blocks im Segment seit 11.2.0.2 aus den Objektstatistiken statt aus dem Segment-Header gezogen werden kann (zur Steuerung des Verhaltens dient der Parameter _direct_read_decision_statistics_driven). Diese Änderung hat auch Auswirkungen auf die Stabilität des Verfahrens:
[...] the direct path read decision is not done by the optimizer anyway and is a runtime decision done during every execution, every time any segment (including individual partitions) is scanned during query runtime. This is true for the old (up to 11.2.0.1) Oracle versions, where a direct path decision is done based on the actual, current block count in the segment header, thus the decision can suddenly change when a segment grows by a few blocks, crossing the _small_table_threshold calculation threshold. Perhaps due to performance stability reasons, this seems to have changed.
My tests on 11.2.0.2 have so far shown that when using the new statistics-driven direct path read decisions, each segments’ block counts are stored somewhere in the compiled cursor and reused during next executions of it, even if the block count of the segment changes in the optimizer stats later on! This might result in somewhat better stability as long as you don’t gather new stats – and your buffer cache size (and already cached block counts) don’t change. However if the amount of cached blocks of a segment does change (due to other, index-based accesses for example), then the direct path decision can still change during runtime. It’s just the block counts which are stored in the cursor, but the other factors affecting the decision (buffer cache size, cached block counts) can still change.
Returning Clause
Carsten Czarski hat in seinem Blog eine kurze Erläuterung der RETURNING clause für DML-Operationen (INSERT, UPDATE und DELETE; leider nicht für MERGE) veröffentlicht. Auf der Suche nach ein paar weiteren Details zum Thema habe ich bei Rob van Wijk den Hinweis gefunden, dass man im RETURNING auch Aggregationen durchführen kann, um so z.B. die Anzahl behandelter Sätze pro Typ zu ermitteln.
Oracle Magazin Beiträge
Eddie Awad hat in seinem Blog darauf hingewiesen, dass die kompletten Listen der Oracle Magazin-Beiträge der Herren Kyte und Feuerstein im Oracle Magazine Issue Archiv verlinkt sind:
Mittwoch, September 05, 2012
RELY Unschärfe
Vor ein paar Tagen hatte ich erwähnt, dass man einen als RELY definierten FOREIGN KEY nur mit Bezug auf einen mit RELY definierten PK erzeugen kann, da man sonst den ziemlich deutlichen Fehler "ORA-25158: RELY kann für Fremdschlüssel nicht angegeben werden, wenn der verknüpfte Primärschlüssel NORELY ist." erhält:
-- 11.1.0.7 drop table c; drop table p; create table p as select 1 id from dual; alter table p add constraint p_pk primary key (id); create table c as select 1 id from dual; alter table c add constraint c_fk foreign key (id) references p(id) rely; alter table c add constraint c_fk foreign key (id) references p(id) rely * FEHLER in Zeile 1: ORA-25158: RELY kann für Fremdschlüssel nicht angegeben werden, wenn der verknüpfte Primärschlüssel NORELY ist.
So also nicht. Aber was bedeutet folgendes Verhalten?
alter table c add constraint c_fk foreign key (id) references p(id);
alter table c modify constraint c_fk rely novalidate;
alter table c disable constraint c_fk;
select constraint_name
, rely
, validated
, status
from user_constraints
where constraint_name in ('P_PK', 'C_FK');
CONSTRAINT_NAME RELY VALIDATED STATUS
------------------------------ ---- ------------- --------
C_FK RELY NOT VALIDATED DISABLED
P_PK VALIDATED ENABLED
explain plan for
select c.id
from c
, p
where c.id = p.id;
select *
from table(dbms_xplan.display('', ''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2174124444
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| C | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C"."ID" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
Wenn man die Anlage und die Umstellung auf RELY voneinander trennt, funktioniert die Umstellung demnach ohne Fehler und laut user_constraints ist der FK tatsächlich als RELY definiert, obwohl der PK es nicht ist. Und für die Test-Query funktioniert auch die table elimination. Seltsam.
Inkrementelle Statistiken für partitionierte Tabellen
Obwohl ich hier in der Vergangenheit diverse Aussagen (vor allem von Randolf Geist und Maria Colgan) zum Thema der incremental statistics verlinkt habe und glaube, eine halbwegs klare Vorstellung ihrer Rolle zu haben, hier ein Test, mit dem ich diese Vorstellungen überprüfen und präzisieren will.
Das grundsätzliche Problem, das mit den inkrementellen Statistiken angegangen werden soll, ist folgendes: für sehr große partitionierte Tabellen, bei denen in erster Linie neue Daten in neuen Partitionen ergänzt werden (also z.B. Abverkaufsdaten), ist es relativ einfach - und billig - Statistiken für neue Partitionen anzulegen. Sehr teuer ist es aber, die globalen Statistiken zu aktualisieren, da dazu ein Lesen der kompletten Tabelle erforderlich ist. Alternativ kann man die globalen Statistiken auch aus den Partitions-Statistiken aggregieren lassen, aber dabei können die NDV(= number of distinct values)-Werte nicht sinnvoll ermittelt werden. Man kann in solchen Fällen auch globale Statistiken auf der Basis beschränkter Block-Samples anlegen lassen, aber auch das kann natürlich zu massiven Fehlern führen (wenn die Daten eine Clusterung in den Blocks besitzen).
Die in 11.1 eingeführten inkrementellen Statistiken lösen das Problem, indem sie diverse interne Hilfstabellen mit den Daten einer Synopsis füllen, aus der sich dann die NDV-Werte ableiten lassen. Dazu nun der Test (11.1.0.7, Windows XP, ASSM, 8K Blocksize):
Fall 1: initiale Statistikerfassung
-- Anlage einer intervall-partitionierten Testtabelle
drop table test_big_part;
create table test_big_part (
startdate date
, id number
, n_col1 number
, n_col2 number
, n_col3 number
, n_col4 number
, v_col1 varchar2(50)
, v_col2 varchar2(50)
, v_col3 varchar2(50)
, v_col4 varchar2(50)
)
partition by range (startdate)
interval (NUMTODSINTERVAL(1,'DAY'))
(
partition test_p1 values less than (to_date('20120906', 'yyyymmdd'))
);
-- Einfügen von 10M rows mit Attributen, deren Werte sich
-- unterschiedlich häufig wiederholen
insert into test_big_part
with
generator as (
select trunc(sysdate) + mod(rownum, 10) a_date
from dual
connect by level <= 100
)
,
basedata as (
select rownum id
, mod(rownum, 2) n_col1
, mod(rownum, 10) n_col2
, round(rownum/10) n_col3
, dbms_random.value * 1000 n_col4
, lpad('*', 50, '*') v_col1
, dbms_random.string('a', 1) v_col2
, dbms_random.string('a', 50) v_col3
, 'test' v_col4
from dual
connect by level <= 100000)
select generator.a_date
, basedata.*
from generator
, basedata;
commit;
Nun folgt die Statistikerfassung, die jeweils bei aktiviertem SQL_TRACE erfolgt. Zunächst mit der Granularität GLOBAL AND PARTITION (alle anderen Parameter sind Default-Werte, nur die METHOD_OPT wurde explizit gesetzt, um die Erzeugung von Histogrammen zu vermeiden):
begin
DBMS_STATS.GATHER_TABLE_STATS ( OwnName => user
, TabName => 'TEST_BIG_PART'
, Granularity => 'GLOBAL AND PARTITION'
, Method_Opt => 'FOR ALL COLUMNS SIZE 1'
);
end;
/
Die Gesamtlaufzeit des Statistikerfassung beträgt in diesem Fall 54 sec., die sich ziemlich gleich auf das Lesen aller Partitionen und das Lesen der gesamten Tabelle verteilen:
-- Zugriff auf Partitionen
SQL ID: 4mnm957p8yjs6
Plan Hash: 2760598834
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t where TBL$OR$IDX$PART$NUM("TEST"."TEST_BIG_PART",0,
4,0,"ROWID") = :objn /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,
NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 15.45 27.11 222785 223490 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 15.45 27.11 222785 223490 0 10
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=22349 pr=22272 pw=0 time=0 us)
998200 APPROXIMATE NDV AGGREGATE (cr=22349 pr=22272 pw=0 time=0 us cost=6109 size=3539530 card=18245)
1000000 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=22349 pr=22272 pw=0 time=31249 us cost=6109 size=3539530 card=18245)
1000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: KEY KEY (cr=22349 pr=22272 pw=0 time=31249 us cost=6109 size=3539530 card=18245)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 2046 0.07 11.56
db file sequential read 45 0.00 0.02
********************************************************************************
-- Zugriff auf Gesamttabelle
SQL ID: 8zu50wq38a6zk
Plan Hash: 719485973
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,
NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 14.76 26.03 222783 223490 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 14.76 26.03 222783 223490 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=223490 pr=222783 pw=0 time=0 us)
9980200 APPROXIMATE NDV AGGREGATE (cr=223490 pr=222783 pw=0 time=437497 us cost=60962 size=3320566340 card=18244870)
10000000 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=223490 pr=222783 pw=0 time=124995 us cost=60962 size=3320566340 card=18244870)
10000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: 1 1048575 (cr=223490 pr=222783 pw=0 time=124995 us cost=60962 size=3320566340 card=18244870)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 2046 0.05 11.15
db file sequential read 45 0.00 0.01
********************************************************************************
Im Fall der initialen Anlage inkrementeller Statistiken ergeben sich (wie erwartet) andere Operationen:
-- Aktivierung der inkrementellen Statistikerfassung
begin
dbms_stats.set_table_prefs('TEST', 'TEST_BIG_PART', 'INCREMENTAL', 'TRUE');
end;
/
begin
DBMS_STATS.GATHER_TABLE_STATS ( OwnName => user
, TabName => 'TEST_BIG_PART'
, Granularity => 'AUTO'
, Method_Opt => 'FOR ALL COLUMNS SIZE 1'
);
end;
/
In diesem Fall beträgt die Laufzeit 45 sec., aber daraus würde ich nicht zwingend eine notwendige Beschleunigung bei der initialen Statistikerfassung gegenüber dem ersten Fall 'GLOBAL AND PARTITION' ableiten - hier zumindest reduziert sich die Laufzeit um ca. 10 sec. Dabei ergeben sich deutlich andere (und anscheinend kompliziertere) Operationen als im nicht-inkrementellen Fall.
SELECT ... FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ ...
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ ...
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ ...
INSERT INTO SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ ...
SELECT ... FROM SYS.MON_MODS_ALL ...
MERGE ... INTO SYS.MON_MODS_ALL$ ...
SELECT ... FROM SYS.MON_MODS ...
DELETE ... FROM SYS.MON_MODS ...
DELETE ... FROM WRI$_OPTSTAT_SYNOPSIS$ ...
-- Zugriff auf Partitionen
SQL ID: a4sr7dfdnjss5
Plan Hash: 2760598834
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t where TBL$OR$IDX$PART$NUM("TEST"."TEST_BIG_PART",0,
4,0,"ROWID") = :objn /* SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,
NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 15.34 27.53 222785 223490 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 15.34 27.53 222785 223490 0 10
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=22349 pr=22228 pw=0 time=0 us)
998200 APPROXIMATE NDV AGGREGATE (cr=22349 pr=22228 pw=0 time=0 us cost=6087 size=1530000 card=10000)
1000000 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=22349 pr=22228 pw=0 time=0 us cost=6087 size=1530000 card=10000)
1000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: KEY KEY (cr=22349 pr=22228 pw=0 time=0 us cost=6087 size=1530000 card=10000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 2046 0.08 11.83
db file sequential read 45 0.01 0.03
********************************************************************************
UPDATE SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ ...
INSERT INTO SYS.WRI$_OPTSTAT_SYNOPSIS$ ...
INSERT INTO SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY
Demnach erfolgen in diesem Fall relativ komplexe Metadaten-Operationen, die die WRI$_OPTSTAT%-Tabellen auslesen und aktualisieren (wobei recht große Datenmengen entstehen können, wie Randolf Geist in seinem Block ausführt).
Fall 2: Ergänzung einer weiteren Partition
Nun der relevantere Fall: wie verhalten sich die beiden Verfahren nach Ergänzung einer zusätzlichen Tagespartition?
-- Daten für einen weiteren Tag
insert into test_big_part
with
generator as (
select trunc(sysdate) + 10 a_date
from dual
connect by level <= 10
)
,
basedata as (
select rownum id
, mod(rownum, 2) n_col1
, mod(rownum, 10) n_col2
, round(rownum/10) n_col3
, dbms_random.value * 1000 n_col4
, lpad('*', 50, '*') v_col1
, dbms_random.string('a', 1) v_col2
, dbms_random.string('a', 50) v_col3
, 'test' v_col4
from dual
connect by level <= 100000)
select generator.a_date
, basedata.*
from generator
, basedata;
commit;
Für den nicht-inkrementellen Fall ergibt sich für die folgende Statistikerfassung folgendes Bild:
-- Statistikerfassung für neue Partition und global
begin
DBMS_STATS.GATHER_TABLE_STATS (OwnName => user
, TabName => 'TEST_BIG_PART'
, partname => 'SYS_P80'
, Method_Opt => 'FOR ALL COLUMNS SIZE 1'
);
end;
/
Im Trace sieht man nun erwartungsgemäß den Zugriff auf eine Partition und die folgende globale Statistikerfassung:
-- Zugriff auf eine Partition
SQL ID: 4mnm957p8yjs6
Plan Hash: 2760598834
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t where TBL$OR$IDX$PART$NUM("TEST"."TEST_BIG_PART",0,
4,0,"ROWID") = :objn /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,
NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.54 2.34 16865 22349 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.54 2.34 16865 22349 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=22349 pr=16865 pw=0 time=0 us)
998380 APPROXIMATE NDV AGGREGATE (cr=22349 pr=16865 pw=0 time=0 us cost=6087 size=1530000 card=10000)
1000000 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=22349 pr=16865 pw=0 time=0 us cost=6087 size=1530000 card=10000)
1000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: KEY KEY (cr=22349 pr=16865 pw=0 time=0 us cost=6087 size=1530000 card=10000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 161 0.02 0.81
********************************************************************************
-- globaler Zugriff
SQL ID: 8zu50wq38a6zk
Plan Hash: 719485973
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t /* NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,
NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL,NDV,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 16.75 29.05 239658 245839 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 16.75 29.05 239658 245839 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=245839 pr=239658 pw=0 time=0 us)
10979786 APPROXIMATE NDV AGGREGATE (cr=245839 pr=239658 pw=0 time=301646 us cost=60796 size=1530000000 card=10000000)
11000000 PARTITION RANGE ALL PARTITION: 1 1048575 (cr=245839 pr=239658 pw=0 time=239300 us cost=60796 size=1530000000 card=10000000)
11000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: 1 1048575 (cr=245839 pr=239658 pw=0 time=156303 us cost=60796 size=1530000000 card=10000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 2207 0.08 12.48
db file sequential read 45 0.01 0.03
********************************************************************************
Hier gibt's also keine Überraschung: die Statistik für die Partition ist schnell erzeugt, aber die Aktualisierung der globalen Statistiken erfordert wieder das Lesen der kompletten Daten. Nun zu den inkrementellen Statistiken und ihrem Verhalten bei der Ergänzung einer weiteren Partition:
begin
DBMS_STATS.GATHER_TABLE_STATS ( OwnName => user
, TabName => 'TEST_BIG_PART'
, Granularity => 'AUTO'
, Method_Opt => 'FOR ALL COLUMNS SIZE 1'
);
end;
/
In diesem Fall sinkt die Laufzeit von ca. 30 sec. auf ca. 10 sec. Davon entfällt der größte Teil (ca. 7 sec.) auf das Lesen der Partition; zusätzlich gibt es aber wieder diverse lesende und schreibende Zugriffe auf die WRI$_OPTSTAT%-Synposis-Tabellen:
SQL ID: a4sr7dfdnjss5
Plan Hash: 2760598834
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
*/to_char(count("STARTDATE")),to_char(substrb(dump(min("STARTDATE"),16,0,32)
,1,120)),to_char(substrb(dump(max("STARTDATE"),16,0,32),1,120)),
to_char(count("ID")),to_char(substrb(dump(min("ID"),16,0,32),1,120)),
to_char(substrb(dump(max("ID"),16,0,32),1,120)),to_char(count("N_COL1")),
to_char(substrb(dump(min("N_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL1"),16,0,32),1,120)),to_char(count("N_COL2"))
,to_char(substrb(dump(min("N_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL2"),16,0,32),1,120)),to_char(count("N_COL3"))
,to_char(substrb(dump(min("N_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL3"),16,0,32),1,120)),to_char(count("N_COL4"))
,to_char(substrb(dump(min("N_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("N_COL4"),16,0,32),1,120)),to_char(count("V_COL1"))
,to_char(substrb(dump(min("V_COL1"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL1"),16,0,32),1,120)),to_char(count("V_COL2"))
,to_char(substrb(dump(min("V_COL2"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL2"),16,0,32),1,120)),to_char(count("V_COL3"))
,to_char(substrb(dump(min("V_COL3"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL3"),16,0,32),1,120)),to_char(count("V_COL4"))
,to_char(substrb(dump(min("V_COL4"),16,0,32),1,120)),
to_char(substrb(dump(max("V_COL4"),16,0,32),1,120))
from
"TEST"."TEST_BIG_PART" t where TBL$OR$IDX$PART$NUM("TEST"."TEST_BIG_PART",0,
4,0,"ROWID") = :objn /* SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,
NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL,SYN,NIL,NIL*/
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.78 6.69 16141 44674 1 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.78 6.69 16141 44674 1 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=44674 pr=16141 pw=0 time=0 us)
998146 APPROXIMATE NDV AGGREGATE (cr=44674 pr=16141 pw=0 time=110048 us cost=5536 size=15400000 card=100000)
1000000 PARTITION RANGE SINGLE PARTITION: KEY KEY (cr=44674 pr=16141 pw=0 time=31253 us cost=5536 size=15400000 card=100000)
1000000 TABLE ACCESS FULL TEST_BIG_PART PARTITION: KEY KEY (cr=44674 pr=16141 pw=0 time=31253 us cost=5536 size=15400000 card=100000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 268 0.11 4.34
db file sequential read 2 0.03 0.03
db file parallel read 2 0.55 0.61
********************************************************************************
Demnach ist das inkrementelle Verfahren zur Aktualisierung der globalen Statistiken also deutlich schneller als das nicht-inkrementelle - was auch wieder keine große Überraschung ist. Bleibt die Frage nach der Qualität der Ergebnisse, deren Beantwortung ich der table function dbms_stats.diff_table_stats_in_history überlasse:
select * from table(dbms_stats.diff_table_stats_in_history(
ownname => user,
tabname => upper('TEST_BIG_PART'),
time1 => systimestamp,
time2 => to_timestamp('05.09.2012 12:05:00','dd.mm.yyyy hh24:mi:ss'),
pctthreshold => 0));
REPORT
--------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : TEST_BIG_PART
OWNER : TEST
SOURCE A : Statistics as of 05.09.12 12:16:41,613000 +02:00
SOURCE B : Statistics as of 05.09.12 12:05:00,000000 +02:00
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
TEST_BIG_PART T A 11000000 245707 153 11000000
B 11000000 245707 154 11000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
N_COL4 A 200512 ,000004987 NO 0 22 BF055 C20A6 1.1E+07
B 199488 ,000005012 NO 0 22 C0042 C20A6 1.1E+07
V_COL3 A 201744 ,000004956 NO 0 51 41414 7A7A7 1.1E+07
B 202816 ,000004930 NO 0 51 41414 7A7A7 1.1E+07
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
Demnach ergeben sich in diesem Test-Beispiel nur geringfügige Abweichungen zwischen den beiden Verfahren.
Fazit:
- die inkrementellen Statistiken scheinen ziemlich genau das zu leisten, was sie versprechen.
- bei der initialen Anlage von Objektstatistiken (also der kompletten Neuerstellung der Statistiken für eine große partitionierte Tabelle) gibt im Hinblick auf die Laufzeit anscheinend keinen gravierenden Unterschied zwischen dem inkrementellen Verfahren und der vollständigen Erfassung.
- im Fall der Ergänzung neuer Partitionen erfolgt die Aktualisierung der globalen Statistiken über den inkrementellen Mechanismus auf der Basis der Synopsis-Informationen deutlich schneller als bei einem kompletten Neuaufbau.
- natürlich ist die Pflege der Synopsis-Informationen in den WRI$_OPTSTAT%-Tabellen nicht kostenlos: die Aktualisierung der Statistik macht relativ komplexe interne Queries und DML-Operationen erforderlich. Außerdem kann der Speicherplatzbedarf der Synopsis signifikant sein (wie Randolf Geist gezeigt hat).
- die Ergebnisse der inkrementellen und der nicht-inkrementellen Statistikerzeugung sind im Test nahezu identisch.
Abonnieren
Posts (Atom)