how to check degree of parallelism in oracle & Default Parallelism

Last updated on September 1st, 2019 at 05:38 pm

how to check degree of parallelism in oracle

Suppose we execute the below 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

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

Leave a Reply