H.2.1.2 PARALLEL_DEGREE_POLICY

A new feature in Oracle 11gR2, PARALLEL_DEGREE_POLICY specifies whether or not automatic Degree of Parallelism, statement queuing, and in-memory parallel execution is enabled. These three topics are covered in depth by the documentation resources found at the end of this appendix.

MANUAL

When the PARALLEL_DEGREE_POLICY option in the FSI_PARALLEL_PARAMETERS table is set to MANUAL, the engine will begin each rule by issuing the following statement:

ALTER SESSION SET PARALLEL_DEGREE_POLICY = 'MANUAL';

The subsequent behavior of both Parallel Query & Parallel DML is the same as it would have been before Oracle 11gR2. SQL statements are only processed in parallel for objects declared with a PARALLEL clause or if a parallel SQL hint is included. Additionally, the Oracle Insurance Allocation Manager for Enterprise Profitability PARALLEL_QUERY parameter must be set to either ENABLE or FORCE (for Parallel Query) and the PARALLEL_DML parameter must be set to either ENABLE or FORE (for Parallel DML).

LIMITED

When the PARALLEL_DEGREE_POLICY option in the FSI_PARALLEL_PARAMETERS table is set to LIMITED, the engine will begin each rule by issuing the following statement:

ALTER SESSION SET PARALLEL_DEGREE_POLICY = 'LIMITED';

Under the LIMITED policy, an automatic Degree of Parallelism is enabled for some statements, but statement queuing and in-memory Parallel Execution is disabled. Automatic Degree of Parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements executed against tables or indexes defined using an explicit Degree of Parallelism through the PARALLEL clause, or statements including a parallel SQL hint may also run in parallel.

AUTO

When the PARALLEL_DEGREE_POLICY option in the FSI_PARALLEL_PARAMETERS table is set to AUTO, the engine will begin each rule by issuing the following statement:

ALTER SESSION SET PARALLEL_DEGREE_POLICY = 'AUTO';

Under the AUTO policy, the automatic Degree of Parallelism, statement queuing, and in-memory parallel execution features are all enabled. Under this policy, the Oracle database automatically decides if a statement should execute in parallel or not and what Degree of Parallelism it should use. The database also determines if you can execute the statement immediately or if it should be queued until more system resources are available. Finally, the database decides if the statement can take advantage of the aggregated cluster memory or not.