Hi Charles,
there are several bugs with "_px_trace" (e.g. #7443854, #7173130, etc.), but it
should not be a daily-doing-thing in general ;-))
However "_px_trace" is also not like the other hidden/underscore parameters -
it is officially documented in MOS ID #400886.1 and #444164.1 and so
more "safely" to use.
It seems like my previous assumption about "parallel_adaptive_multi_user" was
in the right ball park as Randolf also joined in and pointed in this
direction.
@ Randolf: Do you have any paper about the Auto DOP or
parallel_adaptive_multi_user algorithm?
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Charles Schultz <sacrophyte@xxxxxxxxx> hat am 17. Juli 2015 um 16:04--
geschrieben:
Stefan,
There is a bug whereby _px_trace can cause parallel slaves to go off to
limbo land and consume enormous amounts of CPU without actually doing any
work, and not terminating either. I filed an SR on it last night, so awaiting
the official bug number.
Just a note to you and the general public to be careful with this tracing.
:) I suppose it is an underscore parameter for a reason, eh? :)
On Fri, Jul 17, 2015 at 5:16 AM, Stefan Koehler <contact@xxxxxxxx
<mailto:contact@xxxxxxxx> > wrote:
> > Hi Charles,
thank you very much for the PX traces. My assumption about some kind of
adaptive feature was right as you can see in the trace file
("adaptive=on").
-----------------8<---------------------------
2015-07-15 15:35:19.992634*:PX_Messaging:kxfp.c@9923:kxfpgsg(begin):
reqthreads=100 height=0 lsize=0 alloc_flg=0x230
2015-07-15 15:35:19.992634*:PX_Messaging:kxfp.c@9996:kxfpgsg():
reqthreads=100 KXFPLDBL/KXFPADPT/ load balancing=on adaptive=on
-----------------8<---------------------------
However your assumption about parallel_degree_limit=CPU and
parallel_degree_policy=MANUAL can not be true imo as parallel_degree_limit
is used
for
AutoDOP. In addition in your first case you would limit it to 2
(PARALLEL_THREADS_PER_CPU x CPU_COUNT x number of instances available) in
any
case.
This also fits to your study (parallel_degree_policy=AUTO +
parallel_degree_limit=100) in the second test case.
In your initial case (BANIMP_ora_25263.trc) i would count on
parallel_adaptive_multi_user. Can you test it by setting only parameter
parallel_adaptive_multi_user to FALSE? Unfortuantely the exact algorithm
is not known (or at least i never have found anything in great detail
about
it).
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
