Default degree of parallelism in oracle hints

914 views Asked by At

I have used default degree of parallelism in order to gain performance tuning and I got the best results too. but I doubt it will impact when some other job access the same table at same time.

sample code below.

select /*+ FULL(customer) PARALLEL(customer, default) */ customer_name  from customer;

The number of servers available is 8 . How this default degree of parallelism works? will it affect if some other job running query on same table at same time? Before moving this query to production , I would like to know whether this will impact ? Thanks!

1

There are 1 answers

0
Lalit Kumar B On BEST ANSWER

From documentation:

PARALLEL (DEFAULT):

The optimizer calculates a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

The maximum degree of parallelism is limited by the number of CPUs in the system. The formula used to calculate the limit is :

PARALLEL_THREADS_PER_CPU * CPU_COUNT * the number of instances available

by default, all the opened instances on the cluster but can be constrained using PARALLEL_INSTANCE_GROUP or service specification. This is the default.