VLDB and Partitioning Guide -- Chapter 8 Parallel DML Notes

Parallel DML Basics:

1. DOP (Degree of Parallism):

   The number of parallel execution servers associated with a single operation.

2. Query Coordinator

   Master. The server process/shadow process.

3. Parallel Servers

   Slaves, workers

4. 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.

5. 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(*).

6. Producer-Consumer model and parallel server number.

   Oracle employes producer-cosumer model in paralle DML. In the previous example, The count(*) operation is the consumer of table scan of emp. Hence the paralle server number is greater than DOP. Often, 2 times.

7. Shared pool and parallel DML

   Each server in the producer set has a connection to each server in the consumer set.Each communication channel has at least one, and sometimes up to four memory buffers, which are allocated from the shared pool.

The default DOP

   DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT (RAC). If you have used Oracle RAC services to limit the number of nodes across which a parallel operation can execute, then the number of participating nodes is the number of nodes belonging to that service. For example, on a 4-node Oracle RAC cluster, with each node having 8 CPU cores and no Oracle RAC services, the default DOP would be 2 x 8 x 4 = 64.

Configuring auto DOP

    Set PARALLEL_DEGREE_POLICY to AUTO.

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.

Use DBMS_RESOURCE_MANAGER.CALIBRATE_IO to collect IO calibrate.

Specifying manual DOP

SELECT /*+parallel */ ename, dname FROM emp e, dept d WHERE e.deptno=d.deptno;

Parallel Server Pooling

When an instance starts, Oracle Database creates a pool of parallel execution servers.

PARALLEL_MIN_SERVERS <= Pool size <= PARALLEL_MAX_SERVERS.

In-memory Parallel Execution

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. FINAL RESORT!

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.

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.

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.