Re: Curiosity: single-column index on sparse data cannot be built in parallel

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_at_soocs.de> 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_at_9923:kxfpgsg(begin):
> reqthreads=100 height=0 lsize=0 alloc_flg=0x230
> 2015-07-15 15:35:19.992634*:PX_Messaging:kxfp.c_at_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: _at_OracleSK
>
> > Charles Schultz <sacrophyte_at_gmail.com> hat am 16. Juli 2015 um 16:58
> geschrieben:
> >
> > I think I finally got to the bottom of this - running more tests to
> confirm. It seems like, due to parallel_degree_limit=CPU,
> > parallel_degree_policy=MANUAL, and the default DOP being so low (2), the
> kernel decided the CPU was too loaded to grant the requested DOP, so
> > instead calcuated that a DOP of 2 would be easier on the CPU.
> Unfortunately, while this is indeed true, it kills performance.
> >
> > It seems like using parallel_degree_policy=AUTO and
> parallel_degree_limit=100 (or some other high number, maybe even "IO") will
> avoid the kernel
> > freaking out because the CPU has a little load on it, and the added
> advantage is that AUTO will defer until more slaves are available. Going to
> bump
> > cpu_count=256 for another test, as well.
> >
> > Hat tip to Anju Garg's blog Oracle In Action <
> http://oracleinaction.com/tag/parallel_adaptive_multi_user/> (has several
> posts on parallelism).
> > Thanks again to Jonathan Lewis and Stefan Koehler for helping me get
> started on the science and diagnostics.
> >
> >
> > --
> > Charles Schultz
> >
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l