Logo Oracle Deutschland   DBA Community  -   August 2015
Parallele DML Operationen
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Schon lange stellt die Oracle Datenbank eine Parallel Execution Engine zur Verfügung, die ohne viel Zutun eine Parallelisierung von Operationen wie zum Beispiel SELECT, DDL, Rollback, Recovery, und auch DML ermöglicht. Im Falle der parallelen DML Operationen (auch kurz PDML) wird die Verarbeitung der Zeilen dabei nicht nur mit einem Prozess sondern gleich mit mehreren Prozessen - den sogenannten Parallel Execution Prozessen - durchgeführt. So können Änderungen an großen Datenmengen enorm beschleunigt werden.

Da zu diesem Thema immer wieder Fragen beziehungsweise Missverständnisse bei der Verwendung auftauchen, möchte ich in den folgenden Abschnitten einige grundsätzliche Informationen dazu geben und mit einigen Mythen, die immer noch kursieren, aufräumen. Natürlich wird in diesem Blog der aktuelle Stand der Datenbank wiedergegeben und es werden die neuen Features in Oracle Database 12c erklärt.

Aktivieren

Bevor man Parallelisierung einsetzen möchte, sollte man sich zuerst die Frage stellen, ob es sich überhaupt lohnt, die Operationen zu parallelisieren. Der Vorteil von PDML zeigt sich beispielsweise an umfangreichen Änderungen von großen Tabellen. Allerdings sind zum Betreiben der Parallel Execution Prozesse entsprechende Systemressourcen nötig. Hat man beispielsweise einen Engpass im CPU Umfeld, sollte man auf den Einsatz dieser Techniken verzichten. Die Parallel Execution Server sind übrigens standardmässig aktiviert und werden über den Parameter PARALLEL_MAX_SERVERS gesteuert. Ist dieser Parameter auf 0 gestellt, können keine Parallel Server Prozesse verwendet werden. Der Einsatz von Parallel DML hat übrigens nichts mit dem Vorhandensein von Partitionen einer Tabelle zu tun; auch eine nicht partitionierte Tabelle kann von Parallel DML profitieren.

Grundsätzlich handelt es sich bei Parallel DML um Kommandos wie UPDATE, INSERT, DELETE und MERGE, die parallel verarbeitet werden sollen. SELECT Operationen sind nicht damit gemeint! Somit ist es auch ein Irrtum, dass sich Parallel DML mit dem Parameter PARALLEL_AUTOMATIC_TUNING einschalten lässt. PARALLEL_AUTOMATIC_TUNING wird nur zur Automatisierung von SELECT Abfragen verwendet. Parallel DML hingegen wird über Session Kommandos oder ab Oracle Database 12c über einen speziellen Statement Hint aktiviert. Der folgende Befehl führt dazu, dass alle nachfolgenden DML Statements mit entsprechendem PARALLEL Hint oder der entsprechenden Tabellenklausel parallelisiert werden. 
SQL> ALTER SESSION ENABLE PARALLEL DML;
Statement processed;
SQL> insert /*+ parallel(2,t) */ into sales_copy t select * from sales_copy t;
918843 rows created.
Das Setzen einer entsprechenden Tabellenklausel würde folgendermassen aussehen: 
SQL> ALTER TABLE sales_copy PARALLEL 2;
SQL> select degree, table_name from user_tables where table_name like 'SALES%'

DEGREE                                   TABLE_NAME
---------------------------------------- ------------------------------
         1                               SALES
         1                               SALES_ATTRIBUTE
         1                               SALES_TRANSACTIONS_EXT
         1                               SALES_YEAR
         2                               SALES_COPY
Dann würde auch folgendes Statement parallel ablaufen: 
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.

SQL> insert into sales_copy t select * from sales_copy t;
918843 rows created.
Übrigens falls die Tabelle mit einem Parallel DDL Statement angelegt wurde, wird der Parallel Degree als Tabellenklausel für zukünftige Operationen übernommen. Falls dies nicht erwünscht ist, sollte man das Tabellenattribut zurücksetzen. Folgendes Beispiel demonstriert das Verhalten. 
SQL> CREATE TABLE sales_copy2 PARALLEL 2 AS  select * from sales ;
SQL> select degree, table_name from user_tables where table_name like 'SALES%';

