Als PDF Downloaden!

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