Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: parallel_adaptive_multi_user

RE: parallel_adaptive_multi_user

From: Leng Kaing <Leng.Kaing_at_hsntech.com>
Date: Thu, 25 Jan 2007 17:08:31 +1100
Message-ID: <18D551B1B928FF47A65B2D91F705906A03732EC8@HSNDON-EX01.hsntech.int>

I was going to turn this parameter on for our 9.2.0.3 database this weekend and luckily found that the bug is in fact fixed in 9.2.0.3 and above:

Bug 2533038
<https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=2533038> Fixed in 9.2.0.3 onwards.
Details:
PARALLEL_ADAPTIVE_MULTI_USER load calculation is sensitive to IDLE sessions.
Idle sessions should not add to load related calculations.

Re: parallel_adaptive_multi_user

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/

 

 

------------------------------------------------------------------

Leng Kaing

Senior Oracle DBA

Hansen Technologies; 2 Frederick St; Doncaster 3108

Ph: +61-3-9840-3832

 


--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 25 2007 - 00:08:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US