Oracle tries to automate the configuration of the system to maximize the performance of parallel operations, However there's still  a lot of scope for manual tweaking. Increasing the DOP beyond and optimal point fails to result in further performance increase.

DETERMINING THE DEGREE OF PARALLELISM (DOP): An optimal DOP is critical for good  parallel performance. Oracle determines the DOP as follows.

1. If parallel execution is indicated or requested, but no DOP is specified, the default DOP is set to twice the no of CPU cores on the system. For a RAC system, the DOP will be twice the number of cores in the entire cluster. This default is controlled by the configuration parameter PARALLEL_THREADS_PER_CPU.

2. From Oracle 11g R2 forward, if PARALLEL_DEGREE_POLICY is set to auto, Oracle will adjust the DOP depending on the nature of the operation to be performed and the sizes of the objects involved.

3. If PARALLEL_ADAPTIVE_MULTI_USER is set to TRUE, Oracle will adjust the DOP based on the overall load on the system. When the system is more heavily loaded , the DOP will be reduced.

4. if PARALLEL_IO_CAP is set to TRUE in Oracle 11g or higher, Oracle will limit the DOP to that which the IO subsystem can support. These IO subsystem limit can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. A DOP can be specified at the table or index level by using PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE, or ALTER INDEX.

6. A PARALLEL hint can be used to specify the DOP for a specific table within a query.

7. Regardless of any other setting, the DOP can not exceed that which can be supported by PARALLEL_MAX_SERVERS.  For most SQL statements, the no of servers required will be twice the requested DOP.


NOTE: For ad-hoc query execution, you might want to set an explicit DOP. However, for SQL embedded within an application, this might not be good idea because the SQL will be less able to adapt to change in machine configuration (more CPU for instance), workload(more concurrent sessions), or configuration(change in the no of parallel slaves or default DOP). For embedded SQL, it's probably better to omit an explicit DOP or to use the AUTO keyword (in Oracle 11g R2 and higher).