RE: parallel_adaptive_multi_user
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 12 Feb 2007 16:08:44 -0600
In case anyone is interested, I found an old article from a metalink support
person where the article had yet to be officially published, hence no
article/doc ID. On this subject it states:
5. DOP Algorithms
-----------------
A requested DOP will be subject to modification for a number of reasons :
- the number of slaves is not available,
- there are more queries running and so the requested DOP presents a
resource problem.
The first case (insufficient slaves) is well understood. If there are not
enough slaves available then either the slaves will be allocated from another
node (in the IPQ case) or the query will run with reduced parallelism.
The second case will only pertain if parallel_adaptive_multi_user is set to
true and if the current PX load is greater than the target load. In that case
the degree of parallelism will be reduced dependent on the number of server
processes (not just PQ slaves) that are running.
In 8.0 it was the case that only the number of parallel queries running on the
QC node were taken into account. In 8i the number of server shadow process is
used. The degree of parallelism is reduced by a factor related to the estimated
capacity of the machine and the current load.
Load + DefaultDOP
rfact = ----------------- X ln(# of users)
TARGETLoad
TARGETLoad = DefaultDOP * maximum number of PX users.
I have a bit of testing to perform now, to confirm if this formula is still
valid under 9i. Event 10392 didn't give any details related to the formula,
just what the reduction was.
HTH.
Dave
___________________________________
David C. Herring, DBA | A c x i o m D e l i v e r y C e n t e r O r g a n
i z a t i o n
630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax
Database env is 9.2.0.6 EE
OS is Tru64 5.1
Does anyone have a formula for how Oracle determines the reduction factor when
parallel_adaptive_multi_user = TRUE (and obviously parallel_automatic_tuning =
TRUE)? I've relied up to this point on using PARALLEL DEFAULT on all objects,
as our dev, test, and production environments have different CPU counts and I'd
like relative performance. But, as the project keeps expanding our servers are
(fortunately) expanding as well. Production started with 8 CPUs, now has 10,
and will shortly be upgraded to 12. The larger tables aren't growing relative
to this CPU count. Instead, we've got new sets of tables and more users, so
I'd prefer the lower default parallelism of 8.
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?
Thanks.
Dave
-------------------------------------
Dave Herring, DBA
Acxiom Corporation
3333 Finley
Downers Grove, IL 60515
wk: 630.944.4762
<mailto:dherri@xxxxxxxxxx>
-------------------------------------
"When I come home from work and see those little noses pressed against the
windowpane, then I know I am a success" - Paul Faulkner
***************************************************************************
*************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be
legally privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank you.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
