Parallel Query ganz automatisch mit Oracle 11g
von Frank Schneede, ORACLE Deutschland GmbH

Datenbanken enthalten heutzutage eine riesige Menge an Daten, unabhängig davon, ob es sich um OLTP Systeme, Datawarehouses oder Operational Data Stores handelt. Die performante Ausführung von SQL-Abfragen kann daher durchaus eine Herausforderung darstellen, denn die Antwortzeit hängt in einem solchen Fall wesentlich von der Menge der abgefragten Daten ab. Ein gutes Mittel, Abfragen auf großen Datenmengen performant abzuarbeiten, ist die Nutzung von Parallel Query, die es bereits seit der Oracle Version 7.3 in der Enterprise Edition gibt. Die Grundidee von Parallel Query ist es, mehrere Unterprozesse zu starten, die jeder für sich eine Teilmenge der Daten bearbeiten und das Zwischenresultat dann zur Ermittlung des Endergebnisses an den zentralen Benutzerprozess zurückmelden. Durch dieses Prinzip werden die zur Verfügung stehenden Rechnerressourcen besser ausgenutzt und eine zum Teil erhebliche Verbesserung der SQL Antwortzeiten bei Abfragen großer Tabellen erreicht. Das Beispiel wird dies belegen.

Die parallele Ausführung von Abfragen war lange Zeit sehr restriktiv implementiert, so konnte parallel Query zuerst nur mit partitionierten Tabellen verwendet werden, wobei der Parallelitätsgrad (DoP = Degree of Parallelism) mit der Anzahl der Partitionen und den zur Verfügung stehenden Prozessoren korrespondieren musste. Diese Einschränkungen haben glücklicherweise für die aktuell im Einsatz befindlichen Releases keine Relevanz mehr, eine parallele Ausführung ist auch bei nicht partitionierten Objekten völlig unproblematisch. Bis zur Version Oracle 11g musste der DBA allerdings noch für alle in einer Abfrage verwendeten Tabellen und die dazugehörenden Indizes den DoP manuell ermitteln, einstellen und pflegen. Nur auf diese Weise war es überhaupt möglich, gleichbleibend gute Antwortzeiten zu erzielen. Man kann sich leicht vorstellen, dass es nicht immer ganz einfach war, den Parallelitätsgrad zu ermitteln, der für die bestmögliche Antwortzeit sorgte. Der optimale DoP hängt nämlich von vielen Faktoren ab, neben der reinen Datenmenge sind unter anderem auch die zur Verfügung stehenden CPU-Ressourcen (Prozessoren oder Kerne) oder die Art der Benutzerlast entscheidend.

In der aktuellen Datenbankversion Oracle 11g hat es rund um das Thema Parallel Query Verbesserungen gegeben, die den DBA mit sehr mächtigen Automatismen unterstützen. Dieser Artikel führt kurz in das Thema Parallel Query ein, zeigt die Möglichkeiten in Oracle 11g Release 2 und gibt Hinweise darauf, wie die parallele Ausführung überwacht und gesteuert werden kann:

  1. Einführung Funktionsweise Parallel Query
  2. Das Parallel Query Handwerkzeug für den DBA
  3. Parallel Query Überwachung
  4. Fazit


Einführung Funktionsweise Parallel Query

In den folgenden Beispielen wird das Schema SH verwendet, mit dessen Hilfe die gezeigten Statements leicht auf jeder Oracle Datenbank nachvollzogen werden können. Dieser erste Abschnitt soll dazu dienen, das Konzept und die Vorteile der parallelen Abarbeitung zu verstehen und Ausführungspläne lesen zu können. Im folgenden ersten Statement ist ein Join der Tabellen CUSTOMERS und SALES zu sehen, das Informationen über getätigte Verkäufe anzeigt. 
SQL> EXPLAIN PLAN FOR
  2  SELECT c.cust_last_name||', '||c.cust_first_name
  3  ,      s.time_id
  4  ,      s.amount_sold
  5  FROM   customers c
  6  ,      sales     s
  7  WHERE s.cust_id = c.cust_id ;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3549450340

