4.2 Parallelisum and scheduled jobs for ETL and Data warehousing
PL/SQL allows several different ways of doing parallel operations. One important method is to use autonomous scheduled jobs using dbms_scheduler and a concept of a parallel manager to run operations in parallel. An example of this is running a table ETL where many tables are being updated from staged files that have been loaded. This is a good application of parallelism in the database because tables can be merged with new information in parallel as long as the inserts and updates are accessing the tables from different table blocks in memory.
4.2.1 Parallel MERGE Processing Using Hash-Based Partitioning of CRM Change Data
(Example: PERSON table from Appendix 2)
When importing CRM change files that contain many modified or newly added rows, a common challenge is supporting high-volume concurrent MERGE operations without triggering Oracle write contention. To achieve this, the incoming change set is divided into eight logical groups by hashing the primary key (PERSON_ID) into eight buckets. A scheduler job is launched for each bucket, resulting in eight concurrent MERGE operations running in parallel.
This approach significantly reduces the risk of hot block contention, ITL contention, index leaf block hotspots, and redo/undo bottlenecks—all of which commonly appear in single-threaded or poorly partitioned data-loading pipelines.
4.2.1.1 How Hash-Based Partitioning Works
For each incoming CRM person record:
bucket := MOD(person_id_hash, 8);
Each row deterministically maps to exactly one of the eight buckets.
Each bucket is processed by its own scheduled job, performing:
MERGE INTO person p
USING staging_person s
ON (p.person_id = s.person_id)
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...
This maximizes parallelism while preventing write targeting of the same row by multiple sessions.
4.2.1.2 Why Hash Partitioning Eliminates Hot Block Contention
4.2.1.2.1 Updates Are Targeting Different Rows
Because each PERSON_ID hash maps to one bucket:
- Job 1 updates only the rows whose person_id hash = 0
- Job 2 updates only the rows whose person_id hash = 1
- Job 8 updates only the rows whose person_id hash = 7
Therefore, no two jobs update the same block of the PERSON table, since the rows are distributed across table blocks by their primary key order.
This nearly eliminates:
- enq: TX – allocate ITL entry
If you updated PERSON_IDs 1000 through 2000 in the same job, you would likely touch the same blocks. But because the hash spreads the IDs evenly, row access patterns become statistically independent.
4.2.1.2.2 Inserts Avoid “Right-Most Index Hot Block” Problems
A normal identity column generates sequential IDs.
Sequential inserts all land in the right-most leaf block of the index, which becomes a hotspot.
But here, CRM changes come with existing PERSON_ID values, not newly generated values. These PERSON_IDs are non-sequential, so:
- inserts distribute across the B-tree index
- there is no right-end leaf block hotspot
- index splits distribute more evenly
Additionally, hashing ensures that each job inserts into disjoint primary key ranges.
4.2.1.2.3 Direct-Path INSERT Removes Further Contention
Your INSERT path uses:
INSERT /*+ APPEND */ -- direct path
Direct-path inserts:
- load above the High Water Mark
- bypass most buffer cache operations
- write new blocks without competing for existing ones
This eliminates:
- traditional row-level locking issues
Even eight concurrent direct-path jobs will simply allocate new extents, each session writing into its own segment region.
This is the most effective technique to avoid write contention when loading new rows.
4.2.1.3 Why MERGE Still Works Efficiently in Parallel
MERGE contains two paths:
4.2.1.3.1 UPDATE path
- Locks only the rows being updated
- Buckets ensure distinct row sets
- Hashes prevent overlapping updates
4.2.1.3.2 INSERT path
- Uses direct-path, so writes to independent extents
- No competition for index leaf blocks
- No competition for table blocks
Both paths achieve true parallelism without locking conflicts.
4.2.1.4 How this Strategy Avoids the Classic Oracle Contention Patterns
The result: eight jobs perform nearly linearly scalable MERGE operations.
4.2.1.5 Why Hash Partitioning Is Superior to Range Partitioning for MERGE
Because CRM updates involve random PERSON_IDs, not sequential ranges:
- Hashing ensures statistically equal distribution
- Even if PERSON_IDs are clustered, hash breaks clustering apart
- Reduces skew, which is essential for scalable parallel MERGE operations
If range partitioning were used (e.g., 1–10000, 10001–20000), workload might cluster into one partition. Hashing avoids this entirely.
4.2.2 Parallel MERGE with a Parallel Manager Package
In the earlier example, we split a large CRM change file into eight logical buckets by hashing the person_id and launching eight scheduled jobs, each running a MERGE into the person table. This design solves two problems at once:
- Performance – multiple jobs process data in parallel.
- Contention – hashing the key ensures each job owns a disjoint subset of rows, which avoids updates to the same blocks and reduces hot block contention.
Because the incoming person_id values are not simple identity values, they distribute across table and index blocks rather than piling up on the right-most leaf block. On the insert side, we can further reduce contention by using direct-path inserts (INSERT /*+ APPEND */ inside the MERGE’s insert branch), which allocate new blocks above the high water mark instead of fighting over existing blocks. On the update side, each job only touches its own hashed subset of person_id values, so locks and ITL usage are naturally spread out. The result is high-throughput MERGE processing with minimal block-level contention.
In practice, though, we rarely want every caller to hand-craft DBMS_SCHEDULER calls and guess how many jobs are “safe” to run. That is where a parallel manager package becomes useful.
4.2.2.1 The Parallel Manager’s Role
The parallel manager is a PL/SQL package that centralizes all the logic for launching and controlling parallel jobs. Conceptually, it does three things:
- Encapsulates job creation
Callers do not interact with DBMS_SCHEDULER directly. Instead, they call something like:
- parallel_mgr.submit_person_merge(
- p_bucket_count => 8,
- p_max_concurrent => 4
- );
The package is responsible for creating the individual bucket jobs, passing the right hash range or bucket id to each job, and setting job names and metadata consistently.
- Tracks which jobs are deployed and running
The package typically owns a small control table, e.g. parallel_job_control, where it stores:
- job type (e.g. PERSON_MERGE)
- bucket or partition identifier
- current status (SUBMITTED / RUNNING / COMPLETED / FAILED)
- start/end timestamps and row counts, if desired
This makes it easy to see at a glance which jobs are still running and what work they did.
- Enforces a maximum concurrency limit
The caller might specify p_max_concurrent => 4 even if there are 8 logical buckets. The parallel manager will:
- submit up to 4 jobs in RUNNING state
- monitor when a job finishes (by polling DBA_SCHEDULER_RUNNING_JOBS or its own control table)
- then start the next waiting job until all 8 buckets are processed
This lets you define more work units than the hardware can truly run at once, while throttling the actual number of concurrent jobs to match the system’s capacity.
4.2.2.2 Can the Parallel Manager Auto-Sense Load?
Oracle does not provide a single built-in “give me the perfect job count” function, but your parallel manager can make reasonable decisions by querying standard views:
- How many scheduler jobs are already running?
- FROM dba_scheduler_running_jobs
- WHERE job_name LIKE 'PERSON_MERGE_%';
You can use this to enforce your own cap, e.g. “only start a new job if fewer than 4 are currently running.”
- What is the configured background job capacity?
- WHERE name = 'job_queue_processes';
This tells you the maximum number of job queue processes Oracle will run. Your p_max_concurrent should never exceed this value.
- How busy is the PGA / memory / parallel infrastructure?
For more advanced tuning you can look at:
- v$resource_limit for sessions/processes limits
- v$osstat (and OS tools) for CPU pressure
There isn’t a built-in formula like “if load > X then run ≤ N jobs”, but you can implement simple heuristics in your parallel manager, such as:
- if total PGA allocated is close to aggregate PGA target, do not start new jobs
- if number of PERSON_MERGE jobs already running ≥ threshold, queue the rest
In other words, your parallel manager becomes a lightweight scheduler on top of DBMS_SCHEDULER, enforcing rules such as:
- “Never run more than 4 PERSON_MERGE jobs at once.”
- “Only start new jobs if total running jobs of this type < min(job_queue_processes, configured limit).”
- “If PGA usage is above a threshold, pause starting new jobs until usage drops.”
You were already doing the hardest and most important part: splitting the work by hash to avoid contention. The parallel manager formalizes the operational side:
- Who starts the jobs? – the manager.
- How many can run at once? – a configured limit, optionally informed by v$parameter and DBA_SCHEDULER_RUNNING_JOBS.
- How do we know what’s running and what finished? – the manager’s control table and views on the scheduler.
That combination—hashed work units + a concurrency-aware parallel manager—is a clean pattern you can present in the book as a practical way to scale MERGE-based ETL without overloading the database or running into hot block and locking problems.
Robert Diekmann's Dev Demo Platform