This is a post I wrote quite a while ago, and it’s about 11.2 parallel features. It’s a little out dated, now that 12c is out for a while and we are all waiting for 12cR2. But I decided to publish it here, and maybe I’ll write another one on parallel in 12c-12cR2 in the future.
Oracle 11gR2 introduced several new features related to parallelism, all of them 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.
- 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 operation 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 using 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