cpu_count

[dasdeba]

Hi
If this parameter is set , then should i specify any value for parallel parameter at the time of creating table or any parallel degree at time time of running a sql or any parallel degree at the session level in order to use parallel slaves ?

I want to know whether Oracle uses parallel clause whenever it is applicable to use when cpu_count is set.

Deba

[Thomasps]

It is not mandatory to set the PARALLEL query option if the cpu_count is more than 1. But Oracle calculates the default degree of Parallelism as

CPU_COUNT * PARALLEL_THREADS_PER_CPU

Having PARALLEL_ADAPTIVE_MULTI_USER set to true causes the degree of parallelism to be calculated as PARALLEL_THREADS_PER_CPU * CPU_COUNT * (a reduction factor). This mechanism will dynamically size the degree of parallelism based on cpu_count and current work load.

But the run time Parallelism is depends on various parameteres such as

PARALLEL_MAX_SERVERS
PARALLEL_MIN_PERCENT
PARALLEL_AUTOMATIC_TUNING


Regards

Thomas

[dasdeba]

Can I mannually set PARALLEL_ADAPTIVE_MULTI_USER and PARALLEL_THREADS_PER_CPU ? If so, what is basis of setting these two parameters ? How reduction factor is calculated and how to control this reduction factor ?

Deba

[Thomasps]

Yes, Definitely. You can set these parameters manually.

From Docs.
PARALLEL_ADAPTIVE_MULTI_USER =True/False => when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor. So set this if you want Oracle to handle parallelism dynamically.

Example: On a 17 CPU machine the default degree of parallelism could be set to 32. If one user issues a parallel query, that user gets a degree of 32, effectively using all of the CPUs and memory in the system. When a second user enters the system, that user gets a degree of 16. As the number of users on the system increases, this algorithm will continue to reduce the degree until the users are running using degree 1, when there are 32 users on the system.

PARALLEL_THREADS_PER_CPU=integer (default value 2) => specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution, ie the computing power of CPU. 2 is the recomeded value by Oracle.

As long as my understanding DBA have no control over the reduction factor. Oracle is calculating the reduction factor from a "Complicated Algorithm" (metalink) based on the available resources and usage.

Regards

Thomas