How Parallel Execution Works

Dividing Work Among Parallel Execution Servers

The parallel execution coordinator examines each operation in a SQL statement's execution plan then determines the way in which the rows operated on by the operation must be divided or redistributed among the parallel execution servers. As an example of parallel query with intra- and inter-operation parallelism, consider the query in Example 8-1.

EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(4) */ customers.cust_first_name, customers.cust_last_name, 
  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sales, customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

Explained.

Note that a hint has been used in the query to specify the DOP of the tables customers and sales.

Example 8-2 shows the explain plan output for the query in Example 8-1.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 4060011603
--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |   925 | 25900 |        |      |            |
|   1 |  PX COORDINATOR            |           |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10003  |   925 | 25900 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |           |   925 | 25900 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE             |           |   925 | 25900 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10002  |   925 | 25900 |  Q1,02 | P->P | HASH       |
|*  6 |       HASH JOIN BUFFERED   |           |   925 | 25900 |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE          |           |   630 | 12600 |  Q1,02 | PCWP |            |
|   8 |         PX SEND HASH       | :TQ10000  |   630 | 12600 |  Q1,00 | P->P | HASH       |
|   9 |          PX BLOCK ITERATOR |           |   630 | 12600 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| CUSTOMERS |   630 | 12600 |  Q1,00 | PCWP |            |
|  11 |        PX RECEIVE          |           |   960 |  7680 |  Q1,02 | PCWP |            |
|  12 |         PX SEND HASH       | :TQ10001  |   960 |  7680 |  Q1,01 | P->P | HASH       |
|  13 |          PX BLOCK ITERATOR |           |   960 |  7680 |  Q1,01 | PCWC |            |
|  14 |           TABLE ACCESS FULL| SALES     |   960 |  7680 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")
 
26 rows selected.

Figure 8-1 illustrates the data flow or query plan for the query in Example 8-1.

Figure 8-1 Data Flow Diagram for Joining Tables

Description of "Figure 8-1 Data Flow Diagram for Joining Tables"

Parallelism Between Operations

Given two sets of parallel execution servers SS1 and SS2 for the query plan illustrated in Figure 8-1, the execution proceeds as follows: each server set (SS1 and SS2) has four execution processes because of the PARALLEL hint in the query that specifies the DOP.

Child set SS1 first scans the table customers and sends rows to SS2, which builds a hash table on the rows. In other words, the consumers in SS2 and the producers in SS1 work concurrently: one in scanning customers in parallel, the other is consuming rows and building the hash table to enable the hash join in parallel. This is an example of inter-operation parallelism.

After SS1 has finished scanning the entire customers table, it scans the sales table in parallel. It sends its rows to servers in SS2, which then perform the probes to finish the hash-join in parallel. After SS1 has scanned the sales table in parallel and sent the rows to SS2, it switches to performing the GROUP BY operation in parallel. This is how two server sets run concurrently to achieve inter-operation parallelism across various operators in the query tree.

Another important aspect of parallel execution is the redistribution of rows when they are sent from servers in one server set to servers in another. For the query plan in Example 8-2, after a server process in SS1 scans a row from the customers table, which server process in SS2 should it send it to? The operator into which the rows are flowing decides the redistribution. In this case, the redistribution of rows flowing up from SS1 performing the parallel scan of customers into SS2 performing the parallel hash-join is done by hash partitioning on the join column. That is, a server process scanning customers computes a hash function of the value of the column customers.cust_id to decide the number of the server process in SS2 to send it to. The redistribution method used in parallel queries explicitly shows in the Distrib column in the EXPLAIN PLAN of the query. In Figure 8-1, "Data Flow Diagram for Joining Tables", this can be seen on lines 5, 8, and 12 of the EXPLAIN PLAN.

Producer or Consumer Operations

Operations that require the output of other operations are known as consumer operations. In Figure 8-1, the GROUP BY SORT operation is the consumer of the HASH JOIN operation because GROUP BY SORT requires the HASH JOIN output.

Consumer operations can begin consuming rows as soon as the producer operations have produced rows. In Example 8-2, while the parallel execution servers are producing rows in the FULL SCAN of the sales table, another set of parallel execution servers can begin to perform the HASH JOIN operation to consume the rows.