----------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |   918K|    33M|       |  8110  (72)| 00:01:38 |       |       |
|*  1 |  HASH JOIN           |           |   918K|    33M|  1736K|  8110  (72)| 00:01:38 |       |       |
|   2 |   TABLE ACCESS FULL  | CUSTOMERS | 55500 |  1083K|       |   656  (39)| 00:00:08 |       |       |
|   3 |   PARTITION RANGE ALL|           |   918K|    15M|       |  3595  (87)| 00:00:44 |     1 |    28 |
|   4 |    TABLE ACCESS FULL | SALES     |   918K|    15M|       |  3595  (87)| 00:00:44 |     1 |    28 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."CUST_ID"="C"."CUST_ID")

16 rows selected.

SQL>

Ohne weitere Einstellungen an den Objekten vorzunehmen, erfolgt also eine serielle Abarbeitung des Statements. Sobald zum Beipiel in Oracle 11g durch den Befehl ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO auf parallele Abarbeitung umgestellt wird, versucht die Oracle Datenbank, soviel Arbeitsschritte wie möglich parallel auszuführen. In dem folgenden Ausführungsplan kann man die Vorteile zur seriellen Ausführung gut erkennen. Insbesondere in den Spalten Cost (%CPU) und Time des Ausführungsplans sieht man die zu erwartende Verbesserung (1:38 zu 0:34) in der Abfrageperformance. Der Ausführungsplan unterscheidet sich in erster Linie durch zusätzliche Schritte, die die Arbeit der verschiedenen parallelen Prozesse koordinieren und die einzelnen Zwischenergebnisse zusammenführen. 
SQL> EXPLAIN PLAN FOR
  2  SELECT c.cust_last_name||', '||c.cust_first_name
  3  ,      s.time_id
  4  ,      s.amount_sold
  5  FROM   customers c
  6  ,      sales     s
  7  WHERE s.cust_id = c.cust_id ;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1163973071

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |   918K|    33M|  2807  (83)| 00:00:34 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |           |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001  |   918K|    33M|  2807  (83)| 00:00:34 |       |       |  Q1,01 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |           |   918K|    33M|  2807  (83)| 00:00:34 |       |       |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |           | 55500 |  1083K|   294  (24)| 00:00:04 |       |       |  Q1,01 | PCWP |            |
|   5 |      PX SEND BROADCAST  | :TQ10000  | 55500 |  1083K|   294  (24)| 00:00:04 |       |       |  Q1,00 | P->P | BROADCAST  |
|   6 |       PX BLOCK ITERATOR |           | 55500 |  1083K|   294  (24)| 00:00:04 |       |       |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| CUSTOMERS | 55500 |  1083K|   294  (24)| 00:00:04 |       |       |  Q1,00 | PCWP |            |
|   8 |     PX BLOCK ITERATOR   |           |   918K|    15M|  1914  (87)| 00:00:23 |     1 |    28 |  Q1,01 | PCWC |            |
|   9 |      TABLE ACCESS FULL  | SALES     |   918K|    15M|  1914  (87)| 00:00:23 |     1 |    28 |  Q1,01 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("S"."CUST_ID"="C"."CUST_ID")

21 rows selected.

SQL>

Um einen parallelen Ausführungsplan und die sich dahinter verbergenden Prinzipien zu verstehen und letztlich auch steuern zu können, möchte ich an dieser Stelle einige Erklärungen zur parallelen Ausführung geben. Prinzipiell basiert die parallele Ausführung eines SQL Statements auf zwei verschiedenen Komponenten, dem Query Coordinator (abgekürzt QC, grün dargestellt) und den Parallel Execution (abgekürzt PX, blau dargestellt) Serverprozessen. Der Query Coordinator ist im obigen Beispiel im Schritt Id 1 leicht auszumachen. Er heißt PX COORDINATOR und dient dazu, die Einzelergebnisse der unabhängig arbeitenden PX Server Prozesse zusammenzuführen. In komplexeren SQL Statements kann der QC Prozess auch Arbeiten übernehmen, die nicht parallel ausgeführt werden können, zum Beispiel bei einer Abfrage eines Aggregates SUM() die Summation aller Zwischenergebnisse.