DEGREE                                   TABLE_NAME
---------------------------------------- ------------------------------
         2                               SALES_COPY
         2                               SALES_COPY2
         1                               SALES_ATTRIBUTE
         1                               SALES
         1                               SALES_TRANSACTIONS_EXT
         1                               SALES_YEAR
--Zuruecksetzen
SQL> ALTER TABLE sales_copy2 NOPARALLEL;
Eine weitere Möglichkeit besteht darin, gleich den Parallelisierungsgrad mit dem folgenden Kommando anzugeben. 
SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
Session altered.
SQL> insert into sales_copy t select * from sales;
918843 rows created.

SQL> SELECT name, value
     FROM v$ses_optimizer_env
     WHERE sid=userenv('sid') AND isdefault='NO';

NAME                                     VALUE
---------------------------------------- -------------------------
parallel_dml_forced_dop                  2
parallel_dml_mode                        forced
Neu mit Oracle Database 12c ist die Möglichkeit nur ein einziges Statement einer Session zu parallelisieren. Dazu ist der neue Hint ENABLE_PARALLEL_DML PARALLEL (x) eingeführt worden. Folgendes Beispiel gibt den Grad der Parallelisierung mit 2 vor. 
insert /*+ enable_parallel_dml parallel(t 2) */ 
into sales_copy t select * from sales;
Das Ganze funktioniert auch mit dem Hint ENABLE_PARALLEL_DML PARALLEL ohne Angabe des Parallelisierungsgrads. Die Deaktivierung erfolgt übrigens im Hint wie in der Session über das Schlüsselwort DISABLE - also beispielsweise über den Hint DISABLE_PARALLEL_DML bzw. über das folgende Statement. 
ALTER SESSION DISABLE PARALLEL DML;
Wenn man also auf die Eingabe des Parallelisierungsgrads verzichtet, wird der Wert im Hint oder im Tabellenattribut zur Berechnung des Parallelisierungsgrads hinzugezogen. Zur Erinnerung: Der Parallelisierungsgrad (auch DOP) gibt die Anzahl der Parallel Execution Server an, die eine einzige Operation parallel verarbeiten. Die Gesamtanzahl von Parallel Execution Server für ein Statement kann maximal das Zweifache des ermittelten DOPs betragen.

Wichtig zu wissen ist allerdings Folgendes (Zitat aus dem Blog von Yasin Baskan, Produktmanager für Data Warehouse Themen) "What is missing from the documentation is that the maximum DOP you can get will always be the default DOP, which is CPU_COUNT * PARALLEL_THREADS_PER_CPU. No matter what number you set for PARALLEL_DEGREE_LIMIT, the internal enforced limit will stay the same, any value higher than this will be ignored.
Max DOP = MIN (CPU_COUNT * PARALLEL_THREADS_PER_CPU, PARALLEL_DEGREE_LIMIT, DB Resource Manager Parameter(PARALLEL_DEGREE_LIMIT_P1))"

Da ich in meiner Umgebung keinen Ressource Manager nutze, ist der Defaultwert für PARALLEL_DEGREE_LIMIT_P1 unbegrenzt. In meiner "unterdimensionierten" Testumgebung sehen die weiteren Einstellungen folgendermassen aus: 
SQL> show parameter cpu
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     1
parallel_threads_per_cpu             integer     2
...
SQL> show parameter degree
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_level                integer     100
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
So muss ich mich also nicht wundern, wenn ich keinen höheren DOP als 2 erreichen kann. Änderungen an CPU_COUNT und PARALLEL_THREADS_PER_CPU sind übrigens über einen ALTER SYSTEM Befehl möglich, sollten allerdings immer gründlich bedacht werden.

Wichtiger Hinweis: Auch wenn Parallel DML in der Session aktiviert wurde, können die DML Operationen seriell ausgeführt werden. Das Fehlen eines Hints oder das Fehlen des Tabellenattributs PARALLEL können Ursachen dafür sein. Allerdings kann auch ein Verstoss gegen die Einschränkungen zu solch einem Verhalten führen.

Überwachen

