Parallel Query/DML/DDL, Degree of Parallelism, PX Servers, Skew, and Resource Consumption

Learning outcomes

A nightly ServiceHub analytics query takes 20 minutes. The DBA adds /*+ PARALLEL(32) */ and celebrates a five-minute response while every OLTP request slows down. Parallel Execution (PX) reduces one statement's elapsed time by using more processes/CPU/I/O concurrently. That makes it a capacity-allocation mechanism, not free speed.

1. The coordinator delegates one SQL statement to PX servers

The client/server process becomes the Parallel Execution Coordinator (QC). The QC obtains PX servers that execute units called granules. A set of related parallel operations is a Data Flow Operation (DFO). Producer and consumer server sets exchange rows through table queues.

The Degree of Parallelism (DOP) is the number of PX servers in one set. Because one parallelizer can have two active server sets, a DOP 4 operation can involve up to eight PX servers concurrently plus the QC. Multiple parallelizers can use more.

2. Current licensing means this is not a Free execution lab

The current 26ai matrix marks Parallel query/DML = N in Free and SE2-ODA; it is available in EE/EE-ES and specified BaseDB/ExaDB offerings. Parallel statistics gathering, parallel index build/scans, Parallel Data Pump and statement queuing follow the same broad offering split.

3. Entitled PX: inspect requested/granted DOP

SELECT /*+ gather_plan_statistics parallel(t 4) */       region_id,       COUNT(*),       SUM(amount)FROM servicehub_px_case tGROUP BY region_id;SELECT  qcsid,  sid,  server_group,  server_set,  degree,  req_degreeFROM v$px_sessionORDER BY qcsid,server_group,server_set,sid;

REQ_DEGREE is what the statement requested; DEGREE is what the running PX set received after resource/load constraints. A hint is a request, not proof of actual DOP.

4. Distribution method and data skew decide whether workers stay balanced

For joins/aggregations, PX producers may hash, broadcast, range-distribute or otherwise route rows to consumers. If one join/group key represents most rows, one consumer can receive most work while peers idle.

SELECT  dfo_number,  tq_id,  server_type,  process,  num_rows,  bytesFROM v$pq_tqstatORDER BY dfo_number DESC,tq_id,server_type,process;

Large variance in NUM_ROWS/BYTES across workers is concrete skew evidence. The repair may be a different join method/distribution, better key cardinality, preaggregation, partition strategy or lower DOP—not simply more PX servers.

5. Free lab: quantify the data skew before parallelizing it

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_px_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_px_case (  event_id NUMBER PRIMARY KEY,  region_id NUMBER NOT NULL,  amount NUMBER(10,2) NOT NULL,  payload VARCHAR2(200));INSERT INTO servicehub_px_caseSELECT  LEVEL,  CASE    WHEN LEVEL <= 90000 THEN 1    ELSE 2 + MOD(LEVEL,7)  END,  MOD(LEVEL*19,10000)/10,  RPAD('p',100,'p')FROM dualCONNECT BY LEVEL <= 100000;COMMIT;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SERVICEHUB_PX_CASE');END;/

SELECT  region_id,  COUNT(*) AS rows_in_group,  ROUND(100*COUNT(*)/SUM(COUNT(*)) OVER(),2) AS pct_rowsFROM servicehub_px_caseGROUP BY region_idORDER BY rows_in_group DESC;

About 90% of rows belong to region 1. A hash redistribution by REGION_ID can therefore overload the worker that owns region 1. This serial measurement is valid on Free and predicts a PX risk without executing an unlicensed PX plan.

6. Faster elapsed time can reduce system throughput

If a serial query consumes one CPU for 20 minutes and PX makes it consume eight CPU processes for five minutes, elapsed time improved, but total CPU-seconds may be similar or higher. Multiple concurrent PX queries can exhaust CPU, I/O, memory/TEMP and PX server pools, delaying OLTP and other analytics.

SELECT  sql_id,  executions,  elapsed_time,  cpu_time,  buffer_gets,  disk_reads,  px_servers_executionsFROM v$sqlWHERE sql_id = :sql_id;

Compare per-execution elapsed time and CPU/I/O, then repeat under realistic concurrency. A single-query benchmark is not a throughput benchmark.

7. Parallel DML must be enabled explicitly

ALTER SESSION ENABLE PARALLEL DML;UPDATE /*+ parallel(servicehub_px_case 4) */       servicehub_px_caseSET amount=amount*1.01WHERE region_id=1;-- If the UPDATE actually ran as PDML, trying to read/modify the-- same object again in the same transaction can raise:SELECT COUNT(*) FROM servicehub_px_case;-- ORA-12838: cannot read/modify an object after modifying it in parallelCOMMIT;

ORA-12838 is not “parallel is broken.” It is a transaction restriction after parallel/direct-path modification. Commit or rollback at the correct transaction boundary, then continue. Do not introduce extra commits merely to satisfy the error if business atomicity requires one transaction.

8. Parallel DDL and object attributes are persistent design inputs

CREATE INDEX sh19_px_region_ixON servicehub_px_case(region_id)PARALLEL 4;ALTER INDEX sh19_px_region_ix NOPARALLEL;ALTER TABLE servicehub_px_case PARALLEL 4;ALTER TABLE servicehub_px_case NOPARALLEL;

Leaving a table/index with a high default DOP can surprise future maintenance/query workloads. Reset object parallel attributes when the bulk operation is finished unless the persistent design intentionally needs them.

9. Deliberately wrong: FORCE PARALLEL 32 in a multiuser OLTP database

Default/manual DOP can target a single-user assumption and consume the maximum resources available to finish one statement sooner. In multiuser environments, resource governance and workload testing are mandatory. Automatic DOP/parallel statement queuing can help on entitled systems, but Database Resource Manager licensing/offering boundaries from Chapter 13 still apply.

10. Cleanup

DROP TABLE servicehub_px_case PURGE;

11. Production judgment

Use PX for scans, joins, bulk loads and maintenance whose elapsed-time benefit justifies extra CPU/I/O/memory/TEMP. Choose DOP from concurrency/resource budgets and distribution granularity. Inspect requested/granted DOP and V$PQ_TQSTAT skew on the same real workload.

Parallel query/DML is not licensed in Free, so no mandatory Free command enables it. No hidden PX parameter recommendations appear here. Lesson 5 closes the VLDB chapter by deciding how different compression tiers and ILM policies trade CPU, storage, DML cost and platform/license constraints.

Authoritative references

• Using Parallel Execution — QC/PX/DOP/distribution/skew and monitoring
• V$PX_SESSION — requested/granted DOP
• V$PQ_TQSTAT — table-queue distribution statistics
• Managing Processes — enabling/forcing parallel query/DML/DDL
• Licensing Information — parallel feature offering matrix