Enabling Automatic Degree of Parallelism (DOP) in Oracle

Init.ora file parameter PARALLEL_DEGREE_POLICY to AUTO allow Oracle to find out SQL Statement run in parallel or Single thread.
This parameter helps in Enterprise Edition of Oracle.
With this parameter value set to Auto, you instructs the Oracle to choose automatically about parallelism used for SQL Statement.

Enable

-- At system level
alter system set parallel_degree_policy=auto scope=both;
-- At session level
alter session set parallel_degree_policy=auto;


Disable

-- At system level
alter system set parallel_degree_policy=manual scope=both;


Parallel_degree_auto has three values:
MANUAL: it is default which is disable the parallelism.
LIMITED: Oracle to use automatic DOP only on those objects with parallelism explicitly set.
AUTO: Oracle having all rights to set automatic DOP.

Steps follow for choosing the Statement used parallelism or Skip:
1. Parsing
2. Checked another parameter PARALLEL_MIN_TIME_THRESHOLD
3. If value is less then threshold value, then statements runs without parallelism.
4. If value is greater then threshold value, then statements runs with parallelism.

Other Parameters configured for Parallelism in Oracle Database:
Parallel_degree_limit: DOP is determined by no of CPU on the system or I/O requirement of a given query.
Note: One prerequisite of using automatic DOP is to run the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure run only once, and gathers information about the hardware on your system.

Parallel_max_servers: Set the maximum number of parallel processes (from 0 to 3600) for a database instance.

Parallel_min_servers: Set the minimum number of parallel processes for a database instance. Setting to a non-zero value keeps that minimum number of parallel processes for Statements.

Parallel_servers_target: let the oracle decide how many parallel processes can run at one time before query statements requiring parallel execution begin to be queued for execution.

PARALLEL_QUEUE_TIMEOUT: attribute enables you to specify the maximum
time, in seconds, that a parallel statement can wait in the parallel statement queue before it is timed out.