Oracle: Parallel SQL Execution

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
consumers (which consume the data produced by producers)

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 for DML 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
enable_parallel_dml, 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.

Verify if (last) query was executed in parallel

The value of last_query of v$pq_sesstat can be used to verify if the (last) executed query was executed in parallel.

Especially useful for such an investigation are the values where statistic in ('Queries Parallelized', 'DOP') (DOP = Degree of parallism).

Note the typo in the parlallel hint:

select /*+ parlallel(4) */ count(*) from dba_objects where object_name like 'N%';
…
select
   statistic,
   last_query
from
   v$pq_sesstat
order by
   decode(statistic,
     'Queries Parallelized', chr(0),
     'DOP'                 , chr(1),
      statistic
  );
-- 
-- STATISTIC                      LAST_QUERY
-- ------------------------------ ----------
-- Queries Parallelized                    0
-- DOP                                     0
-- …

Same query, but with fixed hint:

select /*+ parallel(4) */ count(*) from dba_objects where object_name like 'N%';  
… 
select
   statistic,
   last_query
from
   v$pq_sesstat
order by
   decode(statistic,
     'Queries Parallelized', chr(0),
     'DOP'                 , chr(1),
      statistic
  );
-- 
-- STATISTIC                      LAST_QUERY
-- ------------------------------ ----------
-- Queries Parallelized                    1
-- DOP                                     4
-- …

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

select
   substr(parameter, 1,15) param,
   substr(value    , 1, 5) val
from
   v$option
where
   lower(parameter) like '%parallel%';
--
-- PARAM              VAL 
-- -----------------  ----
-- Parallel backup    TRUE
-- Parallel execut    TRUE
-- Parallel load      TRUE

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.