Samstag, März 27, 2010
Fast Refresh-Optimierung für MVs
In Alberto Dell'Eras Blog findet sich mal wieder eine interessante Untersuchung zum Fast Refresh für Materialized Views: offenbar sind da diverse Bugs im Spiel, die man durch zwei unterschiedliche Workarounds korrigieren kann. Falls ich mich wieder mal mit MVs beschäftigen sollte, wäre es gut, sich daran zu erinnern...
Freitag, März 26, 2010
Analytics
Analytics sind mir zuerst bei Tom Kyte in seinen Antworten auf AskTom begegnet, und er hat mich dort schnell überzeugt, dass sie das Tollste seit Erfindung des geschnittenen Brots sind. Mich überrascht aber immer wieder, wie wenig sich analytische Funktionen in der Zwischenzeit herumgesprochen haben, obwohl sie eigentlich nicht mehr ganz neu sind. Deshalb hier noch mal eine kurze Erläuterung (bei der ich wieder so tue, als hätte ich hier Leser): der grundsätzliche Unterschied zwischen den Analytics und einer GROUP BY-Operation ist, dass durch ein GROUP BY die Anzahl der Ergebnissätze reduziert wird, während die Analytics alle Sätze der ursprünglichen Query erhalten und um gruppierte Ergebnisse ergänzen. Dazu ein harmloses Beispiel mit der allseits beliebten EMP-Tabelle, aus der ich mir zu jedem Mitarbeiter den Job und die Gesamtzahl der Mitarbeiter mit diesem Job anzeigen lasse:
Man kann das gleiche Ergebnis auch dadurch erreichen, dass man EMP mit einer gruppierenden Unterabfrage joint:
Da ist die analytische Variante auf jeden Fall kompakter; ob sie verständlicher ist, hängt sicher vom Standpunkt des Betrachters ab. Zumindest aber ist sie in der Regel effizienter als die traditionelle Lösung:
Für dieses harmlose Beispiel benötigt die analytische Query nur 7 statt 14 Blockzugriffen, allerdings sind zwei statt einer Sortieroperation erforderlich. Auf die Details will ich jetzt nicht weiter eingehen (weil ich sie noch mal nachlesen müsste...), aber in der Regel sind die Analytics ein extrem nützliches Werkzeug, das die Möglichkeiten von SQL deutlich erweitert.
-- zunächst die analytische Variante select ENAME , job , count(*) over (partition by job) job_count from emp order by ename; ENAME JOB JOB_COUNT ---------- --------- ---------- ADAMS CLERK 4 ALLEN SALESMAN 4 BLAKE MANAGER 3 CLARK MANAGER 3 FORD ANALYST 2 JAMES CLERK 4 JONES MANAGER 3 KING PRESIDENT 1 MARTIN SALESMAN 4 MILLER CLERK 4 SCOTT ANALYST 2 SMITH CLERK 4 TURNER SALESMAN 4 WARD SALESMAN 4 14 Zeilen ausgewählt.
Man kann das gleiche Ergebnis auch dadurch erreichen, dass man EMP mit einer gruppierenden Unterabfrage joint:
select e1.ename , e1.job , e2.job_count from emp e1 , (select JOB , count(*) job_count from emp group by job) e2 where e1.job = e2.job order by e1.ename; ENAME JOB JOB_COUNT ---------- --------- ---------- ADAMS CLERK 4 ALLEN SALESMAN 4 BLAKE MANAGER 3 CLARK MANAGER 3 FORD ANALYST 2 JAMES CLERK 4 JONES MANAGER 3 KING PRESIDENT 1 MARTIN SALESMAN 4 MILLER CLERK 4 SCOTT ANALYST 2 SMITH CLERK 4 TURNER SALESMAN 4 WARD SALESMAN 4 14 Zeilen ausgewählt.
Da ist die analytische Variante auf jeden Fall kompakter; ob sie verständlicher ist, hängt sicher vom Standpunkt des Betrachters ab. Zumindest aber ist sie in der Regel effizienter als die traditionelle Lösung:
-- der Plan für die analytische Variante
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 6 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 182 | 6 (34)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 182 | 6 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistiken
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
831 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
-- der Plan der klassischen Variante
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39 | 1248 | 11 (28)| 00:00:01 |
| 1 | SORT ORDER BY | | 39 | 1248 | 11 (28)| 00:00:01 |
|* 2 | HASH JOIN | | 39 | 1248 | 10 (20)| 00:00:01 |
| 3 | VIEW | | 14 | 266 | 5 (20)| 00:00:01 |
| 4 | HASH GROUP BY | | 14 | 84 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 84 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 182 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E1"."JOB"="E2"."JOB")
Note
-----
- dynamic sampling used for this statement
Statistiken
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
831 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed Für dieses harmlose Beispiel benötigt die analytische Query nur 7 statt 14 Blockzugriffen, allerdings sind zwei statt einer Sortieroperation erforderlich. Auf die Details will ich jetzt nicht weiter eingehen (weil ich sie noch mal nachlesen müsste...), aber in der Regel sind die Analytics ein extrem nützliches Werkzeug, das die Möglichkeiten von SQL deutlich erweitert.
Mittwoch, März 24, 2010
Speicherbedarf für Data Warehouses
Vincent Rainardi liefert in seinem lesenswerten Blog ein paar Erklärungen dafür, dass DWHs in der Regel erstaunlich große Anforderungen an die verfügbaren Plattenkapazitäten stellen (Indizes, Materialized Views, Snapshots von Stage Daten, Backups etc.). Ich bringe an dieser Stelle gerne das Bild vom Eisberg, von dem nur 10% aus dem Wasser ragen: 10% relevante Daten und 90% Hilfsobjekte zur Performanceoptimierung. Vincent Rainardi schreibt übrigens in der Regel über MS SQL, aber in DWH-Zusammenhänge sind die Konzepte der großen RDBMS relativ ähnlich.
Dienstag, März 23, 2010
WORKAREA_SIZE_POLICY - Teil 5
Nach dem wilden Experimentieren und freien Assoziieren jetzt zurück zu den Autoritäten: was sagt Jonathan Lewis in seinem Cost-Based Oracle Fundamentals-Buch zum Thema der Sortierungen? Das passende Kapitel ist Nr 13: Sorting and Merge Joins. Hier eine kurze Liste dort erläuterter Details (ohne Anspruch auf Vollständigkeit):
- beim Sortieren gibt es drei unterschiedlich effektive Varianten:
- optimal: erfolgt vollständig im Arbeitsspeicher. Dabei erfolgt die Allocation des Speichers nach Bedarf (es wird also nicht initial der komplette Speicher verwendet, der duch die SORT_AREA_SIZE definiert ist). Für 6 MB Daten benötigt Lewis in seinem Test einen Speicher von 25,5 MB, um die Sortierung optimal zu halten; ob dieses Verhältnis der Normalfall ist, wäre zu testen.
- onepass: erfolgt, wenn die Daten nicht komplett im Speicher sortiert werden können, aber es möglich ist, kleinere Teilmengen zu sortieren, die Ergebnisse auf der Platte abzulegen, und anschließend Stücke von allen vorsortierten Mengen im Speicher zusammenzuführen
- multipass: verhält sich wie die onepass-Variante, aber es gibt zu viele vorsortierte Mengen, so dass sie nicht in einem Schritt zusammengeführt werden können. Stattdessen werden größere Zwischenergebnisse zusammengeführt wieder auf der Platte abgelegt und diese dann wieder kombiniert.
- zur Analyse der Effekte nutzt JL die gleichen Werkzeuge, die ich auch bei meinen Tests eingesetzt hatte (wobei er aber kompetenter wirkt...): v$sesstat (oder auch v$mystat für die eigene Session), sowie die Trace Events 10032 und 10033 (außerdem schaut er sich auch noch Block Dumps der temp files an).
- Zum Sortieralgorithmus entwickelt Lewis die Theorie, dass intern ein (balancierter) binary insertion tree im Spiel sein könnte, und liefert Indizien, die für diese Annahme sprechen; seit 10.2 könnte aber auch ein neuer Mechanismus im Spiel sein, so dass ich mir die Details spare.
- Sortierungen mit größerer Speichernutzung benötigen auch größere CPU-Ressourcen, so dass für Systeme, in denen I/O ein kleineres Problem als die CPU-Nutzung ist, auch eine Verkleinerung der Speichernutzung zu einer Beschleunigung führen kann.
- Kapitel 13 ist ziemlich umfangreich, so dass ich zu den Erläuterungen zur WORKAREA_SIZE_POLICY ein andermal kommen werde.
Freitag, März 19, 2010
WORKAREA_SIZE_POLICY - Teil 4
And now ... The Punchline!
In Fortsetzung der hier, hier und hier aufgeführten Beobachtungen jetzt zur Auflösung: warum wird eine CTAS-Operation langsamer, wenn sie größere Ressourcen verwenden kann? Die Antwort ist, wenn ich jetzt noch mal darüber nachdenke, recht offensichtlich: weil die Operation die Ressourcen nicht verwendet.
Zwar zeigt v$sesstat die Verwendung der zugeteilten PGA-Ressourcen an, aber offenbar lügt Oracle da ziemlich dreist. Irgendetwas hatte mich in den 10032er Traces irritiert, aber ich konnte es nicht genau greifen, obwohl es ziemlich offensichtlich ist. Zunächst die Angaben im Trace für die Ausführung mit dem automatischem PGA-Management:
Und zum Vergleich die Angaben für die manuell vergrößerte SORT_AREA_SIZE:
Dass der sort_multiblock_read_count auf 1 gesenkt war, hatte ich registriert, nicht aber die Werte für die SORT_AREA_SIZE, die ich ja explizit auf einen sehr hohen Wert gesetzt hatte. Eine Erklärung für das Verhalten habe ich dann schließlich an unerwarteter Stelle gefunden: http://martinpreiss.blogspot.com/2008/12/manuelle-einstellung-der-sortareasize.html. In einem dort verlinkten Blog-Eintrag erläutert Jonathan Lewis, dass in 10.2.0.4 die Einstellung der SORT_AREA_SIZE nicht wirksam wird, obwohl das System behauptet, sie wäre aktiv. So sieht man in v$ses_optimizer_env den erhöhten Parameter-Wert, aber verwendet wird er deshalb noch lange nicht. Der vorgeschlagene Workaround für diesen Bug wirkt eher bizarr: man muss das ALTER SESSION-Kommando zwei Mal absetzen, dann ist die Datenbank überzeugt, dass man es tatsächlich ernst meint damit. Mehrmaliges einbeiniges Hüpfen um den Server ist anscheinend nicht erforderlich.
Nach Verwendung des - gut, ich nenn es weiterhin so - Workarounds wird die vergrößerte SORT_AREA_SIZE dann tatsächlich wirksam und die Laufzeit der Operation sinkt auf einen Wert knapp über einer Minute.
In Fortsetzung der hier, hier und hier aufgeführten Beobachtungen jetzt zur Auflösung: warum wird eine CTAS-Operation langsamer, wenn sie größere Ressourcen verwenden kann? Die Antwort ist, wenn ich jetzt noch mal darüber nachdenke, recht offensichtlich: weil die Operation die Ressourcen nicht verwendet.
Zwar zeigt v$sesstat die Verwendung der zugeteilten PGA-Ressourcen an, aber offenbar lügt Oracle da ziemlich dreist. Irgendetwas hatte mich in den 10032er Traces irritiert, aber ich konnte es nicht genau greifen, obwohl es ziemlich offensichtlich ist. Zunächst die Angaben im Trace für die Ausführung mit dem automatischem PGA-Management:
---- Sort Parameters ------------------------------ sort_area_size 22609920 sort_area_retained_size 22609920 sort_multiblock_read_count 15 max intermediate merge width 44
Und zum Vergleich die Angaben für die manuell vergrößerte SORT_AREA_SIZE:
---- Sort Parameters ------------------------------ sort_area_size 98304 sort_area_retained_size 65536 sort_multiblock_read_count 1 max intermediate merge width 2
Dass der sort_multiblock_read_count auf 1 gesenkt war, hatte ich registriert, nicht aber die Werte für die SORT_AREA_SIZE, die ich ja explizit auf einen sehr hohen Wert gesetzt hatte. Eine Erklärung für das Verhalten habe ich dann schließlich an unerwarteter Stelle gefunden: http://martinpreiss.blogspot.com/2008/12/manuelle-einstellung-der-sortareasize.html. In einem dort verlinkten Blog-Eintrag erläutert Jonathan Lewis, dass in 10.2.0.4 die Einstellung der SORT_AREA_SIZE nicht wirksam wird, obwohl das System behauptet, sie wäre aktiv. So sieht man in v$ses_optimizer_env den erhöhten Parameter-Wert, aber verwendet wird er deshalb noch lange nicht. Der vorgeschlagene Workaround für diesen Bug wirkt eher bizarr: man muss das ALTER SESSION-Kommando zwei Mal absetzen, dann ist die Datenbank überzeugt, dass man es tatsächlich ernst meint damit. Mehrmaliges einbeiniges Hüpfen um den Server ist anscheinend nicht erforderlich.
Nach Verwendung des - gut, ich nenn es weiterhin so - Workarounds wird die vergrößerte SORT_AREA_SIZE dann tatsächlich wirksam und die Laufzeit der Operation sinkt auf einen Wert knapp über einer Minute.
WORKAREA_SIZE_POLICY - Teil 3
Anschließend an die Beobachtungen von gestern kann ich noch ein paar seltsame Effekte ergänzen - aber leider nicht viele Erklärungen dafür..
Noch nicht erwähnt hatte ich die vielleicht wichtigste Beobachtung - nämlich, dass Oracle mit der WORKAREA_SIZE_POLICY=AUTO das fragliche CREATE TABLE Statement schnell durchführt (Laufzeit < 2 min). In gewisser Weise wird die gesamte Fragestellung dadurch rein akademisch, da ja alles funktioniert, wenn man der DB nicht ins Handwerk pfuscht. Trotzdem wüsste ich gerne, wieso das System sich so verhält.
Ebenfalls noch nicht erwähnt war das Verhalten unter Oracle 11.1.0.7 (Instanz auf einem Linux-Server ohne ermittelte Systemstatistiken): dort spielt die Einstellung offenbar keine entscheidende Rolle. Mit der AUTO-Einstellung läuft die CTAS-Operation in 45 sec durch und die gleiche Laufzeit ergibt sich auch bei manueller Einstellung der SORT_AREA_SIZE und HASH_AREA_SIZE auf 100 bzw. 300 MB. In den 10046er Traces sieht man jeweils die größeren block cnt-Angaben (15). Das Problem scheint für Oracle 11 also gar keine Rolle zu spielen (wobei sich die insgesamt bessere Performance vermutlich auch den schnelleren Platten des Servers verdankt).
Bizarr ist allerdings, dass heute bei der Wiederholung der Tests auf dem Oracle 10 - System die manuelle Einstellung der PGA-Parameter auch für den Fall der 50 MB zu den langen Laufzeiten führt (nur AUTO liefert jetzt noch die schnelle Laufzeit). Was sich gegenüber dem gestrigen Versuch geändert haben könnte, ist mir im Moment noch unklar.
Noch nicht erwähnt hatte ich die vielleicht wichtigste Beobachtung - nämlich, dass Oracle mit der WORKAREA_SIZE_POLICY=AUTO das fragliche CREATE TABLE Statement schnell durchführt (Laufzeit < 2 min). In gewisser Weise wird die gesamte Fragestellung dadurch rein akademisch, da ja alles funktioniert, wenn man der DB nicht ins Handwerk pfuscht. Trotzdem wüsste ich gerne, wieso das System sich so verhält.
Ebenfalls noch nicht erwähnt war das Verhalten unter Oracle 11.1.0.7 (Instanz auf einem Linux-Server ohne ermittelte Systemstatistiken): dort spielt die Einstellung offenbar keine entscheidende Rolle. Mit der AUTO-Einstellung läuft die CTAS-Operation in 45 sec durch und die gleiche Laufzeit ergibt sich auch bei manueller Einstellung der SORT_AREA_SIZE und HASH_AREA_SIZE auf 100 bzw. 300 MB. In den 10046er Traces sieht man jeweils die größeren block cnt-Angaben (15). Das Problem scheint für Oracle 11 also gar keine Rolle zu spielen (wobei sich die insgesamt bessere Performance vermutlich auch den schnelleren Platten des Servers verdankt).
Bizarr ist allerdings, dass heute bei der Wiederholung der Tests auf dem Oracle 10 - System die manuelle Einstellung der PGA-Parameter auch für den Fall der 50 MB zu den langen Laufzeiten führt (nur AUTO liefert jetzt noch die schnelle Laufzeit). Was sich gegenüber dem gestrigen Versuch geändert haben könnte, ist mir im Moment noch unklar.
Donnerstag, März 18, 2010
WORKAREA_SIZE_POLICY - Teil 2
Ausgehend von den hier beschriebenen Beobachtungen habe ich jetzt ein wenig genauer auf die Inhalte meiner Trace-Dateien geschaut - und dabei erst einmal auf das 10046er Trace, das für mich weniger exotisch ist als das 10032er Trace für die Sortierung.
Event 10046
Im 10046er Trace für den Fall mit der auf 50 MB dimensionierten SORT- bzw. HASH_AREA_SIZE sehe ich zunächst 166 db file scattered read Events, die in der Regel 64 Blocks umfassen (bei 10240 Blocks in der Tabelle ergibt sich: 10240/64 = 160) - die Laufzeit dieser Events beträgt ca. 4 sec. Anschließend folgen dann mehr als 2.000 direct path read temp Events mit einer Laufzeit von insgesamt etwa 40 sec. Für diese Events ist der block cnt jeweils 15.
Für den Fall mit 300MB liefert das Trace für die db file scattered read Events nahezu die gleichen Werte. Für die direct path read temp Events aber vergrößert sich die Anzahl von etwa 2.000 auf über 200.000 und die Laufzeit steigt von 40 sec auf mehr als 10 min. Überraschend ist dabei auch, dass der block cnt in diesen Fällen jeweils 1 ist, was auf jeden Fall eine Erhöhung der Anzahl der Events hervorrufen muss, allerdings nicht unbedingt um den Faktor 100. Interessant ist aber natürlich vor allem die Frage, wieso die Zugriffe auf Einzelblocks erfolgen.
Event 10032
Jonathan Lewis verwendet dieses Event gelegentlich und weist auch in einem OTN-Thread mit dem vielversprechenden Titel Single block read for Sort darauf hin, aber in meinem Fall bringt es nicht viel, da seine Aktivierung offenbar dazu führt, dass der block cnt für den 50MB-Fall auf 1 sinkt und die Laufzeit und das Verhalten dem 300MB-Fall entsprechen. Im Trace erscheint die Angabe sort_multiblock_read_count = 1, aber ein explizites Setzen des Parameters _sort_multiblock_read_count auf einen anderen Wert ändert das Verhalten nicht. (Bei Steve Adams findet man eine uralte Erläuterung (Dezember 2000; das Internet hat ein erstaunliches Gedächtnis) zum sort_multiblock_read_count, die vermutlich sogar noch zutreffend ist, aber im aktuellen Fall auch nicht weiter hilft; im angesprochenen OTN-Thread erklärt Timur Akhmadeev, dass das Setzen des hidden parameters in seinen Tests ebenfalls keinen Effekt hatte; auch die im Thread genannte Option, die Parameter _smm_auto_max_io_size und _smm_auto_min_io_size zu setzen, die im MetaLink Dokument 330818.1 beschrieben wird, brachte keine Änderung des Verhaltens, wobei das insofern nicht überrascht, als diese Parameter wohl nur den Fall der automatischen PGA-Verwaltung betreffen).
Für Event 10033 ergibt sich der gleiche Effekt wie für 10032: die Aktivierung des Traces ändert das Verhalten (Prof. Heisenberg, bitte übernehmen Sie...).
In der v$ses_optimizer_env sehe ich übrigens außer den explizit gesetzten Parametern keine Unterschiede für beide Fälle (also spielen abgeleitete Parameter vermutlich keine Rolle).
Das ist jetzt auch noch nicht unbedingt ein befriedigendes Ergebnis, aber für den Moment genügt es mir.
Event 10046
Im 10046er Trace für den Fall mit der auf 50 MB dimensionierten SORT- bzw. HASH_AREA_SIZE sehe ich zunächst 166 db file scattered read Events, die in der Regel 64 Blocks umfassen (bei 10240 Blocks in der Tabelle ergibt sich: 10240/64 = 160) - die Laufzeit dieser Events beträgt ca. 4 sec. Anschließend folgen dann mehr als 2.000 direct path read temp Events mit einer Laufzeit von insgesamt etwa 40 sec. Für diese Events ist der block cnt jeweils 15.
Für den Fall mit 300MB liefert das Trace für die db file scattered read Events nahezu die gleichen Werte. Für die direct path read temp Events aber vergrößert sich die Anzahl von etwa 2.000 auf über 200.000 und die Laufzeit steigt von 40 sec auf mehr als 10 min. Überraschend ist dabei auch, dass der block cnt in diesen Fällen jeweils 1 ist, was auf jeden Fall eine Erhöhung der Anzahl der Events hervorrufen muss, allerdings nicht unbedingt um den Faktor 100. Interessant ist aber natürlich vor allem die Frage, wieso die Zugriffe auf Einzelblocks erfolgen.
Event 10032
Jonathan Lewis verwendet dieses Event gelegentlich und weist auch in einem OTN-Thread mit dem vielversprechenden Titel Single block read for Sort darauf hin, aber in meinem Fall bringt es nicht viel, da seine Aktivierung offenbar dazu führt, dass der block cnt für den 50MB-Fall auf 1 sinkt und die Laufzeit und das Verhalten dem 300MB-Fall entsprechen. Im Trace erscheint die Angabe sort_multiblock_read_count = 1, aber ein explizites Setzen des Parameters _sort_multiblock_read_count auf einen anderen Wert ändert das Verhalten nicht. (Bei Steve Adams findet man eine uralte Erläuterung (Dezember 2000; das Internet hat ein erstaunliches Gedächtnis) zum sort_multiblock_read_count, die vermutlich sogar noch zutreffend ist, aber im aktuellen Fall auch nicht weiter hilft; im angesprochenen OTN-Thread erklärt Timur Akhmadeev, dass das Setzen des hidden parameters in seinen Tests ebenfalls keinen Effekt hatte; auch die im Thread genannte Option, die Parameter _smm_auto_max_io_size und _smm_auto_min_io_size zu setzen, die im MetaLink Dokument 330818.1 beschrieben wird, brachte keine Änderung des Verhaltens, wobei das insofern nicht überrascht, als diese Parameter wohl nur den Fall der automatischen PGA-Verwaltung betreffen).
Für Event 10033 ergibt sich der gleiche Effekt wie für 10032: die Aktivierung des Traces ändert das Verhalten (Prof. Heisenberg, bitte übernehmen Sie...).
In der v$ses_optimizer_env sehe ich übrigens außer den explizit gesetzten Parametern keine Unterschiede für beide Fälle (also spielen abgeleitete Parameter vermutlich keine Rolle).
Das ist jetzt auch noch nicht unbedingt ein befriedigendes Ergebnis, aber für den Moment genügt es mir.
WORKAREA_SIZE_POLICY - Teil 1
Der Initialisierungsparameter WORKAREA_SIZE_POLICY dient dazu, zu bestimmen, wer für die Verteilung von PGA-Ressourcen (Process Global Area mit den Daten, die den einzelnen Prozessen zugeordnet sind) zuständig ist: AUTO überlässt die Verwaltung dem System, das die vorhandenen Ressourcen abhängig von der aktuellen Workload zuteilt (wobei die einzelne Operation meiner Erinnerung nach nicht mehr als 5% des über den Parameter PGA_AGGREGATE_TARGET definierten verfügbaren Speichers erhalten sollte (*) ). Mit der Einstellung MANUAL übernimmt der DBA die Kontrolle und kann dann die Ressourcenzuteilung über die Parameter SORT_AREA_SIZE und HASH_AREA_SIZE bestimmen. Üblicherweise funktioniert die automatische Zuteilung recht gut, aber für größere Batchoperationen mit großen Speicheranforderungen kann es sinnvoll sein, die manuelle Kontrolle zu wählen. So weit die Theorie, oder das, was ich mir davon gemerkt habe.
Deshalb hat mich das Ergebnis folgenden Szenarios überrascht (Instanz 10.2.0.4 auf einem Windows 2003er Server; Systemstatistiken wurden ermittelt): ich wollte für eine Tabellenpartition mit ca. 3.000.000 Sätzen und einer Größe von etwa 170 MB eine Aggregation anlegen und setzte dazu die Parameter SORT_AREA_SIZE und HASH_AREA_SIZE jeweils auf 300 MB:
Die abgesetzte Query besitzt einen relativ harmlosen Ausführungsplan:
Für mich überraschend war allerdings die lange Laufzeit von ca. 13 min - dass Oracles Prognose von 7 sec recht optimistisch war, konnte man absehen, aber 13 min sind doch ziemlich viel für die Datenmenge.
Für einen zweiten Versuch setzte ich die beiden %_AREA_SIZE-Paramter auf jeweils 50MB, und erhielt einen nahezu identischen Ausführungsplan:
Erwartungsgemäß erscheint die TempSpc-Angabe, weil die Sortierung jetzt nicht mehr komplett im Memory erfolgen kann, aber davon abgesehen sind die Pläne ziemlich identisch (wobei die Kardinalitäten und Größenangaben durchaus plausibel wirken). Unerwartet ist aber, dass die tatsächliche Laufzeit bei der kleineren Speicherzuweisung von 13 min auf 1:30 min sinkt. Um den Fall klarer fassen zu können, schaute ich mir die Deltas für die Statitisken in v$sesstat an, und beobachtete folgende Unterschiede:
Demnach scheint die Variante mit den 300MB den verfügbaren Speicher tatsächlich zu nutzen, dabei aber deutlich größere Leseoperationen durchzuführen und vor allem direct path Lese- und Schreiboperationen hervorzurufen. Der betroffene Rechner war während der Tests nicht unter Last, so dass Swapping und Paging als Erklärung ausscheiden.
Ich habe außerdem noch ein 10032er Trace durchgeführt, um die Sortierungsoperationen genauer analysieren zu können, aber dazu (vielleicht) später mehr.
(*) Nachtrag 16.02.2011: inzwischen weiß ich, dass die 5% für Version 10.2 (und folgende) nicht mehr gelten. Details dazu findet man hier.
Deshalb hat mich das Ergebnis folgenden Szenarios überrascht (Instanz 10.2.0.4 auf einem Windows 2003er Server; Systemstatistiken wurden ermittelt): ich wollte für eine Tabellenpartition mit ca. 3.000.000 Sätzen und einer Größe von etwa 170 MB eine Aggregation anlegen und setzte dazu die Parameter SORT_AREA_SIZE und HASH_AREA_SIZE jeweils auf 300 MB:
alter session set WORKAREA_SIZE_POLICY=Manual; alter session set sort_area_size = 300000000; alter session set hash_area_size = 300000000;
Die abgesetzte Query besitzt einen relativ harmlosen Ausführungsplan:
-------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | 1483 (100)| | | | | 1 | LOAD AS SELECT | | | | | | | | | 2 | SORT GROUP BY | | 3029K| 395M| 883 (54)| 00:00:07 | | | | 3 | PARTITION RANGE SINGLE| | 3029K| 395M| 883 (54)| 00:00:07 | 1 | 1 | | 4 | VIEW | | 3029K| 395M| 883 (54)| 00:00:07 | | | | 5 | HASH GROUP BY | | 3029K| 130M| 678 (71)| 00:00:06 | | | |* 6 | TABLE ACCESS FULL | FACT_TABLE_xxxxxx | 3029K| 130M| 351 (43)| 00:00:03 | 1 | 1 | --------------------------------------------------------------------------------------------------------------
Für mich überraschend war allerdings die lange Laufzeit von ca. 13 min - dass Oracles Prognose von 7 sec recht optimistisch war, konnte man absehen, aber 13 min sind doch ziemlich viel für die Datenmenge.
Für einen zweiten Versuch setzte ich die beiden %_AREA_SIZE-Paramter auf jeweils 50MB, und erhielt einen nahezu identischen Ausführungsplan:
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | | | | 13112 (100)| | | | | 1 | LOAD AS SELECT | | | | | | | | | | 2 | SORT GROUP BY | | 3029K| 395M| | 12513 (5)| 00:01:38 | | | | 3 | PARTITION RANGE SINGLE| | 3029K| 395M| | 12513 (5)| 00:01:38 | 1 | 1 | | 4 | VIEW | | 3029K| 395M| | 12513 (5)| 00:01:38 | | | | 5 | HASH GROUP BY | | 3029K| 130M| 394M| 12308 (5)| 00:01:36 | | | |* 6 | TABLE ACCESS FULL | FACT_TABLE_xxxxxx | 3029K| 130M| | 351 (43)| 00:00:03 | 1 | 1 | ----------------------------------------------------------------------------------------------------------------------
Erwartungsgemäß erscheint die TempSpc-Angabe, weil die Sortierung jetzt nicht mehr komplett im Memory erfolgen kann, aber davon abgesehen sind die Pläne ziemlich identisch (wobei die Kardinalitäten und Größenangaben durchaus plausibel wirken). Unerwartet ist aber, dass die tatsächliche Laufzeit bei der kleineren Speicherzuweisung von 13 min auf 1:30 min sinkt. Um den Fall klarer fassen zu können, schaute ich mir die Deltas für die Statitisken in v$sesstat an, und beobachtete folgende Unterschiede:
Fall 1 (50MB) Fall 2 (300MB) session uga memory max 15 MB 300 MB session pga memory max 42 MB 300 MB physical read total bytes 800 MB 5700 MB physical write total bytes 1000 MB 6000 MB physical reads direct temporary tablespace 35.767 340.613 physical writes direct temporary tablespace 35.767 340.613 sorts (disk) 1 1 sorts (rows) 9.088.155 9.088.695
Demnach scheint die Variante mit den 300MB den verfügbaren Speicher tatsächlich zu nutzen, dabei aber deutlich größere Leseoperationen durchzuführen und vor allem direct path Lese- und Schreiboperationen hervorzurufen. Der betroffene Rechner war während der Tests nicht unter Last, so dass Swapping und Paging als Erklärung ausscheiden.
Ich habe außerdem noch ein 10032er Trace durchgeführt, um die Sortierungsoperationen genauer analysieren zu können, aber dazu (vielleicht) später mehr.
(*) Nachtrag 16.02.2011: inzwischen weiß ich, dass die 5% für Version 10.2 (und folgende) nicht mehr gelten. Details dazu findet man hier.
Mittwoch, März 03, 2010
Bitmap Indizes - Teil 6
Noch mal zurück zu den Bitmap Indizes: einmal mehr hat Richard Foote dazu eine sehr erhellende Erläuterung geliefert, in der er das Verhalten von Bitmap und B*Tree-Indizes untersucht und noch mal darauf hinweist, dass nicht die Anzahl der distinkten Sätze, sondern die Clusterung der Werte in der Tabelle über die Qualität der Indizes (in diesem Fall beider Typen) entscheidet.
Etwas extrem kommt mir der sbschließende Hinweis vor: "Let me repeat: There is no limit to the number of distinct values in a column for it to be considered for a Bitmap index." Wären demnach auch eindeutige Spalten Kandidaten für Bitmap-Indizes. Dazu ein kurzer Versuch:
Die B*Tree-Indizes haben jeweils einen guten Clustering-Factor, sind aus Sicht des cbo also günstig. Für den Extremfall eines eindeutigen Wertes für col1 ist der Bitmap Index größer als der entsprechende B*Tree-Index (1718 zu 1099 Leaf-Blocks), aber schon für col2 (mit 100.000 distinkten Werten unter 1.000.000 Sätzen) ist der Bitmap Index deutlich kompakter (187 zu 1093 Leaf-Blocks).
Jetzt der Versuch mit einer ungeordneten Tabelle:
Erwartungsgemäß ändert sich die Größe des Bitmap Index für die eindeutige Spalte nicht, da für jeden Wert ein Bitmap aus sehr vielen Nullen und einer 1 erzeugt wird (0000001000000...). Für die B*Tree-Indizes ändert sich die Größe ebenfalls nicht (was ebenfalls zu erwarten war), aber der Clustering-Factor steigt extrem (und nähert sich der Anzahl der Tabellenzeilen). Deutlich größer wird der zweite Bitmap-Index, der aber immer noch sehr viel kleiner ist als der entsprechende B*Tree-Index.
Etwas extrem kommt mir der sbschließende Hinweis vor: "Let me repeat: There is no limit to the number of distinct values in a column for it to be considered for a Bitmap index." Wären demnach auch eindeutige Spalten Kandidaten für Bitmap-Indizes. Dazu ein kurzer Versuch:
SQL> create table test1 2 as 3 select rownum col1 4 , trunc(rownum/10) col2 5 , trunc(rownum/100) col3 6 , trunc(rownum/1000) col4 7 , trunc(rownum/10000) col5 8 , trunc(rownum/100000) col6 9 from dual 10 connect by level <= 1000000 11 order by col1; Tabelle wurde erstellt. create bitmap index test1_bidx1 on test1(col1); create bitmap index test1_bidx2 on test1(col2); SQL> r 1 select index_name 2 , index_type 3 , distinct_keys 4 , clustering_factor 5 , blevel 6 , leaf_blocks 7 from user_indexes 8 where table_name = 'TEST1' 9* order by 1 INDEX_NAME INDEX_TYPE DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS ------------ ------------ ------------- ----------------- ---------- ----------- TEST1_BIDX1 BITMAP 1000000 1000000 2 1718 TEST1_BIDX2 BITMAP 100001 100001 1 187 drop index TEST1_BIDX1; drop index TEST1_BIDX2; create index test1_idx1 on test1(col1); create index test1_idx2 on test1(col2); SQL> select index_name 2 , index_type 3 , distinct_keys 4 , clustering_factor 5 , blevel 6 , leaf_blocks 7 from user_indexes 8 where table_name = 'TEST1' 9 order by 1; INDEX_NAME INDEX_TYPE DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS ------------ ------------ ------------- ----------------- ---------- ----------- TEST1_IDX1 NORMAL 1000000 1962 1 1099 TEST1_IDX2 NORMAL 100001 1966 2 1093
Die B*Tree-Indizes haben jeweils einen guten Clustering-Factor, sind aus Sicht des cbo also günstig. Für den Extremfall eines eindeutigen Wertes für col1 ist der Bitmap Index größer als der entsprechende B*Tree-Index (1718 zu 1099 Leaf-Blocks), aber schon für col2 (mit 100.000 distinkten Werten unter 1.000.000 Sätzen) ist der Bitmap Index deutlich kompakter (187 zu 1093 Leaf-Blocks).
Jetzt der Versuch mit einer ungeordneten Tabelle:
create table test3 as select * from test1 order by dbms_random.value; create bitmap index test3_bidx1 on test3(col1); create bitmap index test3_bidx2 on test3(col2); SQL> r 1 select index_name 2 , index_type 3 , distinct_keys 4 , clustering_factor 5 , blevel 6 , leaf_blocks 7 from user_indexes 8 where table_name = 'TEST3' 9* order by 1 INDEX_NAME INDEX_TYPE DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS ------------ ------------ ------------- ----------------- ---------- ----------- TEST3_BIDX1 BITMAP 1000000 1000000 2 1718 TEST3_BIDX2 BITMAP 100001 100001 1 397 drop index TEST3_BIDX1; drop index TEST3_BIDX2; create index test3_idx1 on test3(col1); create index test3_idx2 on test3(col2); SQL> select index_name 2 , index_type 3 , distinct_keys 4 , clustering_factor 5 , blevel 6 , leaf_blocks 7 from user_indexes 8 where table_name = 'TEST3' 9 order by 1; INDEX_NAME INDEX_TYPE DISTINCT_KEYS CLUSTERING_FACTOR BLEVEL LEAF_BLOCKS ------------ ------------ ------------- ----------------- ---------- ----------- TEST3_IDX1 NORMAL 1000000 999456 1 1099 TEST3_IDX2 NORMAL 100001 997675 2 1093
Erwartungsgemäß ändert sich die Größe des Bitmap Index für die eindeutige Spalte nicht, da für jeden Wert ein Bitmap aus sehr vielen Nullen und einer 1 erzeugt wird (0000001000000...). Für die B*Tree-Indizes ändert sich die Größe ebenfalls nicht (was ebenfalls zu erwarten war), aber der Clustering-Factor steigt extrem (und nähert sich der Anzahl der Tabellenzeilen). Deutlich größer wird der zweite Bitmap-Index, der aber immer noch sehr viel kleiner ist als der entsprechende B*Tree-Index.
Montag, März 01, 2010
DBMS_XPLAN Format Parameter
Charles Hooper liefert eine umfangreiche Referenz zu den Parametern, mit denen man die Ausgabe der Prozeduren des DBMS_XPLAN-Packages beeinflussen kann (inklusive praktischer Beispiele). Doug Burns hat dem Herrn Hooper kürzlich den Ehrentitel "Blogging Machine" verliehen und da ist was dran.
Abonnieren
Posts (Atom)