PARALLEL_THREADS_PER_CPU

Property DescriptionITPUB个人空间*r;}g{k:Jr
Parameter type IntegerITPUB个人空间#K:y [;wVZ-i
Default value Operating system-dependent, usually 2ITPUB个人空间,V|)u3R u!rd
Modifiable ALTER SYSTEM
YvRG^Z0Range of values Any nonzero numberITPUB个人空间$v1Q8Z*X}
ITPUB个人空间q2t#`^ m*v.f rVB
Note:

g6u3s*_/LB0This parameter applies to parallel execution in exclusive mode as well as in a Real ApplicationITPUB个人空间6~;k,P\f$^
Clusters environment.ITPUB个人空间A+nd1|4\f$`Z4dy x
ITPUB个人空间Lyb Mb
PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance andITPUB个人空间kiQ7VQ+~F?
determines the parallel adaptive and load balancing algorithms. The parameter describes the
hl5C-bGT0number of parallel execution processes or threads that a CPU can handle during parallel execution.ITPUB个人空间z9w3r/].p:lj

Tz0I-f*Y9^0The default is platform-dependent and is adequate in most cases. You should decrease the valueITPUB个人空间\2{7Iqd&U9`w
of this parameter if the machine appears to be overloaded when a representative parallel queryITPUB个人空间\ MoIo
iY,? l
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
;G4zg(GkjB_2F0that are slow relative to the CPU speed and for application workloads that perform. few
jW8PJ4IFO0Vf0computations relative to the amount of data involved. If the system is neither CPU-bound norITPUB个人空间zPQkb ~2{
I/O-bound, then the PARALLEL_THREADS_PER_CPU value should be increased. This increases the default
cdA$u"VwZ0DOP and allow better utilization of hardware resources.
C'J9Q'w-z.]3UD0
9mh&xv2]0ITPUB个人空间'O)Y(Br(_'s1G*Gy k`
DOP specifies the number of available processes, or threads, used in parallel operations. Each
:~:L]8RV+{x5P}0parallel thread may use one or two query processes depending on the query's complexity.
.nz"C-r,kg?0The adaptive multi-user feature adjusts DOP based on user load. For example, you may have a table
6W6n%nD:W'^6@0with a DOP of 5. This DOP may be acceptable with 10 users. But if 10 more users enter the systemITPUB个人空间\t8DfjK V~P(f
and you enable the PARALLEL_ADAPTIVE_MULTI_USER feature, Oracle reduces the DOP to spreadITPUB个人空间'j8h2C6bv[;ZM
resources more evenly according to the perceived system load.
C2Y~:mUjqcE0
1o[r Nn0It is best to use the parallel adaptive multi-user feature when users process simultaneousITPUB个人空间-kdEtF(GKV
parallel execution operations.ITPUB个人空间k(l"k
qj]'j
ITPUB个人空间 VpyRos~r
The adaptive multi-user algorithm has several inputs. The algorithm first considers the number of
#B[4G.S*h Nevb1^$y0allocated threads as calculated by the database resource manager. The algorithm then considers theITPUB个人空间%XfS7zu9Gr@
default settings for parallelism as set in INIT.ORA, as well as parallelism options used in CREATE
^ g7W%Cnm^&XxC0TABLE and ALTER TABLE commands and SQL hints.ITPUB个人空间`X1t*D,B0N*|BW
ITPUB个人空间'V-z1hM"E/W l/P
When a system is overloaded and the input DOP is larger than the default DOP, the algorithm uses
WXC s%b-L0the default degree as input. The system then calculates a reduction factor that it applies to theITPUB个人空间"W GaN3W9ut4b(fWc
input DOP.ITPUB个人空间L]:X]5Lit
ITPUB个人空间 t zuL;h:xb,bZ
The initialization parameter PARALLEL_THREADS_PER_CPU affects algorithms controlling both the DOPITPUB个人空间 VE uka'o)L ?
and the adaptive multi-user feature. Oracle multiplies the value of PARALLEL_THREADS_PER_CPU by
.Y3tB
E)N B l5Q`0the number of CPUs per instance to derive the number of threads to use in parallel operations.
@`Qoy(^'C0
(R'kZ7H0]3L0The adaptive multi-user feature also uses the default DOP to compute the target number of queryITPUB个人空间(@$N Tq)h8[3`2B
server processes that should exist in a system. When a system is running more processes than theITPUB个人空间Gw CTx J CB9nDc
target number, the adaptive algorithm reduces the DOP of new queries as required. Therefore, youITPUB个人空间5iY5s;h8^Z
can also use PARALLEL_THREADS_PER_CPU to control the adaptive algorithm.
f9Y
h7LZM2]#F0ITPUB个人空间,Yz7JJIa(q,ey
The default for PARALLEL_THREADS_PER_CPU is appropriate for most systems. However, if your I/OITPUB个人空间hjZfM(dZi-[
subsystem cannot keep pace with the processors, you may need to increase the value forITPUB个人空间je'zQc5I_
PARALLEL_THREADS_PER_CPU. In this case, you need more processes to achieve better system
"PTy&X(Ok tG]1d$s7g0scalability. If too many processes are running, reduce the number.

2. From the session where the query run, type:ITPUB个人空间+H d Z4b;t"ZvSn
select * from v$pq_sesstat;ITPUB个人空间v Xko*Ob
ITPUB个人空间
xUom Q
STATISTIC LAST_QUERY SESSION_TOTALITPUB个人空间zY3ia?!O
------------------------------------------------------------
Qpt1R{ jK~9j)D0Queries Parallelized 1 1ITPUB个人空间+tTtt9L
ITPUB个人空间j[9sa?al
The above row tells us that the last query run in this session was parallelized.ITPUB个人空间*[vPt1p;i6vkA
ITPUB个人空间Bp~XGD
3. Check in v$ views for slave activity:
Nc/r?Uf)x1j)cU0Select from v$pq_slave a couple of times:ITPUB个人空间 f+M})zsi-v#m~%E
ITPUB个人空间 {.h Ue*Ti
SELECT slave_name,status, cpu_secs_total
Jb$z/L/b XR(_0FROM v$pq_slave;

v$session wait can also be queried to look for PQ activity.ITPUB个人空间ii3cj3x2x
i
ITPUB个人空间Q$@#}Qd$R7Z
You can detect the use with the following statement for a version greater than 8.x
Ag\#MT6T0
VA1PAH#Th%T0SELECT sid, event, seq#,p1,p2,p3, wait_time
LyI @_$L P"HC0FROM v$session_waitITPUB个人空间ITbec{X(uD
WHERE upper(event) like ('PX%')ITPUB个人空间.B}VQ@Tf
ORDER BY 1;

4. Trace the Query coordinator using <Event:10046> Level 12:ITPUB个人空间$^h#c-HOCmTeD
Look for parallel query wait events in the resultant trace file. Also look for trace files from the query slaves.

M9a.BJ9R#b*I{0