Boost Oracle Query Speed with Parallel Processing: A Practical Guide

Background

Modern CPUs have many cores, but Oracle Database processes SQL statements single‑threaded by default. Enabling Oracle Parallel Processing allows SQL operations to use multiple cores, improving performance for large‑scale data workloads, especially OLAP queries.

Parallel Features

Parallel Query

Parallel DDL

Parallel DML

Typical Use Cases

Range scans on tables or indexes

Bulk INSERT, UPDATE, DELETE

Creation of tables or indexes

Collecting statistics with DBMS_STATS

RMAN backup/restore

Setting Parallelism

Parallelism can be defined at three levels; precedence is hint > session > object.

ALTER SESSION FORCE PARALLEL QUERY PARALLEL n;   -- session level

ALTER TABLE tab_name PARALLEL n;                -- object level

SELECT /*+ PARALLEL(tab n) */ * FROM tab;      -- hint level

The degree of parallelism (DOP) should not exceed the number of logical CPUs. Oracle computes a default DOP as:

-- Single‑instance
DOP = PARALLEL_THREADS_PER_CPU * CPU_COUNT

-- RAC instance
DOP = PARALLEL_THREADS_PER_CPU * CPU_COUNT * INSTANCE_COUNT

Experiment Environment

Database: Oracle 11gR2 64‑bit

CPU_COUNT = 8

PARALLEL_THREADS_PER_CPU = 1

Test Procedure and Results

Step 1 – Baseline (no parallelism)

ALTER SESSION SET STATISTICS_LEVEL=ALL;
SELECT COUNT(1) FROM edidc;

Result: 47 262 191 rows. Execution plan shows a single‑threaded INDEX FAST FULL SCAN with elapsed time ~00:00:15.52 and 351 K buffers.

Step 2 – Force parallel query at session level

ALTER SESSION FORCE PARALLEL QUERY;
SELECT COUNT(1) FROM edidc;

Result: same row count. Execution plan now contains PX COORDINATOR, PX SEND QC, PX BLOCK ITERATOR and shows DOP equal to the default (8). Elapsed time reduced to ~00:00:13.86 and buffers dropped to 25.

Step 3 – Use explicit hint

SELECT /*+ PARALLEL(4) */ COUNT(1) FROM edidc;

Result: same row count. Plan shows DOP = 4 with similar timing (~00:00:13.86).

Step 4 – Set object parallelism

ALTER TABLE edidc PARALLEL 2;
-- gather statistics if needed
SELECT COUNT(1) FROM edidc;

Result: same row count. Plan shows DOP = 2 and elapsed time ~00:00:12.93.

Observations

Parallel execution reduces elapsed time for aggregation‑heavy queries; the gain depends on the chosen DOP.

DOP should be aligned with the number of logical CPUs; exceeding it yields diminishing returns.

In OLTP environments parallelism can increase contention and resource usage, so it must be applied judiciously.