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

From: Stefan Koehler <contact_at_soocs.de>
Date: Fri, 17 Jul 2015 12:16:41 +0200 (CEST)
Message-ID: <1431555621.152286.1437128201914.JavaMail.open-xchange_at_app02.ox.hosteurope.de>

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
>
 

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

Received on Fri Jul 17 2015 - 12:16:41 CEST