RE: parallel_adaptive_multi

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Mon, 12 Feb 2007 16:08:44 -0600
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3602D1A37C@CWYMSX04.Corp.Acxiom.net>

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_at_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.

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

Received on Mon Feb 12 2007 - 16:08:44 CST

RE: parallel_adaptive_multi_user