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.
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 ITERATORChoosing 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 Exadata - The Complete Guide
Learn how Exadata Smart Scan supercharges parallel query performance and master all Exadata-specific optimization techniques with Gowthami's guide.
Get the Book