Each of the two operations performed concurrently is given its own set of parallel execution servers. Therefore, both query operations and the data flow tree itself have parallelism. The parallelism of an individual operation is called intra-operation parallelism and the parallelism between operations in a data flow tree is called inter-operation parallelism. Due to the producer-consumer nature of the Oracle Database operations, only two operations in a given tree must be performed simultaneously to minimize execution time. To illustrate intra- and inter-operation parallelism, consider the following statement:

SELECT * FROM employees ORDER BY last_name;

The execution plan implements a full scan of the employees table. This operation is followed by a sorting of the retrieved rows, based on the value of the last_name column. For the sake of this example, assume the last_name column is not indexed. Also assume that the DOP for the query is set to 4, which means that four parallel execution servers can be active for any given operation.

Figure 8-2 illustrates the parallel execution of the example query.

Figure 8-2 Inter-operation Parallelism and Dynamic Partitioning

Description of "Figure 8-2 Inter-operation Parallelism and Dynamic Partitioning"

As illustrated in Figure 8-2, there are actually eight parallel execution servers involved in the query even though the DOP is 4. This is because a producer and consumer operator can be performed at the same time (inter-operation parallelism).

Also note that all of the parallel execution servers involved in the scan operation send rows to the appropriate parallel execution server performing the SORT operation. If a row scanned by a parallel execution server contains a value for the last_name column between A and G, that row is sent to the first ORDER BY parallel execution server. When the scan operation is complete, the sorting processes can return the sorted results to the query coordinator, which, in turn, returns the complete query results to the user.

Manually Specifying the Degree of Parallelism

A specific DOP can be requested from Oracle Database. For example, you can set a fixed DOP at a table or index level:

ALTER TABLES sales PARALLEL 8;
ALTER TABLE customers PARALLEL 4;

In this case, queries accessing just the sales table use a requested DOP of 8 and queries accessing the customers table request a DOP of 4. A query accessing both the sales and the customers tables is processed with a DOP of 8 and potentially allocates 16 parallel servers (producer or consumer); whenever different DOPs are specified, Oracle Database uses the higher DOP.

Automatic Parallel Degree Policy

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use. Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Finally, Oracle Database decides if the statement can take advantage of the aggregated cluster memory or not.

The following is a summary of parallel statement processing when parallel degree policy is set to automatic.

1. A SQL statement is issued.

2. The statement is parsed and the optimizer determines the execution plan.

3. The threshold limit specified by the PARALLEL_MIN_TIME_THRESHOLD initialization parameter is checked.

   1. If the execution time is less than the threshold limit, the SQL statement is run serially.

   2. If the execution time is greater than the threshold limit, the statement is run in parallel based on the DOP that the optimizer calculates.

For more information, see "Determining Degree of Parallelism" and "Controlling Automatic Degree of Parallelism".

EXPLAIN PLAN FOR
SELECT SUM(AMOUNT_SOLD) FROM SH.SALES;

PLAN TABLE OUTPUT

Plan hash value: 672559287
-------------------------------------------------------------------------------------------------
| Id |          Operation    |   Name |  Rows | Bytes | Cost(%CPU) |    Time   | Pstart |  Pstop |
-------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |        |    1  |     4 |    5 (0)   |  00:00:01 |        |        |
|  1 | SORT AGGREGATE        |        |    1  |     4 |            |           |        |        |
|  2 |  PX COORDINATOR       |        |    1  |     4 |            |           |        |        |
|  3 |   PX SEND QC(RANDOM)  |:TQ10000|    1  |     4 |    5 (0)   |           |        |        |
|  4 |    SORT AGGREGATE     |        |    1  |     4 |            |           |        |        |
|  5 |     PX BLOCK ITERATOR |        |   960 |  3840 |    5 (0)   |  00:00:01 |      1 |     16 |
|  6 |     TABLE ACCESS FULL |  SALES |   960 |  3840 |    5 (0)   |  00:00:01 |      1 |     16 |
--------------------------------------------------------------------------------------------------
 
Note
-----
   - Computed Degree of Parallelism is 2
   - Degree of Parallelism of 2 is derived from scan of object SH.SALES

Controlling Automatic Degree of Parallelism

