The initialization parameter PARALLEL_DEGREE_POLICY controls whether automatic degree of parallelism (DOP), parallel statement queuing, and in-memory parallel execution are enabled. This parameter has the following possible values:
MANUAL - Disables automatic DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g Release 2 (11.2), which is the default.
LIMITED - Enables automatic DOP for some statements but parallel statement queuing and in-memory parallel execution are disabled. Automatic DOP is applied only to statements that access tables or indexes declared explicitly with the PARALLEL clause, or are defined as PARALLEL without an explicit DOP specified. Tables and indexes that have a DOP specified use that explicit DOP setting
AUTO - Enables automatic DOP, parallel statement queuing, and in-memory parallel execution.
ADAPTIVE - Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution, similar to the AUTO value. In addition, performance feedback is enabled.
By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement. The degree of parallelism used is exactly what was specified. No parallel statement queue occurs and parallel execution does not use the buffer cache. For information about the parallel statement queue, refer to "About Parallel Statement Queuing".
If you want Oracle Database to automatically decide the degree of parallelism only for a subset of SQL statements that touch a specific subset of objects, then set PARALLEL_DEGREE_POLICY to LIMITED and set the parallel property on that subset of objects. If you want Oracle Database to automatically decide the degree of parallelism for all SQL statements, then set PARALLEL_DEGREE_POLICY to AUTO.
When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics. The hardware characteristics include I/O calibration statistics so these statistics should be gathered.
If I/O calibration is not run to gather the required statistics, a default calibration value is used to calculate the cost of operations and the degree of parallelism.
I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. I/O calibration is a one-time action if the physical hardware does not change.
Oracle Database Reference for information about settings for the PARALLEL_DEGREE_POLICY initialization parameter
Oracle Database Performance Tuning Guide for information about diagnosing and tuning database performance
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESOURCE_MANAGER package
Oracle Database SQL Language Reference for information about the PARALLEL hint