Parallel Execution

Query coordinator (QC) plus parallel execution servers (producer/consumer sets), table queues, granules (block ranges or partitions).

Distribution methods: HASH, BROADCAST, RANGE, ROUND-ROBIN, PARTITION, and adaptive distribution.

Automatic Degree of Parallelism (Auto DOP): PARALLEL_DEGREE_POLICY = AUTO | ADAPTIVE | LIMITED | MANUAL, plus parallel statement queuing and in-memory parallel execution.

Parameters: PARALLEL_MAX_SERVERS, PARALLEL_MIN_SERVERS, PARALLEL_SERVERS_TARGET, PARALLEL_DEGREE_LIMIT, PARALLEL_MIN_TIME_THRESHOLD.

Monitoring: V$PX_SESSION, V$PQ_TQSTAT, SQL Monitor.

DBMS_PARALLEL_EXECUTE splits a large update into chunks.

Pitfalls: parallel on OLTP, data skew, downgraded DOP, too many parallel servers.

ALTER TABLE sales PARALLEL 8;
SELECT /*+ PARALLEL(s, 8) */ region, SUM(amount) FROM sales s GROUP BY region;
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(t, 8) */ INTO sales_hist t SELECT * FROM sales;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
CREATE INDEX ix_big ON sales(region) PARALLEL 8;  ALTER INDEX ix_big NOPARALLEL;