Using Parallelism in Oracle Database 12C

By Gowthami | apps-dba.com | Oracle Performance Series

Oracle Parallel Query (PQ) allows a single SQL statement to be executed by multiple parallel execution servers simultaneously, dramatically reducing elapsed time for large data operations. It is the primary tool for accelerating full table scans, large sorts, and bulk DML on multi-CPU systems.

Key Insight: Parallel query trades throughput for response time. A query using DOP 8 uses 8x the CPU and I/O resources but may run 6-7x faster. Use parallelism for batch jobs and large reports — not for OLTP queries where resource contention hurts all users.

Parallel Query Architecture

A parallel query uses a Query Coordinator (QC) — the session that submits the query — and multiple Parallel Execution Servers (PX servers) that perform the actual work. The QC distributes work, collects results, and returns them to the user.

-- Check current parallel configuration
SHOW PARAMETER parallel_max_servers;     -- max PX servers in pool
SHOW PARAMETER parallel_min_servers;     -- pre-started PX servers  
SHOW PARAMETER parallel_degree_policy;   -- MANUAL, AUTO, or ADAPTIVE

-- Check active parallel queries
SELECT qcsid, server#, degree, req_degree, sql_text
FROM v$px_session ps
JOIN v$sql s ON ps.sql_id = s.sql_id;

Enabling Parallelism

-- Method 1: Table-level parallel degree (persistent)
ALTER TABLE large_sales_table PARALLEL 8;

-- Method 2: Query-level hint (preferred for control)
SELECT /*+ PARALLEL(s, 8) */ 
       region, SUM(amount) 
FROM large_sales_table s
GROUP BY region;

-- Method 3: Session-level (affects all queries in session)
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

-- Reset session-level parallelism
ALTER SESSION DISABLE PARALLEL QUERY;

Parallel DML Operations

-- Parallel DML must be explicitly enabled
ALTER SESSION ENABLE PARALLEL DML;

-- Parallel INSERT
INSERT /*+ PARALLEL(t, 8) APPEND */ INTO target_table t
SELECT /*+ PARALLEL(s, 8) */ * FROM source_table s
WHERE created_date >= DATE '2024-01-01';
COMMIT;

-- Parallel UPDATE (12c+)
UPDATE /*+ PARALLEL(e, 4) */ large_emp_table e
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 30);
COMMIT;

-- Parallel CREATE TABLE AS SELECT (CTAS)
CREATE /*+ PARALLEL(8) */ TABLE archive_sales
PARALLEL 8 NOLOGGING AS
SELECT /*+ PARALLEL(s, 8) */ * FROM sales_history s
WHERE sale_year = 2023;

Monitoring Parallel Query Execution

-- Monitor active parallel queries (12c+)
SELECT sql_id, sql_text, px_servers_requested, px_servers_allocated,
       elapsed_time/1000000 elapsed_sec, cpu_time/1000000 cpu_sec
FROM v$sql_monitor
WHERE px_servers_requested > 0
  AND status = 'EXECUTING'
ORDER BY elapsed_time DESC;

-- Check parallel operations in execution plan
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(s, 4) */ region, SUM(amount)
FROM sales s GROUP BY region;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for: PX COORDINATOR, PX SEND/RECEIVE, PX BLOCK ITERATOR

Choosing the Right Degree of Parallelism

Operation Type	Recommended DOP	Notes
Ad-hoc large reports	4-8	Balance speed vs resource use
Nightly ETL/batch	CPUs/2 to CPUs	Off-peak, maximize throughput
CTAS / index rebuild	CPUs or CPUs/2	DDL benefits most from high DOP
OLTP queries	1 (no parallel)	Parallelism hurts OLTP concurrency
Exadata queries	AUTO or ADAPTIVE	Exadata manages DOP automatically

Summary

Oracle Parallel Query is a powerful weapon for large data operations. The key is using it judiciously — enable it for batch jobs, large reports, and maintenance operations on large tables, but avoid it for OLTP workloads. Always monitor with V$SQL_MONITOR to ensure parallel queries are actually running at the requested DOP.

Oracle Apps DBA

Search This Blog