Parallel Features in Oracle 11gR2

Oracle 11gR2 brings us new features related to parallelism. The new features are controlled by the PARALLEL_DEGREE_POLICY initialization parameter.

Automatic Parallel Degree

When PARALLEL_DEGREE_POLICY is set to AUTO, automatic parallel degree is enabled. Oracle will evaluate the execution time of the statement, and if it exceeds the value of PARALLEL_MIN_TIME_THRESHOLD parameter (10 seconds by default) Oracle will calculate an automatic degree of parallelism (DOP) for this statement.

Oracle calculates the DOP based on the statement requirements and the load on the server. The DOP can be limited by the parameter PARALLEL_DEGREE_LIMIT:

• CPU (default) - will set the maximum DOP to the value of PARALLEL_THREADS_PER_CPU * CPU_COUNT * # of instances (in RAC).
• IO - will limit the DOP according to the I/O of the system (available only after running DBMS_RESOURCE_MANAGER.CALIBRATE_IO).
• Integer value - will limit the DOP to this integer.

When PARALLEL_DEGREE_POLICY is set to LIMITED, Oracle will automatically determine the DOP for the statements. However, statements that are not specified to run in parallel, will run in serial.

Statement Queuing

When a statement is executed, Oracle determines the DOP for this statement. If creating the calculated number of parallel processes will cause the total number of active parallel processes in the system to exceed the PARALLEL_SERVERS_TARGET, this statement will be queued.

The queue of statements is based on the simple "first in first out" mechanism. Once there are enough parallel processes available, the statement will start executing.
Using resource manager, we can set priority in the statement queue, timeout for statements and manage the amount of parallel processes for statements.

In Memory Parallel Execution

When running a parallel operation, the server process is reading the data from the file using direct IO operations into its PGA. The data blocks are not cached in the SGA and are not read from it. The in memory parallel execution feature enables the server processes to load the blocks to the SGA and read them from the SGA.
If the object is too large to fit in the buffer cache, Oracle will still use direct read.

Disable Automatic Parallel Features

Enabling and disabling the automatic parallel features is made by the PARALLEL_DEGREE_POLICY parameter:

• AUTO - all of the above features are enabled.
• LIMITED - in memory parallel execution and statement queuing will be disabled, the automatic DOP will be enabled only for queries that were set to run in parallel.
• MANUAL - disable all the automatic parallel features and revert parallel behavior to pre-11gR2

Hope you enjoyed,
Liron, This email address is being protected from spambots. You need JavaScript enabled to view it.