PARALLEL_THREADS_PER_CPU

Property Description
Parameter type Integer
Default value Operating system-dependent, usually 2
Modifiable ALTER SYSTEM
Range of values Any nonzero number

Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application
Clusters environment.

PARALLEL_THREADS_PER_CPU 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.

The default is platform-dependent and is adequate in most cases. You should decrease the value
of this parameter if the machine appears to be overloaded when a representative parallel query
is executed. You should increase the value if the system is I/O bound.

Goal

The purpose of this parameter "PARALLEL_THREADS_PER_CPU"

Solution

PARALLEL_THREADS_PER_CPU enables you to adjust for hardware configurations with I/O subsystems
that are slow relative to the CPU speed and for application workloads that perform few
computations relative to the amount of data involved. If the system is neither CPU-bound nor
I/O-bound, then the PARALLEL_THREADS_PER_CPU value should be increased. This increases the default
DOP and allow better utilization of hardware resources.


DOP specifies the number of available processes, or threads, used in parallel operations. Each
parallel thread may use one or two query processes depending on the query's complexity.
The adaptive multi-user feature adjusts DOP based on user load. For example, you may have a table
with a DOP of 5. This DOP may be acceptable with 10 users. But if 10 more users enter the system
and you enable the PARALLEL_ADAPTIVE_MULTI_USER feature, Oracle reduces the DOP to spread
resources more evenly according to the perceived system load.

It is best to use the parallel adaptive multi-user feature when users process simultaneous
parallel execution operations.

The adaptive multi-user algorithm has several inputs. The algorithm first considers the number of
allocated threads as calculated by the database resource manager. The algorithm then considers the
default settings for parallelism as set in INIT.ORA, as well as parallelism options used in CREATE
TABLE and ALTER TABLE commands and SQL hints.

When a system is overloaded and the input DOP is larger than the default DOP, the algorithm uses
the default degree as input. The system then calculates a reduction factor that it applies to the
input DOP.

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.

2. From the session where the query run, type:
select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------------------------------------
Queries Parallelized 1 1

The above row tells us that the last query run in this session was parallelized.

3. Check in v$ views for slave activity:
Select from v$pq_slave a couple of times:

SELECT slave_name,status, cpu_secs_total
FROM v$pq_slave;

v$session wait can also be queried to look for PQ activity.

You can detect the use with the following statement for a version greater than 8.x

SELECT sid, event, seq#,p1,p2,p3, wait_time
FROM v$session_wait
WHERE upper(event) like ('PX%')
ORDER BY 1;

4. Trace the Query coordinator using <Event:10046> Level 12:
Look for parallel query wait events in the resultant trace file. Also look for trace files from the query slaves.