Tipps & Tricks: April 2013 Bereich: PL/SQL Erstellung: 04/2013 EF Versionsinfo: 11.2 Letzte Überarbeitung: 04/2013 EF Parallelisierung von DML-Operationen mit DBMS_PARALLEL_EXECUTE IN 11.2 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. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 1 von 8 Rückgabewert Bedeutung CREATED Ein Task wurde erstellt CHUNKING Die Tabelle wird in Chunks aufgeteilt, CHUNKED Die Tabelle wurde in Chunks aufgeteilt, aber die Prozessierung hat noch nicht begonnen CHUNKING_FAILED Es gab einen Fehler bei der Aufteilung PROCESSING Prozessierung läuft CRASHED Einer der parallelen Prozesse oder die gesamte Datenbank ist während der Ausführung der Transaktion abgestürzt. FINISHED Alle Tabellenabschnitte wurden fehlerfrei prozessiert FINISHED_WITH_ERROR Alle 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 big_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 ------------------------------------- ---------Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 2 von 8 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: Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 3 von 8 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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 4 von 8 ---------- -------------- ------------ -------------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. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 5 von 8 In der PL/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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 6 von 8 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 Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 7 von 8 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 PL/SQL II oder Packages-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. Muniqsoft GmbH Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40 IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0 Seite 8 von 8