There are two initialization parameters that control automatic DOP, PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD. They are also described in "Automatic Parallel Degree Policy" and "Controlling Automatic DOP, Parallel Statement Queuing, and In-Memory Parallel Execution". There are also two hints you can use to control parallelism.

ALTER SESSION SET parallel_degree_policy = limited;
ALTER TABLE emp parallel (degree default);

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

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

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

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

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

In-Memory Parallel Execution

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). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.

Adaptive Parallelism

The adaptive multiuser algorithm, which is enabled by default, reduces the degree of parallelism as the load on the system increases. When using the Oracle Database adaptive parallelism capabilities, the database uses an algorithm at SQL execution time to determine whether a parallel operation should receive the requested DOP or have its DOP lower to ensure the system is not overloaded.

In a system that makes aggressive use of parallel execution by using a high DOP, the adaptive algorithm adjusts the DOP down when only a few operations are running in parallel. While the algorithm still ensures optimal resource utilization, users may experience inconsistent response times. Using solely the adaptive parallelism capabilities in an environment that requires deterministic response times is not advised. Adaptive parallelism is controlled through the database initialization parameter PARALLEL_ADAPTIVE_MULTI_USER.

Controlling Automatic DOP, Parallel Statement Queuing, and In-Memory Parallel Execution

The initialization parameter PARALLEL_DEGREE_POLICY controls whether automatic degree of parallelism (DOP), parallel statement queuing, and in-memory parallel execution are enabled. This parameter has three possible values:

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

By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement. The degree of parallelism used is exactly what was specified. No parallel statement queue occurs and parallel execution does not use the buffer cache. For information about the parallel statement queue, refer to "Parallel Statement Queuing".

If you want Oracle Database to automatically decide the degree of parallelism only for a subset of SQL statements that touch a specific subset of objects, then set PARALLEL_DEGREE_POLICY to LIMITED and set the parallel clause on that subset of objects. If you want Oracle Database to automatically decide the degree of parallelism, then set PARALLEL_DEGREE_POLICY to AUTO.

When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics. The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature.

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

I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. I/O calibration is a one-time action if the physical hardware does not change.

Managing Parallel Statement Queuing with Resource Manager

By default, the parallel statement queue operates as a first-in, first-out queue. By configuring and setting a resource plan, you can control the order in which parallel statements are dequeued and the number of parallel servers used by each workload or consumer group.

Resource plans and consumer groups are created using the DBMS_RESOURCE_MANAGER PL/SQL package. A resource plan consists of a collection of directives for each consumer group which specify controls and allocations for various database resources, such as parallel servers. A resource plan is enabled by setting the RESOURCE_MANAGER_PLAN parameter to the name of the resource plan.

The following sections describe the directives that can be used to manage the processing of parallel statements for consumer groups when the parallel degree policy is set to AUTO.

• Managing the Order of the Parallel Statement Queue

• Limiting the Parallel Server Resources for a Consumer Group

• Specifying a Parallel Statement Queue Timeout for Each Consumer Group

• Specifying a Degree of Parallelism Limit for Consumer Groups

• A Sample Scenario for Managing Statements in the Parallel Queue

In all cases, the parallel statement queue is managed as a single queue on an Oracle RAC database. Limits for each consumer group apply to all sessions across the Oracle RAC database that belong to that consumer group. The queuing of parallel statements occurs based on the sum of the values of the PARALLEL_SERVERS_TARGET initialization parameter across all database instances.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 
  /* Create consumer groups.
   * By default, users start in OTHER_GROUPS, which is automatically
   * created for every database.
   */
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'MEDIUM_SQL_GROUP',
    'Medium-running SQL statements, between 1 and 15 minutes.  Medium priority.');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    'LONG_SQL_GROUP',
    'Long-running SQL statements of over 15 minutes.  Low priority.');
 
  /* Create a plan to manage these consumer groups */
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    'REPORTS_PLAN',
    'Plan for daytime that prioritizes short-running queries');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'SYS_GROUP', 'Directive for sys activity',
    mgmt_p1 => 100);
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'OTHER_GROUPS', 'Directive for short-running queries',
    mgmt_p2 => 70,
    parallel_degree_limit_p1 => 4,
    switch_time => 60, switch_estimate => TRUE, switch_for_call => TRUE,
    switch_group => 'MEDIUM_SQL_GROUP');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'MEDIUM_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 20,
    parallel_target_percentage => 80,
    switch_time => 900, switch_estimate => TRUE, switch_for_call => TRUE,
    switch_group => 'LONG_SQL_GROUP');
 
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'REPORTS_PLAN', 'LONG_SQL_GROUP', 'Directive for medium-running queries',
    mgmt_p2 => 10,
    parallel_target_percentage => 50,
    parallel_queue_timeout => 14400);
 
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

