Home » Oracle » Oracle Database » How to check degree of parallelism in oracle & Default Parallelism

How to check degree of parallelism in oracle & Default Parallelism

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

See also  How to check failed login attempts in Oracle

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top