Läuft mein Statement überhaupt parallel? Wie hoch ist der Parallelisierungsgrad? Wie kann ich die Performance überwachen? Dies sind häufig Fragen im Zusammenhang mit der Verwaltung von Parallel DML Operationen. Eine gute Möglichkeit der Überwachung bietet wie immer die Ausgabe des Ausführungsplan mit DBMS_XPLAN. Sehen wir uns dazu die Beispiele aus dem oberen Abschnitt an. 
SQL> insert /*+ enable_parallel_dml parallel(t,2) */ into sales_copy t select * from sales; 
918843 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
insert /*+ enable_parallel_dml parallel(t,2) */ into sales_copy t
select * from sales

Plan hash value: 913398603

-------------------------------------------------------
| Id  | Operation                          | Name     |
-------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |
|   1 |  PX COORDINATOR                    |          |
|   2 |   PX SEND QC (RANDOM)              | :TQ10001 |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|          |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |
|   5 |      PX RECEIVE                    |          |
|   6 |       PX SEND ROUND-ROBIN          | :TQ10000 |
|   7 |        PARTITION RANGE ALL         |          |
|   8 |         TABLE ACCESS FULL          | SALES    |
-------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - Degree of Parallelism is 2 because of table property
Ab 12c gibt es sogar weitere wichtige Hinweise und Erklärungen im Abschnitt "Note". Beispielsweise wird die Information, warum ein Statement kein PDML benutzt, sondern seriell abgearbeitet wird, nun in Oracle Database 12c dokumentiert. Folgendes Beispiel demonstriert das Verhalten. 
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.

SQL>  insert /*+ parallel (t,2) */ into sales_copy select * from sales;
918843 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
 insert into sales select * from sales

Plan hash value: 3060979429
---------------------------------------------
| Id  | Operation                | Name     |
---------------------------------------------
|   0 | INSERT STATEMENT         |          |
|   1 |  LOAD TABLE CONVENTIONAL | SALES    |
|   2 |   PX COORDINATOR         |          |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |
|   4 |     PX BLOCK ITERATOR    |          |
|   5 |      TABLE ACCESS FULL   | SALES    |
---------------------------------------------

Note
-----
   - Degree of Parallelism is 2 because of session
   - PDML disabled because parent referential constraints are present
   - Direct Load disabled because parent referential constraints are present
Eine weitere Möglichkeit der Überwachung ist, die Parallel Query Execution Prozesse mit V$PQ_SESSTAT abzufragen. 
SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.

SQL> insert /*+ parallel (t 2) */ into sales_copy t select * from sales;
918843 rows created.

SQL> SELECT * FROM v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized                    0             1          0
DML Parallelized                        1             2          0
DDL Parallelized                        0             0          0
DFO Trees                               1             3          0
Server Threads                          2             0          0
Allocation Height                       2             0          0
Allocation Width                        1             0          0
Local Msgs Sent                      2110          2197          0
Distr Msgs Sent                         0             0          0
Local Msgs Recv'd                      14          2197          0
Distr Msgs Recv'd                       0             0          0
DOP                                     2             0          0
Slave Sets                              1             0          0

13 rows selected.
In manchen Fällen (zum Beispiel FORCE PARALLEL) genügt es, die Optimizer Session Informationen abzurufen. Beispiele dazu finden sich im Abschnitt "Aktivieren". Sehr einfach ist das Monitoring übrigens auch mit graphischen Mitteln zu bewerkstelligen. Das SQL Monitoring Feature eignet sich hervorragend dazu. Nutzbar ist SQL Monitoring in den graphischen Oberflächen wie Cloud Control oder Database Express (ab 12c). Zusätzlich dazu besteht die Möglichkeit, Reports auch mit Linemode Aufruf als Active oder HTML Report zu generieren. Das folgende Beispiel zeigt die Einstiegsseite eines solchen SQL Monitoring Reports.



Über den Menüpunkt "Parallel" gelangt man zu der Übersicht der Parallel Server Sets.



Möchten Sie mehr zu SQL Monitoring wissen, können Sie auch folgende Blogeinträgen lesen:

Einschränkungen

Parallele DML Operationen unterliegen einem speziellen Locking und Speicherplatz Verhalten, auf das ich im Rahmen dieses Blogpostings nicht eingehen möchte (siehe auch das Kapitel "Weitere Informationen"). Zusätzlich operieren die parallelen Execution Prozesse in eigenen Transaktionen. Die Konsequenz aus diesen Tatsachen führt dazu, dass Parallele DML Operationen einigen Einschränkungen unterliegen. Wichtig zu wissen ist auch, dass alle Operationen bis auf eine Ausnahme (siehe unten) auf eine serielle Ausführung umschalten und nicht mit einem Fehler abbrechen. Im Folgenden habe ich ein paar wichtige Einschränkungen aufgelistet:
  • Nach einer parallelen DML Operation können nachfolgende Operationen nicht die gleiche Tabelle selektieren oder verändern. In diesem Fall erfolgt eine Fehlermeldung!
  • Parallele DML Operationen können nicht auf Tabellen mit Triggern ausgeführt werden.
  • PDML kann nicht auf Tabellen mit gewissen referentiellen Constraints operieren.
  • INSERT VALUES Statements werden niemals parallel ausgeführt.
  • Tabellen mit LOB Spalten (gespeichert als BasicFile) können nur dann parallel verarbeitet werden, falls die Tabelle partitioniert ist.
Die vollständige Liste aller Einschränkungen kann man im Handbuch Database VLDB and Partitioning Guide im Kapitel 8 nachlesen.

Hinweis: Mit Oracle Database 12c sind auch parallele DML Operationen auf nicht partitionierten Tabellen mit SecureFile Lobs möglich (siehe auch Handbucheintrag).

Folgendes Beispiel zeigt die Ausführung und den Ausführungsplan bei Verstoss gegen eine dieser Regeln. Bitte beachten Sie die Hinweise im Abschnitt "Note" der Ausgabe. Ein weiteres Beispiel findet sich im Abschnitt "Monitoring". 
SQL> update /*+ parallel (t 2) */ sales set amount_sold=100 where rownum<10000;

9999 rows updated.

SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
update /*+ parallel (t 2) */ sales set amount_sold=100 where
rownum<10000

Plan hash value: 2267128321

---------------------------------------
| Id  | Operation             | Name  |
---------------------------------------
|   0 | UPDATE STATEMENT      |       |
|   1 |  UPDATE               | SALES |
|   2 |   COUNT STOPKEY       |       |
|   3 |    PARTITION RANGE ALL|       |
|   4 |     TABLE ACCESS FULL | SALES |
---------------------------------------
Note
-----
   - PDML disabled because triggers are defined

21 rows selected.

DBMS_EXECUTE_PARALLEL - eine Alternative

Möchte man mehr Einfluss auf die parallele Verarbeitung ausüben beziehungsweise verstösst die Tabelle mit ihren Eigenschaften gegen eine der oben erwähnten Regeln, kann man als Alternative das Package DBMS_EXECUTE_PARALLEL verwenden. Das Package DBMS_EXECUTE_PARALLEL ist mit 11g Release 2 eingeführt worden und steht in jeder Edition der Datenbank zur Verfügung. DBMS_PARALLEL_EXECUTE unterteilt eine große Tabelle in kleine Abschnitte (Chunks) gemäß unterschiedlicher Kriterien wie ROWID, bestimmter Spaltenwerte oder gar selbsdefinierter Methoden. Ein SQL Statement und auch ein PL/SQL Block kann damit eine Tabelle parallel verarbeiten. Die Parallelisierung wird dabei über einen Scheduler Job verwaltet. Im Gegensatz zu PDML muss hier also programmiert werden. Zusätzlich erhält man aber dafür die Möglichkeit, ein Error Logging, automatische Wiederanläufe und eine COMMIT Ausführung pro Chunkverarbeitung zu verwenden. Möchte man mehr dazu erfahren, kann man folgende Beschreibungen im Internet verwenden:

Lizenzierung

Die Nutzung von Parallel DML erfordert die Lizenzierung der Enterprise Edition. Die Verwendung von DBMS_EXECUTE_PARALLEL ist in jeder Edition verfügbar.

Weitere Informationen

Zurück zur Community-Seite