/* Allow all users to run in these consumer groups */
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
  'public', 'MEDIUM_SQL_GROUP', FALSE);
 
EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
  'public', 'LONG_SQL_GROUP', FALSE);

Grouping Parallel Statements with BEGIN_SQL_BLOCK .. END_SQL_BLOCK

Often it is important for a report or batch job that consists of multiple parallel statements to complete as quickly as possible. For example, when many reports are launched simultaneously, you may want all of the reports to complete as quickly as possible. However, you also want some specific reports to complete first, rather than all reports finishing at the same time.

If a report contains multiple parallel statements and PARALLEL_DEGREE_POLICY is set to AUTO, then each parallel statement may be forced to wait in the queue on a busy database. For example, the following steps describe a scenario in SQL statement processing:

serial statement
parallel query - dop 8
  -> wait in queue
serial statement
parallel query - dop 32
  -> wait in queue
parallel query - dop 4
  -> wait in queue

For a report to be completed quickly, the parallel statements must be grouped to produce the following behavior:

start SQL block
serial statement
parallel query - dop 8
  -> first parallel query: ok to wait in queue
serial statement
parallel query - dop 32
  -> avoid or minimize wait
parallel query - dop 4
  -> avoid or minimize wait
end SQL block

To group the parallel statements, you can use the BEGIN_SQL_BLOCK and END_SQL_BLOCK procedures in the DBMS_RESOURCE_MANAGER PL/SQL package. For each consumer group, the parallel statement queue is ordered by the time associated with each of the consumer group's parallel statements. Typically, the time associated with a parallel statement is the time that the statement was enqueued, which means that the queue appears to be FIFO. When parallel statements are grouped in a SQL block with the BEGIN_SQL_BLOCK and END_SQL_BLOCK procedures, the first queued parallel statement also uses the time that it was enqueued. However, the second and all subsequent parallel statements receive special treatment and are enqueued using the enqueue time of the first queued parallel statement within the SQL block. With this functionality, the statements frequently move to the front of the parallel statement queue. This preferential treatment ensures that their wait time is minimized.

Managing Parallel Statement Queuing with Hints

You can use the NO_STATEMENT_QUEUING and STATEMENT_QUEUING hints in SQL statements to manage parallel statement queuing.

• NO_STATEMENT_QUEUING

  When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. For example:

  SELECT /*+ NO_STATEMENT_QUEUING */ emp.last_name, dpt.department_name 
    FROM employees emp, departments dpt 
    WHERE emp.department_id = dpt.department_id;
  

• STATEMENT_QUEUING

  When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be delayed and to only run when parallel processes are available to run at the requested DOP. For example:

  SELECT /*+ STATEMENT_QUEUING */ emp.last_name, dpt.department_name 
    FROM employees emp, departments dpt 
    WHERE emp.department_id = dpt.department_id;
  

Processing without Enough Parallel Execution Servers

Oracle Database can process a parallel operation with fewer than the requested number of processes. 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.

See Oracle Database Reference for information about using the initialization parameter PARALLEL_MIN_PERCENT and "Tuning General Parameters for Parallel Execution" for information about the PARALLEL_MIN_PERCENT and PARALLEL_MAX_SERVERS initialization parameters.

Block Range Granules

Block range granules are the basic unit of most parallel operations, even on partitioned tables. Therefore, from an Oracle Database perspective, the degree of parallelism is not related to the number of partitions.

Block range granules are ranges of physical blocks from a table. Oracle Database computes the number and the size of the granules during run-time to optimize and balance the work distribution for all affected parallel execution servers. The number and size of granules are dependent upon the size of the object and the DOP. Block range granules do not depend on static preallocation of tables or indexes. During the computation of the granules, Oracle Database takes the DOP into account and tries to assign granules from different data files to each of the parallel execution servers to avoid contention whenever possible. Additionally, Oracle Database considers the disk affinity of the granules on massive parallel processing (MPP) systems to take advantage of the physical proximity between parallel execution servers and disks.

