Oracle Parallel Execution – Enable, Disable, and Tune CPU Usage

Leave a reply

A Guide to Configuring Oracle Parallel Execution Safely

Parallel execution in Oracle is one of the most powerful features for speeding up queries, index creation, and data loading. But if not configured properly, it can easily overload your server’s CPU and slow down other workloads. This blog will explain how Oracle parallelism works, how much CPU it consumes, and how to configure it safely.

🔹 What is Oracle Parallel Execution?

Normally, Oracle runs a query using a single process. With parallel execution, Oracle breaks the workload into smaller chunks and assigns them to parallel slave processes.

For example:

  • A query with Degree of Parallelism (DOP) = 8 will use 8 slave processes plus 1 coordinator.
  • Each slave can use 1 CPU core if available.

This means Oracle can use multiple CPUs at the same time for a single operation, dramatically reducing query time.

🔹 How Much CPU Does Parallelism Use?

The CPU usage depends on:

  1. DOP (Degree of Parallelism)
    DOP=4 → up to 4 CPUs DOP=16 –> up to 16 CPUs
  2. Number of concurrent queries
    If 2 users run queries with DOP=8 each → 16 CPUs are needed.
  3. Server CPU capacity
    • If your server has 8 CPUs and Oracle tries to use 16 → CPU contention and slow performance.

👉 Rule of thumb:

Maximum effective DOP = (Total CPU cores ÷ Active Sessions)

🔹 Key Initialization Parameters

Enable / Disable Parallel Execution

    -- Disable:
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=MANUAL SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_DEGREE_LIMIT=1 SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=0 SCOPE=BOTH;

-- Enable
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_DEGREE_LIMIT=CPU SCOPE=BOTH;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=64 SCOPE=BOTH;

    Control Parallel Servers (allowed maximum parallel worker)

    -- Set max parallel limit (Maximum number of parallel workers allowed)
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=64 SCOPE=BOTH;

--Set Min Parallel limit (Minimum number of parallel workers allowed)
ALTER SYSTEM SET PARALLEL_MIN_SERVERS=4 SCOPE=BOTH;

    Enable or disable parallel at object level:

    -- Disable
ALTER TABLE table_name NOPARALLEL;
ALTER INDEX index_name NOPARALLEL;

-- Enable
ALTER TABLE table_name PARALLEL 8;
ALTER INDEX index_name PARALLEL 8;

    Enable or Disable Parallel at Session Level

    -- Disable
ALTER SESSION DISABLE PARALLEL QUERY;
ALTER SESSION DISABLE PARALLEL DML;
ALTER SESSION DISABLE PRALLEL DDL;

-- Enable
ALTER SESSION ENABLE PARALLEL QUERY;
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION ENABLE PRALLEL DDL;

    Leave a Reply