Oracle Parallel Execution

基础篇

Parallel Execution名词解释

DOP (Degree of Parallism): The number of parallel execution servers associated with a single operation.
Query Coordinator: Master: The server process/shadow process.
Parallel Servers: Slaves, workers
Intra-operation Parallel: The parallelism of an individual operation is called intra-operation parallelism. For instance, a table scan carried out by 4 parallel servers.
Inter-operation parallel: The parallelism between operations in a data flow tree is called inter-operation parallelism. Consider: select count(*) from emp. An example of inter-operation parallel would be - Sending Data retrieved from emp to paralel servers that do count(*).
Producer-Consumer Model: To execute a query in parallel, Oracle Database generally creates a set of producer parallel execution servers and a set of consumer parallel execution servers. For SQL - SELECT /*+ PARALLEL */ count(*) FROM EMP, the count(*) operation is the consumer of table scan of emp. Therefore, the paralle server number is greater than DOP. Often, 2 times.

Parallel Server Communication

Each server in the producer set has a connection to each server in the consumer set. The number of virtual connections between parallel execution servers increases as the square of the degree of parallelism. Each communication channel has at least one, and sometimes up to four memory buffers, which are allocated from the shared pool. Each parallel execution server actually has an additional connection to the parallel execution coordinator. Multiple memory buffers facilitate asynchronous communication among the parallel execution servers.
When a connection is between two processes on the same instance, the servers communicate by passing the buffers back and forth in shared pool.It is important to size the shared pool adequately when using parallel execution. If there is not enough free space in the shared pool to allocate the necessary memory buffers for a parallel server, it fails to start.

How SQL is Executed in Parallel

Traditional, parallel DML uses direct-path to retrieve data directly from disk, bypassing the SGA. However, when the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). If the size of the object is larger than the size of the buffer cache (single instance),then the object is read using direct-path reads.
1. A SQL statement is issued.
2. The statement is parsed and the optimizer determines the execution plan.
3. If the execution time is greater than the threshold limit (PARALLEL_MIN_TIME_THRESHOLD), the statement is run in parallel based on the DOP that the optimizer calculates. PARALLEL_MIN_TIME_THRESHOLD defaults to 10 seconds.
4. Available parallel resources are checked. Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the PARALLEL_SERVERS_TARGET initialization parameter.
5. The the execution time, Oracle uses an algorithm to determine whether a parallel operation should receive the requested DOP or have its DOP lower to ensure the system is not overloaded. Adaptive parallelism is controlled through the database initialization parameter PARALLEL_ADAPTIVE_MULTI_USER.
6. If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing.

Parallel Server Pooling

When an instance starts, Oracle Database creates a pool of parallel execution servers. The initialization parameter PARALLEL_MIN_SERVERS specifies the number of parallel execution servers that Oracle Database creates at instance startup.

When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle Database can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout execution. After the statement has been processed completely, the parallel execution servers return to the pool.

If the number of parallel operations increases, Oracle Database creates additional parallel execution servers to handle incoming requests. However, Oracle Database never creates more parallel execution servers for an instance than the value specified by the initialization parameter PARALLEL_MAX_SERVERS. If the number of parallel operations decreases, Oracle Database terminates any parallel execution servers that have been idle for a threshold interval. Oracle Database does not reduce the size of the pool less than the value of PARALLEL_MIN_SERVERS, no matter how long the parallel execution servers have been idle.

Granules of Parallism

Block Range Granules
Ranges of physical blocks from a table. Oracle Database computes the number and the size of the granules during run-time.The number and size of granules are dependent upon the size of the object and the DOP.
In the execution plan, The operation - PX BLOCK ITERATOR above the table or index access indicates that block range granules have been used. For SQL statements run in parallel by block range or by parallel execution servers, the workload is dynamically divided among the parallel execution servers.This minimizes workload skewing.

Partition Granules
A parallel server process works on an entire partition or subpartition of a table or index. When partition granules are used for parallel access to a table or index, you should use a relatively large number of partitions (ideally, three times the DOP), so that Oracle Database can effectively balance work across the query server processes.
Above the table or index access in the explain plan output,PX PARTITION RANGE ALL indicates the statement accesses all of the partitions in the table.If not all of the partitions are accessed, it simply shows PX PARTITION RANGE.

Initial Parameters

PARALLEL_DEGREE_POLICY
Specifies whether or not automatic degree of Parallelism,statement queuing, and in-memory parallel execution will be enabled. MANUAL is the default, which disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. AUTO enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
    MANUAL - Disables automatic DOP, statement queuing and in-memory parallel execution. It reverts the behavior. of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2), which is the default.
    LIMITED - Enables automatic DOP for some statements but parallel statement queuing and in-memory parallel execution are disabled. Automatic DOP is applied only to statements that access tables or indexes declared explicitly with the PARALLEL clause. Tables and indexes that have a DOP specified use that explicit DOP setting
    AUTO - Enables automatic DOP, parallel statement queuing, and in-memory parallel execution. If I/O calibration is not run to gather the required statistics, the explain plan output includes the following text in its notes:automatic DOP: skipped because of IO calibrate statistics are missing.
    ALTER SESSION SET PARALLEL_DEGREE_POLICY = {AUTO|MANUAL};

