Implementing Parallel Stored Procedures in Oracle 10g with DBMS_SCHEDULER

Purpose

This document describes a DIY method to run heavy‑weight PL/SQL stored procedures in parallel on Oracle 10g, thereby increasing throughput and fully utilizing multi‑CPU servers.

Background

Large computational jobs (e.g., data consolidation, receivable verification) require row‑by‑row PL/SQL processing, causing massive I/O and DML.

The server has many CPUs and ample disk space, but default Oracle execution uses only one CPU.

Traditional single‑threaded Java programs cannot exploit Oracle’s parallel query capabilities.

Parallelism is achieved by splitting the workload into N non‑overlapping groups and launching N copies of a PL/SQL sub‑program.

Scope

The solution applies to any Oracle 10g project that experiences high throughput demands and strict execution‑time limits.

Glossary

DBMS_SCHEDULER : Oracle job scheduler (available from Oracle 10g).

READ LOCK : Row‑level exclusive lock (available in all Oracle versions).

Autonomous Transactions : Self‑contained transactions (available in all Oracle versions).

Dynamic SQL : SQL built and executed at runtime (available in all Oracle versions).

Overall Architecture Design

Design Principles

Provide a business‑agnostic, highly reusable component.

Minimize code changes; configuration should satisfy diverse requirements.

Include mechanisms to prevent duplicate execution.

Offer a user‑friendly interface for progress display.

Balance implementation effort with execution efficiency.

Methodology

The parallel execution model divides a large task into smaller parts and runs them concurrently in separate sessions (e.g., P001‑P004). A dispatcher assigns work based on each session’s load, and each session invokes a predefined business procedure with its parameters. Results are committed inside the business procedure. This approach dramatically improves throughput for high‑volume, performance‑critical operations, but it requires sufficient system resources and should be used during off‑peak periods.

System Components

Front‑end User : Initiates scheduling and monitors execution.

Task List (SC01) : Regular table storing task parameters as ‘|’‑delimited strings, plus exception and progress info.

Process Control : Uses DBMS_SCHEDULER to spawn the required number of service processes and to manage repeat execution or emergency termination.

Task Reader : Leverages Oracle transaction and lock features to ensure each service process reads a unique task row.

Business Procedure : Developer‑provided stored procedure invoked dynamically with the task name and parameter count.

Service Process : Oracle‑managed sessions that (1) obtain parameters via Task Reader and (2) call the Business Procedure.

Data Store : Persists results generated by the business procedure.

Process Monitor : Provides real‑time and final execution status to the front‑end user.

System Interface Design

Development Interfaces

PKG_多线程服务.PRC_进程控制(PRM_PROGRAM, PRM_PARMCOUNT)

– Starts the parallel service for a given business procedure. PKG_多线程服务.PRC_进程停止 – Immediately stops all running service processes. PKG_多线程服务.PRC_调试使用(PRM_PROGRAM, PRM_PARMS) – Debug interface; parameters must match the business procedure exactly.

Task List (SC01)

Developers insert tasks into SC01 using ‘|’‑separated parameters. While the service runs, no other module may modify SC01. A safeguard query must be executed before any DML on SC01:

SELECT COUNT(*) INTO N_EXISTS FROM USER_SCHEDULER_JOBS S
WHERE S.STATE = 'RUNNING'
AND S.JOB_ACTION = 'PKG_多线程服务.PRC_服务进程';

After inserting a correct SC01 record, invoke the appropriate interface to start processing.

User Display Interfaces

Front‑end users query the following views:

SC02 – Run Monitoring View

CREATE OR REPLACE VIEW SC02 AS
SELECT S.JOB_CREATOR AS creator,
       S.JOB_NAME AS thread_name,
       S.JOB_ACTION AS procedure_name,
       S.LAST_START_DATE AS start_time,
       S.COMMENTS AS remarks,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '0') AS pending,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '1') AS success,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '-1') AS failure
FROM USER_SCHEDULER_JOBS S
ORDER BY S.JOB_NAME;

SC03 – Run Result View

CREATE OR REPLACE VIEW SC03 AS
SELECT S.OWNER AS user_name,
       S.JOB_NAME AS thread_name,
       S.STATUS AS thread_status,
       S.LOG_DATE AS log_time,
       S.ACTUAL_START_DATE AS actual_start,
       S.RUN_DURATION AS duration,
       S.SESSION_ID AS session_id,
       S.SLAVE_PID AS process_id,
       S.ADDITIONAL_INFO AS remarks,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '0') AS pending,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '1') AS success,
       (SELECT COUNT(*) FROM SC01 WHERE THREAD_NAME = LOWER(S.JOB_NAME) AND STATUS = '-1') AS failure
FROM USER_SCHEDULER_JOB_RUN_DETAILS S
ORDER BY S.JOB_NAME;

Database Structure Design

Table Design

Task dispatch table SC01 stores task parameters (pipe‑delimited) and execution status.

View Design

See the CREATE VIEW statements for SC02 and SC03 above.

Data Security

Required privileges for the service user (example: AHSIMIS):

GRANT MANAGE SCHEDULER TO AHSIMIS;
GRANT CREATE ANY JOB TO AHSIMIS;

Open Issues

The current implementation does not support cross‑instance execution in RAC environments.

Illustrations