Boost Oracle Performance: Master Parallel Execution Techniques

Concept and Syntax

Parallel execution leverages multiple CPUs to run a single SQL statement concurrently, reducing overall execution time. Oracle supports parallel query, parallel DML (insert, delete, update), and parallel DDL (table and index creation).

Object‑level Parallelism

Set parallel degree on tables or indexes:

ALTER TABLE 门诊费用记录 PARALLEL 8;
ALTER INDEX 门诊费用记录_IX_登记时间 PARALLEL;

Omitting the degree lets Oracle estimate a default based on system parameters. Disable with PARALLEL 1 or NOPARALLEL, or set parallel_max_servers=0 to turn off parallel query globally.

Session‑level Parallelism

Enable or disable parallelism for the current session:

ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION DISABLE PARALLEL DDL;

Force a specific degree:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;
ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;

Check the session status:

SELECT pq_status, pdml_status, pddl_status
FROM v$session
WHERE sid = SYS_CONTEXT('USERENV','SID');

Statement‑level Parallelism

Use optimizer hints to request parallel execution for a specific statement:

SELECT /*+ PARALLEL(t1,8) */ COUNT(*) FROM 门诊费用记录 t1;
CREATE TABLE 医嘱执行时间 PARALLEL 8 AS SELECT /*+ PARALLEL(t1,8) */ * FROM 医嘱执行时间 t1;
ALTER INDEX 门诊费用记录_IX_登记时间 REBUILD PARALLEL 8;

Parallel DML requires both session‑level parallel query and DML to be enabled.

Common Pitfalls

Parallel DML without an active transaction will block subsequent queries on the modified table (ORA‑12838).

Automatic primary‑key index creation is not parallel; a workaround is to create the unique index with PARALLEL and then add the constraint manually.

When rebuilding indexes in parallel, the parameter parallel_execution_message_size may need to be increased (e.g., to 8192) to avoid ORA‑00600 errors.

Performance Experiments

During a historical data export, parallel query and DML were unsuitable because most queries used index range scans. However, an accidental parallel DDL on an index caused full‑table scans and severe slowdown, illustrating that parallelism can degrade performance if misapplied.

Direct‑path inserts with the APPEND hint and NOLOGGING reduced load time up to fivefold.

Parallel DDL for index rebuilds cut execution time by more than 50% on a 32‑CPU, 32 GB RAM test system (36 min → 16 min). The optimal parallel degree was 8 due to I/O limits.

Parallel statistics collection reduced total time from 1.5 hours to about 30 minutes for the ZLHIS schema.

Conclusion

Parallel execution is an OLAP‑oriented feature that can deliver dramatic speedups when long‑running tasks have ample idle CPU, memory, and I/O bandwidth. It should be used cautiously in OLTP environments, as excessive resource consumption may impact other users.