Appendix B: Parallel Processing and Scheduled Jobs
B.1 New Interface: parallel_manager.runjob
We’ll define a generic entry point:
parallel_manager.runjob(
p_job_name IN VARCHAR2,
p_max_jobs IN PLS_INTEGER,
p_job_run_limit IN VARCHAR2 -- number or 'AUTO'
);
B.1.1 Semantics
- p_job_name
Logical name of the parallel-capable job, e.g. 'PERSON_MERGE'.
The manager assumes this job knows how to run in parallel.
- p_max_jobs
Total number of logical work units / slices / buckets.
Example:
- 8 buckets of person_id hash
- 16 partitions of a large file
- p_job_run_limit
Maximum number of concurrent worker jobs. Can be:
- A number as text, e.g. '4', '8'
- The literal string 'AUTO', which tells the manager to compute a safe concurrency threshold.
Internally, parallel_manager.runjob will:
- Normalize p_job_run_limit:
- If numeric (e.g. '4'), use that directly.
- If 'AUTO', call an internal function to compute a safe concurrency value.
- Insert/record metadata for this run (job name, max_jobs, run_limit).
- Create up to effective_run_limit child jobs immediately.
- As jobs complete, it will start more until all p_max_jobs slices are processed.
B.2 “AUTO” Concurrency: How the Manager Chooses a Limit
You wanted "AUTO" to trigger a calculation. There’s no magic built-in, but we can define a simple, explainable heuristic that you can document in the book.
Example internal function (conceptual, not final code):
FUNCTION compute_auto_limit(
p_job_name IN VARCHAR2,
p_max_jobs IN PLS_INTEGER
) RETURN PLS_INTEGER;
Possible rule of thumb:
- Read job_queue_processes (upper bound on concurrent scheduler jobs):
- SELECT value
- INTO l_job_queue_processes
- FROM v$parameter
- WHERE name = 'job_queue_processes';
- Read CPU count (optional refinement):
- SELECT value
- INTO l_cpu_count
- FROM v$parameter
- WHERE name = 'cpu_count';
- Compute something like:
- l_base := LEAST(p_max_jobs, l_job_queue_processes);
- l_safe := GREATEST(1, TRUNC(l_base / 2)); -- run at ~50% of theoretical
- Optionally check how many jobs of this type are already running and adjust down if needed.
So "AUTO" might resolve to something like:
“Up to min(p_max_jobs, job_queue_processes/2) concurrent workers for this job type.”
You can tune the rule and document it as:
“AUTO uses database job and CPU capacity to pick a conservative concurrency level that balances speed and resource usage.”
B.3 Letting the Job Ask: “What Parallel Slice Am I?”
You said:
“There should be a method from the parallel_manager which can return the parallel number to whatever job is being called…”
Perfect. That’s the second half of the contract.
We add a helper function the worker job can call at runtime:
FUNCTION get_worker_info
RETURN parallel_manager.worker_info_t;
Where worker_info_t might be a record like:
TYPE worker_info_t IS RECORD (
job_name VARCHAR2(100),
run_id NUMBER,
worker_id PLS_INTEGER, -- 1 .. max_jobs
max_jobs PLS_INTEGER,
run_limit PLS_INTEGER -- effective concurrency
);
B.3.1 How does this work?
- Each child job created by parallel_manager.runjob gets:
- A worker ID (from 1 to p_max_jobs)
- The manager writes this mapping into a control table, e.g.:
- Inside the child job, the code calls:
- l_info := parallel_manager.get_worker_info;
get_worker_info can use:
l_scheduler_job_name := SYS_CONTEXT('USERENV', 'JOB_NAME');
to look itself up in pm_job_run and return its worker_id, max_jobs, etc.
Now the job knows:
- “I am worker 3 of 8 for PERSON_MERGE, run 42, with effective concurrency 4.”
That’s all it needs to choose its slice.
B.4 Specification of the Called Job
You asked:
“We will need to define the specification of the called job.”
Here’s a clean contract you can put in the book.
B.4.1 Job Entry Point
Every parallel-capable job must implement a standard entry procedure, for example:
PROCEDURE run_parallel_job(p_job_name IN VARCHAR2);
Or more concretely, you might decide on something like:
PACKAGE person_merge_job IS
PROCEDURE run(p_job_name IN VARCHAR2);
END;
The scheduler job associated with p_job_name = 'PERSON_MERGE' calls this run procedure.
B.4.2 Job Responsibilities
Inside run(p_job_name) the job must:
- Call parallel_manager.get_worker_info to discover:
- any other metadata it needs
- Use this to choose its slice of data, e.g.:
- l_info := parallel_manager.get_worker_info;
- -- Example: hash on primary key using worker_id and max_jobs
- WHERE MOD(ORA_HASH(person_id), l_info.max_jobs) = l_info.worker_id - 1;
- Perform its work (e.g., MERGE) only on that slice.
- Commit appropriately and update status if required.
B.4.3 Invariants (Rules)
All parallel jobs must follow these rules:
- Disjoint slices: each worker must operate on a disjoint subset of rows (hash, range, partition, etc.).
- Idempotent per slice: re-running the same slice should not corrupt data (so retries are safe).
- No cross-slice shared state unless managed carefully (e.g., no “global” cursor without proper partitioning).
parallel_manager will never inspect business logic. It only:
- Creates child jobs with worker IDs
- Controls how many are running
- Allows the job to look up its own worker info
B.5 How This Fits the Earlier Story
Putting it all together:
- You have CRM change data for the person table.
- You want to run parallel MERGEs with:
- Hashed slices on person_id
- Direct-path inserts for new rows
- Parallel jobs that don’t stomp on each other
- p_job_name => 'PERSON_MERGE',
- p_job_run_limit => 'AUTO'
- Computes an effective concurrency (say 4)
- Creates 8 scheduled jobs and starts up to 4 of them
- Records worker_id / max_jobs mapping
- Calls parallel_manager.get_worker_info
- Sees it is, say, worker 3 of 8
- Processes only rows in bucket 3/8
- Performs MERGE (update + direct-path insert)
Result:
- High-performance parallel MERGE
- No hot-block or lock fights
- Centralized concurrency control
- Generic manager + job-specific logic cleanly separated
B.6 What can we measure to approximate “system effort”?
There’s no single “system load” number in Oracle, but you can get a pretty good picture using a combination of dynamic performance views:
B.6.1 PGA usage – how much private memory is in play?
From earlier, you already use:
SELECT name, value
FROM v$pgastat
WHERE name IN (
'total PGA inuse',
'total PGA allocated',
'maximum PGA allocated',
'aggregate PGA target parameter',
'aggregate PGA auto target'
);
Relevant ideas:
- total PGA inuse – memory currently being used by all sessions
- aggregate PGA target parameter – soft target (PGA_AGGREGATE_TARGET)
- aggregate PGA auto target – how much Oracle is currently willing to use automatically
You can compute a PGA pressure ratio, for example:
SELECT
(total_inuse / aggregate_target) AS pga_pressure
FROM (
SELECT
MAX(CASE WHEN name = 'total PGA inuse'
THEN value END) AS total_inuse,
MAX(CASE WHEN name = 'aggregate PGA target parameter'
THEN value END) AS aggregate_target
FROM v$pgastat
);
If pga_pressure is, say, 0.8 (80%), you might decide not to start more jobs.
B.6.2 Session and process limits – how crowded is the instance?
v$resource_limit can tell you how close you are to limits on sessions, processes, etc.:
SELECT resource_name,
current_utilization,
max_utilization,
limit_value
FROM v$resource_limit
WHERE resource_name IN ('sessions', 'processes');
If current sessions or processes are near the limit, AUTO should be conservative.
B.6.3 CPU pressure – how busy are we?
Oracle exposes OS-level stats via v$osstat, and approximate CPU load via v$sysmetric or v$sysmetric_summary. For example:
- v$osstat – CPU count, busy time, idle time
- v$sysmetric – metrics like “Host CPU Utilization (%)”, “Database CPU Time Ratio”, etc.
A typical query (example pattern, not carved in stone):
SELECT metric_name, value
FROM v$sysmetric
WHERE metric_name IN (
'Host CPU Utilization (%)',
'Database CPU Time Ratio'
);
If host CPU utilization is already ~85–90%, starting more jobs is usually a bad idea.
B.6.4 How many of our jobs are already running?
You can scope this to your own parallel framework:
SELECT COUNT(*) AS running_workers
FROM dba_scheduler_running_jobs
WHERE job_name LIKE 'PERSON_MERGE_%'; -- or your naming convention
That lets AUTO reason about “we already have N workers; is it safe to add one more?”.
B.7 Tying this back to the earlier PGA / LIMIT discussion
Earlier in your Appendix, you explain how LIMIT in a BULK COLLECT loop lets you control PGA consumption per batch. That idea carries nicely into AUTO concurrency:
- Inside each worker, LIMIT controls per-session memory usage.
- Across workers, the number of concurrent jobs controls system-wide memory and CPU pressure.
So AUTO can loosely think like this:
“Each worker is using roughly X MB of PGA (based on earlier tests / estimates), and total PGA in use vs target is already Y%. If I start another worker, will I push the system into a dangerous zone?”
You won’t get exact per-session PGA from v$pgastat (it’s aggregate), but you can:
- Benchmark: run 1 worker, measure rise in total PGA inuse and CPU; that gives a rough per-worker impact.
- Use that as a sizing guideline:
- If 4 workers get you to ~60% PGA target, then 8 will likely be ~near or over 100%.
- AUTO can aim to keep PGA + CPU under, say, 70–80% of configured targets.
B.8 A realistic AUTO algorithm for parallel_manager
Here’s a design you can describe in the book as “one workable approach”:
B.8.1 Step 1 – Base capacity from static limits
Inside parallel_manager.compute_auto_limit:
- Get job_queue_processes
- Get cpu_count
- Derive a hard upper bound, e.g.:
- base_limit = min(p_max_jobs,
- job_queue_processes,
- 2 * cpu_count) -- rule of thumb
B.8.2 Step 2 – Check current database load at decision time
Before starting each new worker, the manager queries:
- v$pgastat – compute pga_pressure = total_inuse / aggregate_target
- v$sysmetric – host CPU utilization (if available)
- dba_scheduler_running_jobs – how many of this job are already running
B.8.3 Step 3 – Decision rule for starting another job
Pseudo-logic:
IF running_workers >= base_limit THEN
-- already at or above hard cap
do_not_start_new_job;
ELSIF pga_pressure > 0.8 THEN
-- PGA usage too high
do_not_start_new_job;
ELSIF host_cpu_utilization > 85 THEN
-- CPU too busy
do_not_start_new_job;
ELSE
-- Looks safe
start_one_new_worker;
END IF;
You can phrase this in your book as:
“AUTO mode does a quick health check of PGA and CPU metrics before launching each new worker. If the system is below configured thresholds (for example, PGA under 80% of target and host CPU under 85%), it allows another job to start, up to a base cap derived from job_queue_processes and CPU count.”
B.8.4 Step 4 – “Always below a percentage of acceptable consumption”
That’s your safety guarantee:
- You pick thresholds like:
- pga_pressure_threshold = 0.8 (80%)
- AUTO uses these thresholds every time it tries to spawn another job.
“The number of jobs will never grow to a point where aggregate PGA usage or CPU consumption exceeds our safety thresholds—AUTO will simply stop spawning more workers.”
B.9 Caveats you probably want to mention in the book
To be honest with readers (and with yourself as the architect), it’s worth noting:
- These metrics are coarse-grained.
v$pgastat and v$sysmetric report overall instance state, not per-job. Other workloads on the system also affect these numbers.
- AUTO is heuristic, not perfect.
System load can change quickly. By the time you measure and spawn a job, other programs could start or stop. AUTO provides a good approximation, not a hard guarantee.
- Multi-tenant / busy environments complicate this.
In shared environments, your jobs are not the only ones consuming PGA and CPU, so your thresholds might need to be conservative.
- Over-tuning can hurt throughput.
If you set thresholds too low, AUTO will be so cautious that you lose most of the benefit of parallelism. You might suggest default thresholds (e.g. 75–80% PGA, 80–85% CPU) and advise DBAs to tune based on real measurements.
B.10 How to describe this in your book
You can summarize AUTO like this:
In AUTO mode, the parallel manager does more than just look at configuration parameters. Before each new worker job is started, it queries Oracle’s dynamic performance views to see how “busy” the instance is. It looks at aggregate PGA usage relative to PGA_AGGREGATE_TARGET, host CPU utilization, and how many workers are already running for the requested job. If starting another worker would push PGA or CPU above configurable safety thresholds, the manager pauses further job creation. In this way, AUTO mode keeps parallel execution within a safe fraction of the system’s capacity instead of blindly launching all configured workers at once.
Robert Diekmann's Dev Demo Platform