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

From: Stefan Koehler <contact_at_soocs.de>
Date: Sat, 18 Jul 2015 13:14:15 +0200 (CEST)
Message-ID: <525470925.161120.1437218055353.JavaMail.open-xchange_at_app10.ox.hosteurope.de>

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.

_at_ 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: _at_OracleSK

> Charles Schultz <sacrophyte_at_gmail.com> 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_at_soocs.de <mailto: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

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

Received on Sat Jul 18 2015 - 13:14:15 CEST