Suppose we execute the below parallel query in Oracle database
select /*+ PARALLEL(JN) */ count(*) from ar.RA_INTERFACE_LINES_ALL jn;
Here we can see , we have not specified the degree of the parallelism.Now there are two cases
i. if we have defined degree at the table level, then that degree will be used for parallel processing
SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME = '<table_name>';
ii. In-case, No degree is specified at the table level, then default parallelism degree will be used
How the parallelism is determined
Now lets see how it is determined
If the PARALLEL clause is specified but no degree of parallelism is listed, the object gets the default DOP. Default parallelism uses a formula to determine the DOP based on the system configuration, as in the following:
For a single instance
DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
For an Oracle RAC configuration
DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT
By default, INSTANCE_COUNT is all of the nodes in the cluster. However, if you have used Oracle RAC services to limit the number of nodes across which a parallel operation can execute, then the number of participating nodes is the number of nodes belonging to that service. For example, on a 4-node Oracle RAC cluster, with each node having 8 CPU cores and No Oracle RAC services, the default DOP would be 2 x 8 x 4 = 64.
The default DOP algorithm is designed to use maximum resources and assumes that the operation finishes faster if it can use more resources. Default parallelism targets the single-user workload. In a multiuser environment, default parallelism is not recommended.
Also This just decides the degree of the parallelism but the number of max parallel workers in the database is defined by parallel_max_servers parameter. So DOP is less than parallel_max_servers, then degree of parallelism will be DOP and if DOP > parallel_max_servers,then degree will be parallel max servers
Suppose we execute the below query in Oracle database
select /*+ PARALLEL(JN,4) */ count(*) from ar.RA_INTERFACE_LINES_ALL jn;
Here we know that degree of parallelism used is 4. Now 4 will be used if we have available free parallel server to execute the process
Also Reads
Automatic Workload Repository
Oracle DBA interview Questions
change date format in oracle