Dave, The algorithm is quite simple. We just use the cpu_count and parallel_threads_per_cpu to define our upper limit. With in that upper limit, we just allocate the required parallel degree with in our limit. The reduction factor is based on the number of active users on the system. In earlier versions , there used to be bug which doesn't ignore the idle sessions and causing some issues. It is fixed in 9205 and above and the algorithm works pretty efficient these days.
I'm reluctant to set parallel_adaptive_multi_user = TRUE without first having a good idea of how Oracle chooses to reduce the default degree. Manuals say its based on system load and active parallel users. Great, but is load solely on CPU % used or some other resource combination? For active parallel users, is it based on the total of PX slaves across the entire database or just on the objects involved? For both factors, what reduction factor is used?
It does care the objects, but it applies the reduction factor on top of the object's parallelism. You can use 10392 event to trace the execution of the parallel adaptive multiuser algorithm. -- Best Regards, K Gopalakrishnan Co-Author: Oracle Wait Interface, Oracle Press 2004 http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/ Author: Oracle Database 10g RAC Handbook, Oracle Press 2006 http://www.amazon.com/gp/product/007146509X/ -- http://www.freelists.org/webpage/oracle-l