Oracle: Parallel SQL Execution

In an Oracle database, parallel SQL execution is based on

the Query Coordinator (QC) and
the Parallel Execution (PX) server processes.

QC - Query Coordinator

QC initiates the parallel execution and distributes the work to PX servers.

The QC is identified in execution plans as PX COORDINATOR.

PX - Parallel Execution (Servers)

PX does most of the work for the job at hand.

There are two types of PX servers:

producers (which produce the data for the)
consumers

The number of PX servers that is started at instance startup is determined by the init parameter parallel_min_servers.

Oracle can dynamically add more PX servers, up to a maximum defined by the parameter parallel_max_servers.

Pre-allocated PX servers are listed in v$px_process.

QC and PX

For example, in a parallel select statement with a sum(), the PX servers sum part of the data while the QC in the end will calculate the final sum from the results from the PX servers.

Ranger

A Ranger process runs early and inspects the distribution of the input data in order to assign data ranges to server processes.

Enabling parallel execution

By default, Oracle is enabled for parallel execution of queries and DDL statements.

Parallel execution can be enabled forDML statements, select and DDL statements with these alter session statements:

alter session enable parallel dml;
alter session enable parallel query;
alter session enable parallel ddl;

alter session force  parallel dml parallel 16;

After enabling parallel dml or query, the new state is reflected in v$session.

select
-- pdml_enabled, -- Obsolete, replaced by pdml_status
   pdml_status,
   pddl_status,
   pq_status,
   sid
from
   v$session
where
   audsid = sys_context('userenv', 'sessionid');

Using SQL hints

insert /*+ enable_parallel_dml */ …

Other hints include

statement_queuing, no_statement_queuing
parallel, no_parallel
parallel_index, no_parallel_index
disable_parallel_dml
pq_concurrent_union, no_pq_concurrent_union
pq_distribute
pq_skew
… ?

Transactions / rollback

Apparently, an alter session [enable|disable] parallel… statement cannot be executed if a transaction is still active.

A rollback of a DML statement that was executed in parallel seems to be faster as well. TOOD: Needs to be investigated.

Granules

A granule is a unit of work into which parallelized operations can be divided.

These granules are passed to parallel execution servers (PX) to let them process one after another.

The number of granules and their size is dependent on the degree of parallelism (DOP).

Degree of Parallelism (DOP)

DOP: Degree of parallelism. The DOP defines the number of parallel streams with which a statement will be processed.

In most cases, each stream has a sender and a receiver of data. In these cases, the number of parallel execution servers is twice the DOP.

An example where only and server is needed per DOP is a single table scan.

See also default DOP for tables and the init parameter parallel_min_degree

select * from v$pq_sesstat where statistic = 'DOP';

Default degree of parallelism

In a single instance environment, the default degree of parallelism is determined by the product of the two parameters cpu_count and parallel_threads_per_cpu:

with p as (
  select
    max(case when name = 'cpu_count'                then value end) cpu_count,
    max(case when name = 'parallel_threads_per_cpu' then value end) parallel_threads_per_cpu
  from
    v$parameter
)
select
  p.cpu_count,
  p.parallel_threads_per_cpu,
  p.cpu_count * p.parallel_threads_per_cpu "default parallism degree"
from
  p;

Data Flow Opration (DFO)

DFO: Data flow operation.

In an execution plan, a DFO corresponds to the PX SEND operation.

DFOs are arranged in an DFO tree (PX Coordinator).

See also the column DFO_NUMBER in V$PQ_TQSTAT.

DFO Tree

A DFO Tree corresponds to the PX COORDINATOR operation.

select * from v$pq_sesstat where statistic = 'DFO Trees';

TODO

TQ: Table Queue

ORA-12801 and ORA-12805

ORA-12801 is error signaled in parrallel query server …

ORA-12805 is parallel query server died unexpectedly

These two errors are generic messages that may be caused when executing a parallel query. These errors are usually (always?) accompanied by additional messages which indicate the real cause of the problem.