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:
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.
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.
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.
Enabling and disabling the automatic parallel features is made by the PARALLEL_DEGREE_POLICY parameter:
Hope you enjoyed,
Liron, This email address is being protected from spambots. You need JavaScript enabled to view it.