PARALLEL_MIN_TIME_THRESHOLD
This parameter specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 10 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. The default is AUTO.

PARALLEL_DEGREE_LIMIT
With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. This limit is enforced by PARALLEL_DEGREE_LIMIT.
CPU - The maximum degree of parallelism is limited by the number of CPUs in the system. The formula used to calculate the limit is PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available (by default, all the opened instances on the cluster but can be constrained using PARALLEL_INSTANCE_GROUP or service specification). This is the default.

PARALLEL_MAX_SERVERS
specifies the maximum number of parallel execution processes and parallel recovery processes for an instance.

PARALLEL_SERVERS_TARGET
This parameter specifies the number of parallel server processes allowed to run parallel statements before statement queuing is used.
When PARALLEL_DEGREE_POLICY is set to AUTO, statements that require parallel execution are queued if the number of parallel processes currently in use on the system equals or is greater than PARALLEL_SERVERS_TARGET. This is not the maximum number of parallel server processes allowed on a system (that is controlled by PARALLEL_MAX_SERVERS). However, PARALLEL_SERVERS_TARGET and parallel statement queuing is used to ensure that each statement that requires parallel execution is allocated the necessary parallel server resources and the system is not flooded with too many parallel server processes.

PARALLEL_MIN_SERVERS
This parameter specifies the number of processes to be started in a single instance that are reserved for parallel operations. Processes started using PARALLEL_MIN_SERVERS do not exit until the database is shut down. This way, when a query is issued, the processes are likely to be available.

PARALLEL_MIN_PERCENT
Default value is 0. Setting this parameter to values other than 0 (zero) causes Oracle Database to return an error when the requested DOP cannot be satisfied by the system at a given time. For example, if you set PARALLEL_MIN_PERCENT to 50, which translates to 50 percent, and the DOP is reduced by 50 percent or greater because of the adaptive algorithm or because of a resource limitation, then Oracle Database returns ORA-12827: insufficient parallel query slaves available.

SHARED_POOL_SIZE
Parallel execution requires memory resources in addition to those required by serial SQL execution. Additional memory is used for communication and passing data between query server processes and the query coordinator.Oracle Database allocates memory for query server processes from the shared pool.

PARALLEL_FORCE_LOCAL
This parameter specifies whether a SQL statement executed in parallel is restricted to a single instance in an Oracle RAC environment.

PARALLEL_IO_CAP_ENABLED
The PARALLEL_IO_CAP_ENABLED parameter is deprecated. It is retained for backward compatibility only. It is replaced by the PARALLEL_DEGREE_LIMIT parameter when set to IO.

PARALLEL_THREADS_PER_CPU

The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.

实战篇-Parallel DML

Here, parallel DML refers to insert, update, delete, merge, not select.

Enabling Parallel DML

Alter session enable parallel DML --Must!

A transaction can contain multiple parallel DML statements that modify different tables, but after a parallel DML statement modifies a table, no subsequent serial or parallel statement (DML or query) can access the same table again in that transaction.

Parallel DML and Direct-Path Insert

When you are inserting in parallel mode, direct-path INSERT is the default.

例子

INSERT /*+ APPEND PARALLEL*/ INTO xxx SELECT col FROM yyy;

1. Data is written to data file directly, bypassing buffer cache.
2. Inserts data beyond the High Water Mark.
3. Referential integrity is ignored.
4. Transaction atomicity is not guaranteed during parallel direct-load insert using SQL*Loader.
5. When you are inserting in parallel DML mode, direct-path INSERT is the default. There's no need to add "append" hint into Insert statement.

How Parallel Direct-Path Insert works

Parallel direct-load into partitioned table -- Each parallel execution server is assigned one or more partitions, with no more than one process working on a single partition.
Parallel direct-load into non-partitioned table -- Each parallel execution server allocates a new temporary segment and inserts data into that temporary segment. When a COMMIT runs, the parallel execution coordinator merges the new temporary segments into the primary table segment, where it is visible to users.

演示

我将给出3个例子分别演示

• Parallel Select and Seriel Conventional Insert
• Parallel Insert Select and Direct-Path Insert
• Parallel Insert Select and Conventional Insert 

请观察三种情况下的SQL写法和执行计划的差别。

版本信息:

show rel;
release 110200300

Parallel Select and Seriel Conventional Load