Partition Granules

When partition granules are used, a parallel server process works on an entire partition or subpartition of a table or index. Because partition granules are statically determined by the structure of the table or index when a table or index is created, partition granules do not give you the flexibility in executing an operation in parallel that block granules do. The maximum allowable DOP is the number of partitions. This might limit the utilization of the system and the load balancing across parallel execution servers.

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.

Partition granules are the basic unit of parallel index range scans, joins between two equipartitioned tables where the query optimizer has chosen to use partition-wise joins, and parallel operations that modify multiple partitions of a partitioned object. These operations include parallel creation of partitioned indexes, and parallel creation of partitioned tables.

You can tell which types of granules were used by looking at the execution plan of a statement. The line PX BLOCK ITERATOR above the table or index access indicates that block range granules have been used. In the following example, you can see this on line 7 of the explain plan output just above the TABLE FULL ACCESS on the SALES table.

-------------------------------------------------------------------------------------------------
|Id|      Operation          |  Name  |Rows|Bytes|Cost%CPU|  Time  |Pst|Pst|  TQ |INOUT|PQDistri|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT         |        |  17| 153 |565(100)|00:00:07|   |   |     |     |        |
| 1| PX COORDINATOR          |        |    |     |        |        |   |   |     |     |        |
| 2|  PX SEND QC(RANDOM)     |:TQ10001|  17| 153 |565(100)|00:00:07|   |   |Q1,01|P->S |QC(RAND)|
| 3|   HASH GROUP BY         |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 4|    PX RECEIVE           |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 5|     PX SEND HASH        |:TQ10000|  17| 153 |565(100)|00:00:07|   |   |Q1,00|P->P | HASH   |
| 6|      HASH GROUP BY      |        |  17| 153 |565(100)|00:00:07|   |   |Q1,00|PCWP |        |
| 7|       PX BLOCK ITERATOR |        | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWC |        |
|*8|        TABLE ACCESS FULL|  SALES | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWP |        |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("CUST_ID"<=22810 AND "CUST_ID">=22300)

When partition granules are used, you see the line PX PARTITION RANGE above the table or index access in the explain plan output. On line 6 of the example that follows, the plan has PX PARTITION RANGE ALL because this statement accesses all of the 16 partitions in the table. If not all of the partitions are accessed, it simply shows PX PARTITION RANGE.

---------------------------------------------------------------------------------------------------
|Id|      Operation                |  Name  |Rows|Byte|Cost%CPU|  Time  |Ps|Ps|  TQ |INOU|PQDistri|
---------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT               |        |  17| 153|   2(50)|00:00:01|  |  |     |    |        |
| 1| PX COORDINATOR                |        |    |    |        |        |  |  |     |    |        |
| 2|  PX SEND QC(RANDOM)           |:TQ10001|  17| 153|   2(50)|00:00:01|  |  |Q1,01|P->S|QC(RAND)|
| 3|   HASH GROUP BY               |        |  17| 153|   2(50)|00:00:01|  |  |Q1,01|PCWP|        |
| 4|    PX RECEIVE                 |        |  26| 234|    1(0)|00:00:01|  |  |Q1,01|PCWP|        |
| 5|     PX SEND HASH              |:TQ10000|  26| 234|    1(0)|00:00:01|  |  |Q1,00|P->P| HASH   |
| 6|      PX PARTITION RANGE ALL   |        |  26| 234|    1(0)|00:00:01|  |  |Q1,00|PCWP|        |
| 7|       TABLEACCESSLOCAL INDEX ROWID|SALES| 26| 234|    1(0)|00:00:01| 1|16|Q1,00|PCWC|        |
|*8|        INDEX RANGE SCAN       |SALES_CUST|26|    |    1(0)|00:00:01| 1|16|Q1,00|PCWP|        |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("CUST_ID"<=22810 AND "CUST_ID">=22300)

Limiting the Number of Available Instances

In Oracle Real Application Clusters, services are used to limit the number of instances that participate in a parallel SQL operation. The default service includes all available instances. You can create any number of services, each consisting of one or more instances. Parallel execution servers are to be used only on instances that are members of the specified service.