Parallel processing in PSQL reduces the turnaround time of batch work on a machine, with elegant error handling.
Invantive PSQL now offers parallel processing: the composite statement parallel ... branch ... end parallel for heterogeneous blocks side by side, and a parallel clause on both for-loop variants for homogeneous work per row.
The parallel Statement
The parallel statement allows multiple PSQL blocks to be executed simultaneously, each introduced with branch and an optional label. An (optional) number after parallel indicates the maximum number of parallel executions, as in:
begin
parallel 2
branch <<first>>
begin
dbms_output.put_line('first done');
end
branch <<second>>
begin
dbms_output.put_line('second done');
end
end parallel;
end;
The output reads, with the order of the two lines following the completion order and therefore may vary:
first done
second done
Without save exceptions, a failing branch stops the start of not yet begun branches; running branches complete their work, and then the original exception is re-thrown, including the name of the failing branch. Existing error handling thus remains functional.
With save exceptions, all branches are executed; if at least one fails, a collective exception parallel_error follows. Individual errors can be found via the table function dbms_parallel.saved_exceptions().
Parallel For-loops
It is now also possible to process results from a for loop in parallel as with:
...
for r in
( select ...
)
parallel 8
save exceptions
loop
...
end loop;
...
dbms_lock
Synchronization between parallel branches is possible with new features of dbms_lock (see Nieuwe vergrendelingsfuncties in dbms_lock: request, release, is_acquired en held_locks).
dbms_output
The dbms_output lines of a parallel action are treated as a single output: they are buffered per branch and appear as a whole once the branch is done.
Example: Processing Invoices in Parallel with Restart Capability
The following example shows the pattern for financial processing per item: parallel over the rows, attempting all rows with save exceptions, summarising any failures afterwards, and restart capability via a status filter so a repeated run only picks up the remainder:
declare
l_cnt number;
begin
create or replace table invoices@InMemoryStorage
as
select 1 invoice_id, 'OPEN' status from dual@DataDictionary
union all
select 2 invoice_id, 'OPEN' status from dual@DataDictionary
union all
select 3 invoice_id, 'OPEN' status from dual@DataDictionary
;
begin
for r in
( select fct.invoice_id
from invoices@InMemoryStorage fct
where fct.status = 'OPEN'
)
parallel 8
save exceptions
loop
--
-- Processing per invoice; invoice 2 deliberately fails here.
--
declare
l_x number;
begin
l_x := 1 / (r.invoice_id - 2);
update invoices@InMemoryStorage
set status = 'PROCESSED'
where invoice_id = r.invoice_id
;
end;
end loop;
exception
when parallel_error
then
l_cnt := 0;
for f in
( select instance
from table(dbms_parallel.saved_exceptions())
)
loop
l_cnt := l_cnt + 1;
end loop;
dbms_output.put_line(l_cnt || ' invoice(s) not processed; a next run will pick up the remainder.');
end;
drop table invoices@InMemoryStorage;
end;
The output reads:
1 invoice(s) not processed; a next run will pick up the remainder.
Rate Limiting
Parallelism accelerates work with waiting time, such as calls to cloud services, and heterogeneous sections side by side. The throughput to a single platform remains limited by that platform’s limits; those limits remain under parallel PSQL fields.
See Also
See also the following topics:
- Parallel Statement (pSqlParallelStatement) - Invantive Product Documentation
- dbms_parallel - Invantive Product Documentation
- dbms_lock - Invantive Product Documentation
- Nieuwe vergrendelingsfuncties in dbms_lock: request, release, is_acquired en held_locks
Availability
The new functionality is available from release 27.0.