演示show rel;release 110200300Serial Conventional Loadalter session disable parallel DML;  Insert /*+ PARALLEL(4) */ into /* hello1*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER;  SELECT t.*  FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello1%';

In this case, the parallel DML is disabled, the parallel hint only applies to the select part.

Parallel Direct-Path Load

rollback;  alter session enable parallel DML;  Insert /*+ PARALLEL(4) */ into /* hello2*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER;  SELECT t.*  FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello2%';  

Please note the operation "LOAD AS SELECT" for direct-path insert.

select count(*) from orders;  

--ORA-12839: Cannot read/modify an object after modifying it in parallel.

rollback;  Insert /*+ NOAPPEND PARALLEL(4) */ into /* hello3*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER;  SELECT t.*  FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello3%';  

select count(*) from orders;

--ORA-12839: Cannot read/modify an object after modifying it in parallel.

Parallel Conventional Load

rollback;  Insert /*+ NOAPPEND PARALLEL(4) */ into /* hello3*/ orders select ROWNUM,ROWNUM,sysdate-mod((ROWNUM-1),31),ROWNUM FROM CUSTOMER;  SELECT t.*  FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%hello3%';  

select count(*) from orders;

--ORA-12839: Cannot read/modify an object after modifying it in parallel.

理解执行计划

已第三个例子的执行计划为例，现将各栏中与并发有关的信息解释如下：

Operation

PX is for parallel execution.

HASH GROUP BY: Starting from 10g, Oracle uses a hash algorithm to "group by" instead of the sort algorith, it's CPU cost is lower.
PX CORDINATOR: The query coordinator.
PX SEND HASH: PX sends in Hash. Indicates a producer group.

PX SEND BROADCAST:

PX SEND QC(RANDOM): Parallel sends to Query Coordinator in random order. Also indicates a producer group.
PX BLOCK ITERATOR: Parallism graules in block.
PX RECEIVE: PX receieves. Indicates a consumer group.

Hash join buffered: 由于对于一个DFO（Data Flow Operation)，只能有两组slave process，一组producer，一组consumer。假如，Oracle没有多余的slave process来处理这两组slave process产生的结果集，Oracle只能将结果集暂存在内存或temp tablespace中。这时的Operation就称为xxx buffered，如Hash Join Buffered。

TQ - Table Queue

Table Queue是一块内存，用于Producer， Consumer，QC进程间通信。Each set of parallel processes gets a table queue, so this column indicates which set of parallel processes implement the activity specified in the "Operation" column.

这时，Name一栏的值有形如TQ<DFO_Number><TQ_ID>

DFO_Number为一位数字，与V$PQ_TQSTATS的DFO_Number一致。

TQ_ID为四位数字，与V$PQ_TQSTATS的TQ_ID一致。

IN-OUT

PCWP: Parallel Combine with Parent.
PCWC: Parallel Combine wth Child.  
P->S: Parallel to Serial.
S->P: Serial to Parallel.
P->P: Parallel to Parallel. Inter-operation parallism.

PQ Distrib

QC(Rand): All producers send their rows to the query coordinator in random order.
HASH: A hash function is used to determine which consumer a producer sends a row to. This is useful for hash joins. Q1,00 are producers, while Q1,01 consumers.
Range: Producers send rows in a different range to different consumers.  This is useful for an ORDER BY operation.

理解这些信息，能够帮助我们更好理解Oracle如何去做并发。

V$PQ_TQSTAT

该视图包含了每个slave, QC处理了多少行数据，同时只能在QC session中查询。

select * from v$pq_tqstat order by tq_id, server_type;

Total the bytes column and group by TQ_ID, and the table queue identifier, to obtain the total number of bytes sent through each table queue. Compare this to the optimizer estimates; large variations might indicate a need to analyze the data using a larger sample.

Data Flow Operations

Operations that are based on the same data distribution form a DFO, in a DFO, there's no data re-distribution.

Parent and child DFOs communicate via table queues.

A DFO tree consists of one or more DFOs.

Multiple DFO trees can have parent/child relationship.

A parallel execution plan consists of one or more DFO trees.

Each DFO tree has a seriel root operation - PX COORDINATOR. 

In most cases, for each DFO tree a DoP will be determined - Could be a different DoP per DFO tree.

Each DFO tree usually has its own slave processes - up to two sets per DFO tree.

For execution plans with one DFO trees, number of slave process is 2 * DoP. Plans with 2 DFO trees, both trees have DoP of 8, number of slave process is 2 * 2 * DoP.

DFO trees might start once or multiple times - depending on execution plan shape. If a DFO tree starts multiple times, it may be an indication of inefficient execution plan.

Because each time DFO tree strats, it acquires slave process sets from QC, which is an overhead and needs to be minimized.

Each DFO trees gets its own child cursor. The child cursor contains a copy of the full execution plan across all DFO trees.

Execution plans with multiple DFO trees migth experience DoP downgrade if Oracle runs out of available slave processes.

参考资料：

http://www.oracle.com/technetwork/articles/database-performance/geist-parallel-execution-1-1872400.html

http://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html

Oracle Database VLDB and Partitioning Guide

Oracle Database Reference