Die PX Server Prozesse werden aus einem Pool verfügbarer Prozesse bedient. Die Anzahl der möglichen PX Server Prozesse wird durch den init.ora Parameter PARALLEL_MAX_SERVERS limitiert, der Standardwert wird aus anderen init.ora Parametern (CPU_COUNT, PARALLEL_THREADS_PER_CPU, PGA_AGGREGATE_TARGET) abgeleitet. Die PX Server Prozesse sind auf Betriebssystemebene mit dem Befehl ps zu sehen: 
[oracle@sccloud027 ~]$ ps -efa |grep ora_p0                                                                                               
oracle   12282     1  0 13:17 ?        00:00:00 ora_p000_orcl
oracle   12284     1  0 13:17 ?        00:00:00 ora_p001_orcl
oracle   12286     1  0 13:17 ?        00:00:00 ora_p002_orcl
oracle   12288     1  0 13:17 ?        00:00:00 ora_p003_orcl
oracle   12316 12018  0 13:18 pts/1    00:00:00 grep ora_p0
[oracle@sccloud027 ~]$
Anhand des nächsten Beipiels möchte ich das Producer/Consumer Modell erläutern und gleichzeitig auf ein Problem aufmerksam machen, das sich ergibt, wenn nicht alle von einer SQL Abfrage verwendeten Objekte auf parallele Abarbeitung eingerichtet sind. In der Abfrage werden die Umsätze je Bundesstaat der USA ermittelt. Um diese Informationen berechnen zu können, wird der JOIN um die Tabelle COUNTRIES erweitert, die jedoch nicht parallelisiert ist. 
SQL> EXPLAIN PLAN FOR
  2  SELECT c.cust_state_province
  3  ,      sum(s.amount_sold) revenue
  4  FROM customers c
  5  ,    sales s
  6  ,    countries co
  7  WHERE s.cust_id = c.cust_id
  8  AND   s.time_id BETWEEN to_date('01-NOV-1998','DD-MON-YYYY')
  9                  AND     to_date('31-DEZ-1998','DD-MON-YYYY')
 10  AND   c.country_id = co.country_id
 11  AND   co.country_name = 'United States of America'
 12  GROUP BY c.cust_state_province;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1031408158

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |        |      |            |
|*  1 |  PX COORDINATOR                 |           |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10003  |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                |           |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                  |           |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH               | :TQ10002  |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY             |           |   145 |  7830 |  4011  (89)| 00:00:49 |       |       |  Q1,02 | PCWP |            |
|*  7 |        FILTER                   |           |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|*  8 |         HASH JOIN               |           |  1916 |   101K|  4003  (89)| 00:00:49 |       |       |  Q1,02 | PCWP |            |
|   9 |          PX RECEIVE             |           |  2921 |   102K|   405  (44)| 00:00:05 |       |       |  Q1,02 | PCWP |            |
|  10 |           PX SEND BROADCAST     | :TQ10001  |  2921 |   102K|   405  (44)| 00:00:05 |       |       |  Q1,01 | P->P | BROADCAST  |
|* 11 |            HASH JOIN            |           |  2921 |   102K|   405  (44)| 00:00:05 |       |       |  Q1,01 | PCWP |            |
|  12 |             BUFFER SORT         |           |       |       |            |          |       |       |  Q1,01 | PCWC |            |
|  13 |              PX RECEIVE         |           |     1 |    15 |     3   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  14 |               PX SEND BROADCAST | :TQ10000  |     1 |    15 |     3   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |
|* 15 |                TABLE ACCESS FULL| COUNTRIES |     1 |    15 |     3   (0)| 00:00:01 |       |       |        |      |            |
|  16 |             PX BLOCK ITERATOR   |           | 55500 |  1138K|   368  (40)| 00:00:05 |       |       |  Q1,01 | PCWC |            |
|* 17 |              TABLE ACCESS FULL  | CUSTOMERS | 55500 |  1138K|   368  (40)| 00:00:05 |       |       |  Q1,01 | PCWP |            |
|  18 |          PX BLOCK ITERATOR      |           | 36408 |   639K|  3573  (94)| 00:00:43 |     8 |   KEY |  Q1,02 | PCWC |            |
|* 19 |           TABLE ACCESS FULL     | SALES     | 36408 |   639K|  3573  (94)| 00:00:43 |     8 |   KEY |  Q1,02 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(' 1998-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE('31-DEZ-1998','DD-MON-YYYY'))
   7 - filter(TO_DATE(' 1998-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')<=TO_DATE('31-DEZ-1998','DD-MON-YYYY'))
   8 - access("S"."CUST_ID"="C"."CUST_ID")
  11 - access("C"."COUNTRY_ID"="CO"."COUNTRY_ID")
  15 - filter("CO"."COUNTRY_NAME"='United States of America')
  17 - filter(SYS_OP_BLOOM_FILTER(:BF0001,"C"."COUNTRY_ID"))
  19 - filter("S"."TIME_ID">=TO_DATE(' 1998-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "S"."TIME_ID"<=TO_DATE('31-DEZ-1998','DD-MON-YYYY') AND SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

38 rows selected.

SQL>
Die Prozesse dieses Beispiels sind drei Komponenten zuzuordnen. Die erste Komponente ist wieder der Query Coordinator (QC, grün dargestellt). Die anderen PX Server Prozesse sind Consumer (rot dargestellt) und Producer (blau dargestellt). Die Bearbeitung fängt mit der Verarbeitung der Tabelle CUSTOMERS (seriell!) an, hierbei muss von serieller auf parallele Arbeitsweise umgestellt werden (S->P in Spalte IN-OUT des Ausführungsplanes). Eine solche Umstellung sollte grundsätzlich vermieden werden, da bei größeren Tabellen mit Performanceeinbußen durch die Umstellung seriell/parallel gerechnet werden muss!

Das Ergebnis der Producer für die Tabellen COUNTRIES (Id 14) und CUSTOMERS (Id 16) wird durch den Consumer (Id 13) in einem Hash-Join (Id 11) verarbeitet und durch den Producer (Id 10) an den nächsten Consumer (Id 9) weitergereicht, der das Ergebnis des Joins mit der Tabelle SALES (Producer, Id 18) wiederum über einen Hash-Join verknüpft, filtert (Id 7) und gruppiert (Id 6). Am Ende der Verarbeitung werden die Zwischenergebnisse weitergereicht (Producer Id 5) und durch den obersten Consumer (Id 4) zusammengefasst. Dieses Ergebnis schließlich wird durch den Query Coordinator (Id 1) an den Benutzerprozess zurückgegeben.

Aus der Art des Zusammenspiels Producer/Consumer ergibt sich die Konsequenz, dass immer eine Kombination paralleler Prozesse (Producer/Consumer) vorhanden sein muss. Eine parallele JOIN Operation zweier Tabellen mit Parallelitätsgrad 4 bräuchte demnach die zweifache Anzahl des Parallelitätsgrades als PX Server Prozesse, also insgesamt 8 PX Server Prozesse, 4 Producer und 4 Consumer.

Die Daten, die ein PX Server Prozess bearbeitet, werden als Granules bezeichnet. Die Festlegung, wie die Granules gebildet werden, hängt vom SQL Statement und dem eingestellten DoP ab und wird ausschließlich durch die Datenbank selbst festgelegt.

Ein weiterer Punkt, der zu dem oben gezeigten Ausführungsplan anzumerken ist, betrifft die Daten-Umverteilung (Spalte PQ Redistrib im Ausführungsplan). Diese Umverteilung ist nötig, um für die unabhängig voneinander arbeitenden PX Server Prozesse sicherzustellen, dass jeder PX Prozess die für die Ausführung einer Join-Operation notwendigen Schlüsselattribute erhält. Dieses geschieht in dem oben gezeigten Beispiel auf zweierlei Weise:
  1. BROADCAST - dieses Verfahren wird angewendet, wenn die Ergebnismengen einer Join-Operation unterschiedlich groß sind. Die kleinere von beiden wird hierbei als Ganzes an alle anderen PX Server Prozesse geschickt.
  2. HASH - mit diesem Verfahren wird eine mengenmäßige Gleichverteilung der Ergebnismengen einer Join-Operation erreicht.

Nachdem nun die grundsätzliche Arbeitsweise von Parallel Query beschrieben worden ist, sollen im folgenden Abschnitt die Möglichkeiten vorgestellt werden, mit denen der DBA die Parallel Query Funktionalität beeinflussen kann.

Das Parallel Query Handwerkzeug für den DBA

Der DBA muss eine ganze Menge an Randbedingungen beachten, um mit Parallel Query tatsächlich eine nachhaltig gute Systemperformance zu erzielen. Dieses wird auch im Alltag deutlich, wenn gut eingerichtete Systeme im Laufe ihres Lebenszyklus an Performance zu wünschen übrig lassen. Um das zu vermeiden, sind in Oracle 11g Automatismen eingeführt worden, die nach der initialen Einrichtung dafür sorgen, dass eine gute Systemperformance in Bezug auf Parallel Query auch langfristig erhalten bleibt.

Die Oracle Datenbank ist standardmäßig so eingerichtet, dass Parallel Query genutzt werden kann. Wenn der DBA nun die Steuerung der Parallelität selbst in der Hand behalten möchte, anstelle auf datenbankinterne Automatismen zu vertrauen, so kann er das tun, in dem die Default-Parallelität für jedes gewünschte Objekt separat eingerichtet wird. Das Ergebnis sieht man anschließend im Data Dictionary. 
SQL> ALTER TABLE sales PARALLEL;

Table altered.

SQL> ALTER TABLE customers PARALLEL;

Table altered.

SQL> select table_name
  2  ,      degree
  3  FROM user_tables
  4  WHERE table_name IN ('SALES','CUSTOMERS');

TABLE_NAME                     DEGREE
------------------------------ ----------
CUSTOMERS                         DEFAULT
SALES                             DEFAULT

SQL>
Innerhalb eines Statements kann der gewünschte Parallelitätsgrad auch für jede Tabelle separat in einem Hint angegeben werden. Dieses Vorgehen ist nur sinnvoll, wenn man das System- bzw. Antwortzeitverhalten testen möchte. Eine grundsätzliche Festlegung des DoP zum Beispiel im Code eines Programms ist hingegen nicht sinnvoll, denn dann müssten bei Änderungen Anpassungen im Programmcode erfolgen. 
SELECT /*+ parallel(c,4) parallel(s,4) */ 
       c.cust_last_name||', '||c.cust_first_name
,      s.time_id
,      s.amount_sold 
FROM   customers c
,      sales     s
WHERE s.cust_id = c.cust_id ;
Soll innerhalb einer Sitzung Parallel Query genutzt werden, so kann das auch über die Änderung eines Session-Parameters geschehen. 
SQL> ALTER SESSION FORCE PARALLEL QUERY;

Session altered.

SQL>
Die bis hierher geschilderten Steuerungsmöglichkeiten sind auch bereits vor Oracle 11g nutzbar, erfordern nur eine stetige Überwachung und gegebenenfalls Anpassung der Systemparameter. Zudem kann es bei einer ungünstigen Dimensionierung des Parallelitätsgrades dazu kommen, dass das System überlastet wird und somit genau das Gegenteil dessen passiert, was der DBA bzw. Anwendungsentwickler beabsichtigt hat, nämlich ein massiver Performanceeinbruch. Um diesem möglichen Problem vorzubeugen, wurden Parametrisierungsparameter eingeführt, mit deren Hilfe Grenzen für das Starten von parallelen Prozessen gesetzt werden können. Der Parameter PARALLEL_MAX_SERVERS definiert diesen Grenzwert. Der Wert stellt sicher, dass nicht mehr PX Server Prozesse gestartet werden, als vom DBA gewünscht. Standardmäßig wird der Wert von PARALLEL_MAX_SERVERS aus der Anzahl der CPUs, der Anzahl der Threads pro CPU (auf den meisten Systemen vom Betriebssystem her auf 2 festgelegt) und der Größe der SGA berechnet, kann aber auch jederzeit dynamisch angepasst werden.

In den unterschiedlichen Beispielen dieses Artikels wurden bereits viele der Möglichkeiten genutzt, den DoP zu setzen. Die Angabe DEFAULT bewirkt, dass durch die Datenbank der DoP auf 2*CPU_COUNT gesetzt wird, bei Cluster-Umgebungen wird dieser Wert nochmals mit dem ACTIVE_INSTANCE_COUNT multipliziert. Eine solche Festlegung kann in Mehrprozessorsystemen bzw. solchen mit vielen Prozessorkernen dazu führen, dass der Parallelitätsgrad sehr hoch ist. Ein System mit vielen gleichzeitig aktiven Benutzern kommt dann schnell an seine Grenze, ein einzelner Batchjob nutzt die zur Verfügung stehenden Ressourcen hingegen optimal aus.

Um dieses Problem zu umgehen, kann ein fester Parallelitätsgrad im Data Dictionary eingetragen werden. Es hat sich hierbei in vielen Fällen bewährt, einen Grad von höchstens 4 zu wählen und diesen bei allen korrespondierenden Objekten (d. h. den zugehörigen Indizes) identisch zu halten. Bei einem laufenden System hat es der DBA hier oftmals schwer, diese Regel einzuhalten, was in letzter Konsequenz dann wieder zur Performanceproblemen führen kann. 
SQL> ALTER TABLE sales PARALLEL 4;

Table altered.

SQL> ALTER TABLE customers PARALLEL 4;

Table altered.

SQL>
In Oracle 11g steht dem DBA nun eine sehr elegante Lösung für den Einsatz von Parallel Query zur Verfügung, dieses ist der Automatic Degree of Parallelism. Die Funktion wird über den init.ora Parameter PARALLEL_DEGREE_POLICY gesteuert. Der Oracle Optimizer berechnet die benötigte Zeit für die serielle Abarbeitung des Statements und prüft das ermittelte Ergebnis gegen den Schwellenwert PARALLEL_MIN_TIME_THRESHOLD. Wenn die berechnete Zeit diese Schwelle nicht übersteigt, dann wird seriell gearbeitet, anderenfalls erfolgt die Berechnung des optimalen DoP. Die folgende Grafik illustriert die Implementierung.

Für eine größere Ansicht auf das Bild klicken


Das oben dargestellte Problem der möglichen Systemüberlastung wird bei dem geschilderten Automatismus dadurch vermieden, dass der ideale DoP, der unmittelbar von der Objektgröße abhängt, und ein weiterer Schwellenwert, PARALLEL_DEGREE_LIMIT, in Relation gesetzt werden. PARALLEL_DEGREE_LIMIT berechnet sich hierbei nach der Regel PARALLEL_THREADS_PER_CPU * CPU_COUNT * ACTIVE_INSTANCE_COUNT. Das Minimum aus dem idealen DoP und dem PARALLEL_DEGREE_LIMIT wird dann für die Ausführung des Statements verwendet. Da PARALLEL_DEGREE_LIMIT manuell modifiziert werden kann, hat der DBA jederzeit direkten Einfluss auf das Systemverhalten und kann basierend auf seinen eigenen Erfahrungen Grenzwerte festlegen. Im Ausführungsplan wird die durch den Optimizer getroffene Entscheidung im Bereich NOTES festgehalten. 

SQL> ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

Session altered.

SQL> EXPLAIN PLAN FOR
  2  SELECT c.cust_state_province
  3  ,      sum(s.amount_sold) revenue
  4  FROM customers c
  5  ,    sales s
  6  ,    countries co
  7  WHERE s.cust_id = c.cust_id
  8  AND   s.time_id BETWEEN to_date('01-NOV-1998','DD-MON-YYYY')
  9                  AND     to_date('31-DEC-1998','DD-MON-YYYY')
 10  AND   c.country_id = co.country_id
 11  AND   co.country_name = 'United States of America'
 12  GROUP BY c.cust_state_province;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2054505035

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   145 |  7830 |   550  (56)| 00:00:07 |       |       |        |      |            |
|   1 |  PX COORDINATOR                |           |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10003  |   145 |  7830 |   550  (56)| 00:00:07 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY               |           |   145 |  7830 |   550  (56)| 00:00:07 |       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                 |           |   145 |  7830 |   550  (56)| 00:00:07 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH              | :TQ10002  |   145 |  7830 |   550  (56)| 00:00:07 |       |       |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY            |           |   145 |  7830 |   550  (56)| 00:00:07 |       |       |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN               |           |  1526 | 82404 |   544  (56)| 00:00:07 |       |       |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE             |           |  2921 |   102K|   404  (45)| 00:00:05 |       |       |  Q1,02 | PCWP |            |
|   9 |          PX SEND BROADCAST     | :TQ10001  |  2921 |   102K|   404  (45)| 00:00:05 |       |       |  Q1,01 | P->P | BROADCAST  |
|* 10 |           HASH JOIN            |           |  2921 |   102K|   404  (45)| 00:00:05 |       |       |  Q1,01 | PCWP |            |
|  11 |            PX RECEIVE          |           |     1 |    15 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  12 |             PX SEND BROADCAST  | :TQ10000  |     1 |    15 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
|  13 |              PX BLOCK ITERATOR |           |     1 |    15 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
|* 14 |               TABLE ACCESS FULL| COUNTRIES |     1 |    15 |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  15 |            PX BLOCK ITERATOR   |           | 55500 |  1138K|   368  (40)| 00:00:05 |       |       |  Q1,01 | PCWC |            |
|* 16 |             TABLE ACCESS FULL  | CUSTOMERS | 55500 |  1138K|   368  (40)| 00:00:05 |       |       |  Q1,01 | PCWP |            |
|  17 |         PX BLOCK ITERATOR      |           | 28995 |   509K|   120  (86)| 00:00:02 |     8 |     8 |  Q1,02 | PCWC |            |
|* 18 |          TABLE ACCESS FULL     | SALES     | 28995 |   509K|   120  (86)| 00:00:02 |     8 |     8 |  Q1,02 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("S"."CUST_ID"="C"."CUST_ID")
  10 - access("C"."COUNTRY_ID"="CO"."COUNTRY_ID")
  14 - filter("CO"."COUNTRY_NAME"='United States of America')
  16 - filter(SYS_OP_BLOOM_FILTER(:BF0001,"C"."COUNTRY_ID"))
  18 - filter("S"."TIME_ID">=TO_DATE(' 1998-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<=TO_DATE(' 1998-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

39 rows selected.

SQL>
Der Parameter PARALLEL_DEGREE_POLICY kann auf Session- oder Systemebene geändert werden. Es gibt auch die Möglichkeit, die Auto DoP-Funktion durch den Hint PARALLEL(AUTO) zu nutzen. Mit den beschriebenen Alternativen hat der DBA für nahezu jede Lebenslage eine gute Lösungsmöglichkeit. Der Auto DoP sollte Vorrang vor dem bisherigen Algorithmus mittels PARALLEL_ADAPTIVE_MULTI_USER haben, da mit dem neuen Algorithmus mehr Statements parallel abgearbeitet werden können.

Die Einstellung PARALLEL_DEGREE_POLICY=AUTO ignoriert jede andere manuelle Festlegung. Sollen weiterhin bestimmte benutzerdefinierte Ausführungen von Statements stattfinden, so muss PARALLEL_DEGREE_POLICY=MANUAL gesetzt werden. Die dritte Einstellungsmöglichkeit, PARALLEL_DEGREE_POLICY=LIMITED, bedeutet, dass der Auto DoP nur für Tabellen oder Indizes, die über die PARALLEL Klausel ohne expliziten Parallelitätsgrad definiert worden sind, berechnet wird. Alle weiteren PQ Funktionen, wie das Parallel Statement Queueing, werden nicht genutzt.

Bei einem Multiuser Betrieb und lang laufenden Parallel Query Statements kann es natürlich vorkommen, dass die Datenbank an die Grenzen der zur Verfügung stehenden Ressourcen stößt. Bislang war es so, dass ein Statement in diesem Fall üblicherweise serialisiert abgearbeitet oder - bei Verwendung des init.ora Parameters PARALLEL_MIN_PERCENT - eine Fehlermeldung erzeugt wurde. Mit Oracle 11g gibt es die Möglichkeit des parallel Statement Queueing. In diesem Fall wird ein Statement, für dessen optimale Ausführung die Ressourcen fehlen, solange in einer internen Queue geparkt, bis genügend PX Server Prozesse zur Verfügung stehen, und anschließend quasi mit voller Kraft ausgeführt. Das Abwarten ist erfahrungsgemäß besser, als das Statement möglicherweise extrem langwierig seriell auszuführen. Das folgende Schaubild zeigt den Algorithmus.

Für eine größere Ansicht auf das Bild klicken


Parallel Statement Queueing arbeitet strikt nach dem Prinzip "first in - first out", dieses ist für die Einhaltung der Datenkonsistenz sehr wichtig. Im Einzelfall kann es wünschenswert sein, Statement Queueing explizit auszuschalten. Das geschieht durch Einsatz des Hints /*+ NO_STMT_QUEUING */ . Der DBA kann über eine Abfrage des Data Dictionaries prüfen, ob gerade ein Parallel Statement Queuing stattfindet, wie die folgende Abfrage zeigt. 
SQL> SELECT sql_id, sql_text
  2  FROM v$sql_monitor
  3  WHERE status='QUEUED';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
9utw4mbrm17t7
SELECT c.cust_state_province
,      sum(s.amount_sold) revenue
FROM customers c
,    sales s
,    countries co
WHERE s.cust_id = c.cust_id
AND   s.time_id BETWEEN to_date('01-NOV-1998','DD-MON-YYYY')
                AND     to_date('31-DEC-1998','DD-MON-YYYY')
AND   c.country_id = co.country_id
AND   co.country_name = 'United States of America'
GROUP BY c.cust_state_province


SQL>
Parallel Query Überwachung

Für die Überwachung von Parallel Query Prozessen können natürlich die Data Dictionary Views
  • v$pq_slave
  • v$pq_sysstat
  • v$pq_sesstat
  • v$pq_tqstat

genutzt werden. Wesentlich einfacher ist allerdings die Verwendung des Oracle Enterprise Manager Grid Control, der eine intuitive grafische Oberfläche bietet. Im Abschnitt Performance bekommt man über den Link SQL Überwachung die im letzten Auswertungsintervall überwachten SQL Statements angezeigt. Mit dieser Anzeige hat der Anwender sofort einen Überblick über die aktuell im System befindlichen SQL Statements. In dem unten dargestellten Screenshot sieht man zum Beispiel, dass zwei Statements beendet worden sind, eines wird aktuell ausgeführt und eines befindet sich in der Warteschlange.

Für eine größere Ansicht auf das Bild klicken


Durch einen Klick auf die SQL Id erhält man eine genaue Übersicht zu dem ausgewählten Statement. Hier ist insbesondere der Blick auf den Ausführungsplan interessant. Die verschiedenen Schritte des Ausführungsplans sind detailliert aufgeschlüsselt und farblich markiert, dass gut erkennbar ist, welche einzelnen Schritte in Gruppen zusammengefasst werden. Durch Auswahl des Reiters Parallel im Detail-Fenster erhält man eine Aufstellung über die Aktivitäten der unterschiedlichen PX Server Prozesse.

Für eine größere Ansicht auf das entsprechende Bild klicken


Fazit

Der Bereich Parallel Query ist in Oracle 11g erheblich einfacher geworden. Von den vorhandenen 14 init.ora Parametern, die es im Umfeld von Parallel Query gibt, sind im Grunde nur 3 wirklich interessant:
  • PARALLEL_MAX_SERVERS: Mit diesem Parameter wird festgelegt, wie viele PX Server maximal vorhanden sein können.
  • PARALLEL_MIN_SERVERS: Dieser Parameter legt fest, wie viele PX Server minimal gestartet sein müssen. Dieses dient dazu, das zeitkritische Starten von PX Server Prozessen zu minimieren.
  • PARALLEL_DEGREE_POLICY: Mit diesem Parameter kann in der Einstellung AUTO Parallel Query nahezu vollständig automatisiert werden. Dieses ist ganz eindeutig die empfohlene Methode. Die Einstellung MANUAL sollte nur aus Gründen der Rückwärtskompatibilität genutzt werden.

Mit dem neuen, effizienten Algorithmus zur Steuerung der Parallel Query Funktionalität hat der DBA das Werkzeug, die eingesetzten Systeme optimal auszunutzen.

Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite