Parallelisierung von DML-Operationen mit DBMS_PARALLEL_EXECUTE IN 11.2

04.
April
2013
Veröffentlicht von: Elke Fritsch

Die Parallelisierung von größeren DELETE- und UPDATE-Aktionen bietet diverse Vorteile:

Die Parallelisierung von größeren DELETE- und UPDATE-Aktionen bietet diverse Vorteile:

  • Die Performance wird durch den parallelen Zugriff erhöht.
  • Es wird weniger Platz in den Undo-Segmenten verbraucht, da die Transaktion auf mehrere kleinere Einheiten verteilt wird.
  • Die Tabellen sind nicht so lange offline, da die Sperren schneller aufgehoben werden können.

Wenn man selber prozedurale Lösungen zum parallelen Löschen oder Aktualisieren großer Datensatzmengen erstellen will, wird das allerdings schnell mühsam, weil man erst einmal sicherstellen muss, dass die Pakete so aufgeteilt werden, dass sich die einzelnen Transaktionen nicht gegenseitig behindern.
Ab der Oracle Version 11 Release 2 gibt es eine Oracle-eigene Lösung für die Parallelisierung von DML-Aktionen über den Scheduler, die den Anwendern sehr viel Arbeit abnehmen kann, das Package DBMS_PARALLEL_EXECUTE.
In diesem Fall werden die Tabellen über einen DBMS_SCHEDULER-Prozess automatisch in Teilbereiche, sog. Chunks, unterteilt (die aber nichts mit den Chunks bei der Speicherung von LOBs zu tun haben). Auf diese Abschnitte werden die DML-Befehle parallel abgesetzt. Ein COMMIT erfolgt nach jeder Fertigstellung des DML-Befehls auf dem jeweiligen Tabellenbereich. Dadurch werden die Undo-Segmente weniger stark belastet.
Um das Fehlerlogging und automatische Wiederholungen im Fehlerfall kümmert sich ebenfalls der Scheduler.
Die einzige Voraussetzung für die Nutzung ist das CREATE JOB-Recht. Das Package an sich ist an Public gegrantet. Im Gegensatz zu den meisten anderen Optionen, die mit Parallelisierung zu tun haben, ist die Nutzung des Packages nicht auf die Enterprise Edition beschränkt!!
Das Package besteht aus folgenden Prozeduren, die alle einen Commit beinhalten.

  • CREATE_TASK erstellt einen Auftrag für die parallele Abarbeitung.
  • Über CREATE_CHUNKS_BY_ROWID kann man die Teilbereiche der Tabelle über die  ROWID definieren. Diese Unterteilung ist die unkomplizierteste Methode. Die Abschnitte überlappen sich garantiert nicht, insofern hat man auch keine Probleme mit Sperren und die Tabelle muss für das Chunking nicht extra abgefragt werden, da die Informationen über die ROWIDs bequem aus dem Data Dictionary bezogen werden können.
  • Wenn man die Aufteilung der Tabelle über ein SQL-Statement bestimmen will, benutzt man CREATE_CHUNKS_BY_SQL.
  • Auch eine Stückelung anhand der Werte einer numerischen Spalte ist möglich, die entsprechende Prozedur heißt CREATE_CHUNKS_BY_NUMBER_COL.
  • Nach dem Erstellen der Chunks startet man die Abarbeitung über die Prozedur RUN_TASK.
  • TASK_STATUS liefert Informationen über den Status.
  • STOP_TASK würgt den Auftrag ab.
  • RESUME_TASK startet die Wiederaufnahme.
  • DROP_TASK löscht den Task.

Die Funktion TASK_STATUS kann zum Monitoren und zur Fehlerbehandlung genutzt werden.

 

Rückgabewert Bedeutung
CREATED

Ein Task wurde erstellt

CHUNKINGDie Tabelle wird in Chunks aufgeteilt,
CHUNKEDDie Tabelle wurde in Chunks aufgeteilt, aber die Prozessierung hat noch nicht begonnen
CHUNKING_FAILEDEs gab einen Fehler bei der Aufteilung
PROCESSINGProzessierung läuft
CRASHEDEiner der parallelen Prozesse oder die gesamte Datenbank ist während der Ausführung der Transaktion abgestürzt.
FINISHEDAlle Tabellenabschnitte wurden fehlerfrei prozessiert
FINISHED_WITH_ERRORAlle Tabellenabschnitte wurden prozessiert, aber es sind Fehler aufgetreten

Beispiel: Paralleler Update, Einteilung über die ROWID

Probieren's wir mal aus. Für den ersten Test nehmen wir Tom Kytes bewährte Tabelle Initiates file downloadbig_tab mit 2 Millionen Datensätzen. Weil wir später die Session_ids bei der parallelen Ausführung ermitteln wollen, benennen wir die data_object_id-Spalte in session_id um.

Zunächst braucht Scott das Create Job-Recht:

conn / as sysdba
GRANT CREATE JOB TO scott;

conn scott/tiger
@ d:/create_bigtab.sql
ALTER TABLE big_tab RENAME COLUMN data_object_id TO session_id;

Jetzt kann Scott einen Task erstellen. Der Taskname ist einfach ein VARCHAR2-Parameter. Er muss nicht den Regeln für Oracle-Bezeichner entsprechen:

BEGIN
   DBMS_PARALLEL_EXECUTE.create_task (task_name => 'update big_tab, chunks by rowid');
END;

Ob die Erstellung geklappt hat, kann man über die View USER_PARALLEL_EXECUTE_TASKS herausfinden:

col task_name for a50
SELECT task_name, status FROM user_parallel_execute_tasks;
=>
TASK_NAME                             STATUS
------------------------------------- ----------
update big_tab, chunks by rowid       CREATED

Falls einem grad kein passender Name einfällt, kann man die Funktion GENERATE_TASK_NAME verwenden, die eine interne Sequenz hochzählt:

SELECT DBMS_PARALLEL_EXECUTE.generate_task_name FROM dual;
=> TASK$_1

Der nächste Schritt ist die Unterteilung der Tabelle. Wenn der Parameter by_row auf TRUE gesetzt wird, bezieht sich die chunk_size auf die Anzahl der Zeilen, wenn er auf FALSE steht, auf die Anzahl der Blöcke.
Optimale Werte für die chunk_size muss man selber ermitteln. Je kleiner die chunk_size, desto schneller sind die Tabellenabschnitte wieder frei von Sperren:

BEGIN
  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
          task_name => 'update big_tab, chunks by rowid',
        table_owner => 'SCOTT',
         table_name => 'BIG_TAB',
             by_row => TRUE,
         chunk_size => 10000);
END;
/

SELECT task_name, status FROM user_parallel_execute_tasks;
TASK_NAME                             STATUS
------------------------------------- -------------
update big_tab, chunks by rowid       CHUNKED

Genauere Informationen über die einzelnen Abschnitte liefert die View USER_PARALLEL_EXECUTE_CHUNKS:

SELECT chunk_id, status, start_rowid, end_rowid
FROM   user_parallel_execute_chunks
WHERE  task_name = 'update big_tab, chunks by rowid'
ORDER BY chunk_id;
=>
CHUNK_ID STATUS               START_ROWID        END_ROWID
-------- -------------------- ------------------ ------------------
       2 UNASSIGNED           AAAVbmAAEAAAGegAAA AAAVbmAAEAAAGenCcP
       3 UNASSIGNED           AAAVbmAAEAAAGeoAAA AAAVbmAAEAAAGevCcP
       4 UNASSIGNED           AAAVbmAAEAAAGewAAA AAAVbmAAEAAAGe3CcP
       5 UNASSIGNED           AAAVbmAAEAAAGe4AAA AAAVbmAAEAAAGe/CcP
       6 UNASSIGNED           AAAVbmAAEAAAGfAAAA AAAVbmAAEAAAGfHCcP
....
331 Zeilen ausgewählt.

Jetzt folgt die eigentliche Ausführung:
In diesem Beispiel werden 10 parallele Prozesse (Scheduler-Jobs) gestartet, die sich jeweils einen der nicht zugeordneten (unassigned) Abschnitte vornehmen, die über den Parameter sql_stmt vorgegebene DML-Anweisung durchführen, festschreiben und zum nächsten freien Chunk übergehen. Wenn man z. B. nur 4 Prozessoren hat, laufen die Jobs natürlich teilweise seriell.
Der Quotation-Operator (q'[...]') erlaubt die Schreibweise ohne Maskierung der inneren Hochkommata:

DECLARE
  l_stmt VARCHAR2(32767);
BEGIN
  l_stmt := q'[UPDATE big_tab
               SET data_object_id = sys_context('userenv', 'sessionid'),
                      object_type = object_type||'*',
                          created = sysdate
                 WHERE rowid BETWEEN :start_id AND :end_id]';
  DBMS_PARALLEL_EXECUTE.run_task(
           task_name => 'update big_tab, chunks by rowid',
            sql_stmt => l_stmt,
       language_flag => DBMS_SQL.NATIVE,
      parallel_level => 10);
END;
/
Abgelaufen: 00:00:48.98

Die Bindvariablen :start_id and :end_id beziehen sich auf die erste bzw. letzte Rowid jedes Chunks.
Der Eintrag der Session_id über die Sys_constext-Funktion ermöglicht auch nachträglich, festzustellen, welcher Anteil der Zeilen in welcher der parallelen Sessions erledigt wurde:

SELECT data_object_id session_id, COUNT(*)
FROM   big_tab
GROUP BY data_object_id
ORDER BY data_object_id;
=>
SESSION_ID   COUNT(*)
---------- ----------
   7491384     279693
   7491385     283494
   7491386     273875
   7491387     260867
   7491388     141022
   7491389     143615
   7491390     153054
   7491391     154875
   7491392     161342
   7491393     148163

Status Informationen und Diagnose-Möglichkeiten

Ob alles glatt gegangen ist, erfährt man über die Data Dictionary-Views:

SELECT status,
       job_prefix, -- für die parallelen Scheduler-Jobs
       chunk_type,
       -- sql_stmt,
       parallel_level
FROM user_parallel_execute_tasks
WHERE TASK_NAME = 'update big_tab, chunks by rowid';
=>
STATUS     JOB_PREFIX     CHUNK_TYPE   PARALLEL_LEVEL
---------- -------------- ------------ --------------
FINISHED   TASK$_241      ROWID_RANGE              10

SELECT status, COUNT(*)
FROM user_parallel_execute_chunks
GROUP BY status;
STATUS                 COUNT(*)
-------------------- ----------
PROCESSED                   331

Aufschlussreich ist auch die Scheduler-DD-View user_scheduler_job_run_details. Hierfür muss man nur das Job-Präfix aus der View user_parallel_execute_tasks für die Job-Namen einsetzen:

SELECT job_name,
       status,
       error#,
       SUBSTR(run_duration, INSTR(run_duration,':')+1) "Dauer [Sek]",
       REGEXP_SUBSTR(actual_start_date, '[^ ]+') uhrzeit
FROM user_scheduler_job_run_details
WHERE job_name LIKE (SELECT job_prefix || '%'
                     FROM user_parallel_execute_tasks
                     WHERE task_name = 'update big_tab, chunks by rowid');
=>
JOB_NAME       STATUS         ERROR# Dauer [Sek]    UHRZEIT
-------------- ---------- ---------- -------------- ----------------
TASK$_241_6    SUCCEEDED           0 00:40          17:46:52,578000
TASK$_241_9    SUCCEEDED           0 00:40          17:46:52,781000
TASK$_241_1    SUCCEEDED           0 00:46          17:46:46,781000
TASK$_241_10   SUCCEEDED           0 00:40          17:46:52,843000
TASK$_241_2    SUCCEEDED           0 00:46          17:46:46,890000
TASK$_241_3    SUCCEEDED           0 00:46          17:46:46,953000
TASK$_241_4    SUCCEEDED           0 00:46          17:46:47,109000
TASK$_241_7    SUCCEEDED           0 00:40          17:46:52,656000
TASK$_241_8    SUCCEEDED           0 00:40          17:46:52,718000
TASK$_241_5    SUCCEEDED           0 00:40          17:46:52,578000

Nach der erfolgreichen Ausführung kann man den Task löschen:

BEGIN
   DBMS_PARALLEL_EXECUTE.drop_task('test_task');
END;

Fehlerlogging

Was macht man, wenn bei der Prozessierung Fehler auftreten? Wir bauen ein paar Fallen in die Tabelle ein. Die Spalte object_type ist vom Datentyp VARCHAR2(19). Während des Updates wird der Wert mit einem Sternchen konkateniert, also muss ich nur ein paar Einträge verlängern, so dass es während der DML-Aktion kracht:

UPDATE big_tab SET object_type = RPAD(object_type, 19, '+') WHERE MOD(id, 43) = 0;
=> 46511 Zeilen wurden aktualisiert.

In der Opens external link in new windowPL/SQL Packages and Types Reference  findet sich ein Beispiel für die Ausführung der Prozedur incl. Fehlerbehandlung. Statt RUN_TASK wird hier die Prozedur GET_ROWID_CHUNK eingesetzt:

DECLARE
  v_stmt        VARCHAR2(32767);
  v_chunk_id    NUMBER;
  v_start_rowid ROWID;
  v_end_rowid   ROWID;
  v_rows_left   BOOLEAN;
  v_errcnt      NUMBER := 0;
BEGIN
  BEGIN
     DBMS_PARALLEL_EXECUTE.drop_task('update big_tab, chunks by rowid');
  EXCEPTION
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Task bereits vorhanden, wird gelöscht');
  END;
  v_stmt := q'[UPDATE big_tab
               SET session_id = sys_context('userenv', 'sessionid'),
                  object_type = object_type||'*',
                      created = sysdate
                 WHERE rowid BETWEEN :start_id AND :end_id]';
-- Task erstellen
   DBMS_PARALLEL_EXECUTE.create_task (
       task_name => 'update big_tab, chunks by rowid');
-- Aufteilung in Chunks
   DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
          task_name => 'update big_tab, chunks by rowid',
        table_owner => 'SCOTT',
         table_name => 'BIG_TAB',
             by_row => TRUE,
         chunk_size => 10000);
  LOOP
  -- Solange Zeilen zu prozessieren sind
  -- liefert GET_ROWID_CHUNK den nächsten noch nicht zugewiesenen Chunk
     DBMS_PARALLEL_EXECUTE.get_rowid_chunk(
            task_name => 'update big_tab, chunks by rowid',
             chunk_id => v_chunk_id,
          start_rowid => v_start_rowid,
            end_rowid => v_end_rowid,
             any_rows => v_rows_left);
     EXIT WHEN v_rows_left = FALSE;
    -- Im Unterblock wird die eigentliche DML-Aktion durchgeführt
    BEGIN
      EXECUTE IMMEDIATE v_stmt USING v_start_rowid, v_end_rowid;
      -- Wenn alles glatt geht, wird der Chunk status auf 'processed' gesetzt.
      DBMS_PARALLEL_EXECUTE.set_chunk_status(
           task_name => 'update big_tab, chunks by rowid',
            chunk_id => v_chunk_id,
              status => 2 /* DBMS_PARALLEL_EXECUTE.PROCESSED */);
      EXCEPTION
        WHEN OTHERS THEN
      -- Wenn Fehler auftreten, werden sie mitsamt Fehlermeldung festgehalten
          DBMS_PARALLEL_EXECUTE.set_chunk_status(
                 task_name => 'update big_tab, chunks by rowid',
                  chunk_id => v_chunk_id,
                    status => 3 /* DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR */,
                   err_num => SQLCODE,
                   err_msg => SQLERRM);
                   v_errcnt  := v_errcnt + 1;
     END;
  -- Wenn man wie üblich pro prozessiertem Chunk committen will,
  -- kann man das an dieser Stelle tun
  -- commit;
  END LOOP;
  -- Wenn man nur fehlerfreie Transaktionen erlauben will,
  -- baut man so etwas wie diesen Block ein
  IF v_errcnt <> 0 THEN
     DBMS_OUTPUT.PUT_LINE(
        v_errcnt|| ' Fehler aufgetreten. Alles zurück auf Start');
     ROLLBACK;
  ELSE
     DBMS_OUTPUT.PUT_LINE('Task ohne Fehler abgeschlossen');
     COMMIT;
  END IF;
END;
/
-- Ausgabe
330 Fehler aufgetreten. Alles zurück auf Start

Der Eintrag in die View user_parallel_execute_chunks wird durch den Rollback-Vorgang nicht zurückgerollt:

SELECT status, COUNT(*)
FROM   user_parallel_execute_chunks
GROUP BY status;
STATUS                 COUNT(*)
-------------------- ----------
PROCESSED_WITH_ERROR        330
PROCESSED                     1

Welche Fehler aufgetreten sind, erfährt man über:

SELECT DISTICNT error_message
FROM user_parallel_execute_chunks
WHERE task_name = 'update big_tab, chunks by rowid';
Ò ORA-12899: Wert zu groß für Spalte "SCOTT"."BIG_TAB"."OBJECT_TYPE" (aktuell: 20, maximal: 19)

Der Nachteil an dieser Lösung:

  • Die Prozessierung der einzelnen Chunks bricht beim ersten Fehler ab (aber bei einer "Alles oder Nichts"-Transaktion will man ja genau das erreichen).
  • Es werden keine parallelen Scheduler-Jobs gestartet.
  • Welche Zeilen im einzelnen betroffen waren, erfährt man nicht.

Ein Fehlerlogging auf Zeilenebene incl. Parallelisierung ist allerdings mit sehr geringem Aufwand zu implementieren. Die Ausführung dauert in diesem Fall natürlich etwas länger. Für das obige Beispiel habe ich 1,48 Minuten für den Update der Tabelle big_tab mit 46511 fehlerhaften Zeilen gemessen gegenüber 49 Sekunden für die Änderung der fehlerfreien Tabelle.

Fazit

Die Ausführung von DML-Transaktionen über DBMS_PARALLEL_EXECUTE bietet auch Nutzern der Standard-Edition eine einfache Möglichkeit, größere Transaktionen ohne viel Aufwand an Zeit und Geld zu parallelisieren. Die Verwendung ist zudem wesentlich einfacher als die meisten "handgeschnitzten" Lösungen.
Wenn Sie mehr wissen wollen, dann schauen Sie doch mal in unserem Opens internal link in current windowPL/SQL II oder Opens internal link in current windowPackages-Kurs vorbei. Dort erfahren Sie,

  • wie man DBMS_PARALLEL_EXECUTE für die Parallelisierung von DELETES in Kombination mit benutzerdefinierten Funktionen oder die parallele Ausführung von Prozeduren einsetzt,
  • wie unkompliziert die Automatisierung des Package (incl. Fehlerbehandlung) sein kann,
  • welche Performance-Vorteile DBMS_PARALLEL_EXECUTE gegenüber alternativen Methoden der Parallelisierung bietet.
Parallelisierung DBMS_PARALLEL_EXECUTE

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.