oracle 8i: parallel_threads_per_cpu parameter

[Paolo Gilli]

I have upgrade oracle 8063 to 8174, in the init.ora file the values of parallel_threads_per_cpu is 2.
The sistem is an hp-ux 9000 class n4000 with 4 cpu 440MHz 2Gb ram, 500Gb disk array storage (sc10+fc60), OS hp-ux 11.00

Is the parameter parallel_threads_per_cpu =2 ok, or whith oracle 8i it can be modified with an higer value to improve system performance?

thank.

[Ceesjan van Hattum]

Keep in mind: DOP (degree of parrallism)= #CPU*PARALLEL_THREADS_PER_CPU.
DOP=4*2=8

But it should always be considered with 'adaptive multi-user feature, memory sizing and 'parrallel_automatic_tuning=TRUE'.

Read for details of Tuning Parallel Execution:
http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch24_pex.htm

--->
The initialization parameter PARALLEL_THREADS_PER_CPU affects algorithms controlling both the DOP and the adaptive multi-user feature. Oracle multiplies the value of PARALLEL_THREADS_PER_CPU by the number of CPUs per instance to derive the number of threads to use in parallel operations.

The adaptive multi-user feature also uses the default DOP to compute the target number of query server processes that should exist in a system. When a system is running more processes than the target number, the adaptive algorithm reduces the DOP of new queries as required. Therefore, you can also use PARALLEL_THREADS_PER_CPU to control the adaptive algorithm.

The default for PARALLEL_THREADS_PER_CPU is appropriate for most systems. However, if your I/O subsystem cannot keep pace with the processors, you may need to increase the value for PARALLEL_THREADS_PER_CPU. In this case, you need more processes to achieve better system scalability. If too many processes are running, reduce the number.

The default for PARALLEL_THREADS_PER_CPU on most platforms is 2. However, the default for machines with relatively slow I/O subsystems can be as high as 8.
<---

Regards,
Ceesjan

[Jean-Luc Oudart]

According to Oracle the default for the machine should be allright.

More information cf. Metalink doc id 68604.1

Jean-Luc
Metalink : http://support.oracle.co.uk/