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:

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.