Database Reference
In-Depth Information
time required to purge. The parallel threaded purge is functionally the same as
the single-threaded purge with one performance advantage: it distributes the
workload across multiple jobs to fully utilize host resources and optimizes the
amount of data that can be deleted in a period. The parallel purge script uses a
dbms_scheduler
package to spawn multiple purge jobs, with each job work-
ing on a subset of data. This procedure is designed to purge large Dehydration
Stores housed on high-end database nodes with multiple CPUs and a good I/O
subsystem. It is recommended that this procedure is executed during non-peak
times as it acquires a lot of resources and may contend with normal online op-
erations. Determining the optimal number of jobs to spawn will require constant
on-site testing and tuning.
As a rule of thumb, the number of jobs should not exceed the number of CPUs
on the node by more than one. For example, on a database box with 4 CPUs,
the degree of parallelism can be set to a value of 1 to 4 to match the number of
CPUs. The following script depicts the use of parallel purging:
DECLARE
MAX_CREATION_DATE TIMESTAMP;
MIN_CREATION_DATE TIMESTAMP;
RETENTION_PERIOD TIMESTAMP;
BEGIN
MIN_CREATION_DATE := TO_TIMESTAMP(
'2010-01-01'
,'YYYY-MM-DD');
MAX_CREATION_DATE := TO_TIMESTAMP(
'2011-08-06'
,'YYYY-MM-DD');
RETENTION_PERIOD := TO_TIMESTAMP(
'2011-08-06'
,'YYYY-MM-DD');
SOA.DELETE_INSTANCES_IN_PARALLEL(
MIN_CREATION_DATE => MIN_CREATION_DATE,
MAX_CREATION_DATE => MAX_CREATION_DATE,
BATCH_SIZE =>
10000
,
MAX_RUNTIME =>
60
,
RETENTION_PERIOD => RETENTION_PERIOD,
DOP => 3,
MAX_COUNT =>
1000000
,
PURGE_PARTITIONED_COMPONENT => FALSE);
END;
