The speed of synchronize has been increased by mutating data and making actions executable in parallel.
The synchronize statement retrieves both tables, determines the differences, and then applies them row by row. Mutating the data and/or executing actions via triggers often determines the duration. The new parallel clause speeds up these activities by executing them simultaneously where possible.
The parallel clause
The clause is placed after batchsize and before continue on first ... errors as in:
synchronize facturen_soll@InMemoryStorage
to facturen_ist@InMemoryStorage
with insert
identified
by factuurnummer
batchsize 1
parallel 8
...
Number of processors
The session setting invantive-sql-psql-max-parallel limits concurrent processing, just like with parallel PSQL constructs.
By default, processing now takes place in parallel. The value parallel 1 executes the statement sequentially as before.
There is no parallel processing if a transaction is open on the session and if the statement itself is already running within a parallel PSQL construct. This prevents an explosion of parallelism.
Other logic, such as the number of rows with errors after which processing stops, remains unchanged.
Triggers on processors
Triggers are also executed in parallel. Reading variables outside the trigger is possible, but writing requires the use of dbms_atomic. With dbms_lock, concurrency can be limited for critical sections.
The output of dbms_output is printed per completed batch. This ensures that the output of handling a batch does not intermingle.
Speed limitation of platform
Parallel execution is limited to the processor. If reading and/or writing is done on a (cloud) platform, then those actions are subject to the limitations of that platform.
If the actual limitation in terms of throughput for parallel was already at a platform due to, for example, a rate limiter, parallel will not add any speed. If there was still room, that space will be further filled.
Tests show that execution scales almost linearly until all processors are busy, assuming no further impact from speed limitations on underlying platforms.
Example: parallel mutating
In this example, mutations for three rows are processed in parallel:
create or replace table testsync_soll@InMemoryStorage
as
select 1 id
, 'x' waarde
union all
select 2
, 'X'
union all
select 3
, 'y'
;
create or replace table testsync_ist@InMemoryStorage
as
select 1 id
, 'z' waarde
;
synchronize testsync_soll@InMemoryStorage
to testsync_ist@InMemoryStorage
with insert
or
update all
except rowid$
identified
by id
parallel 4
;
select tsi.id
, tsi.waarde
from testsync_ist@InMemoryStorage tsi
order
by tsi.id
See also
- https://documentation.invantive.com/Invantive+UniversalSQL/Packages/dbms_atomic
- https://documentation.invantive.com/Invantive+UniversalSQL/Packages/dbms_lock
- Meer datadoorvoer dankzij "parallel" PSQL constructies
Availability
The new functionality is available from release 27.0.