RE: PARALLEL QUERY

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Tue, 16 Oct 2007 06:58:36 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3603CB9C4B@CWYMSX04.Corp.Acxiom.net>

Deepak,

More than likely the optimizer determined that a parallel query with just 4 PX slaves is not more beneficial than a serial scan, whereas 16 slaves is more beneficial. I've never seen this before, but then again our databases are configured to maximize parallel performance, so any parameter related to parallelism is set to a larger than average value.

To validate what the optimizer is doing, set event 10053 for each query and review the optimizer's decisions.

Also, remember that hints are just that, a hint or suggestion, not a "force".

Dave
 

---

David C. Herring, DBA  |   A c x i o m  Delivery Center Organization 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of DBA Deepak Sent: Tuesday, October 16, 2007 1:50 AM
To: oracle-l
Subject: PARALLEL QUERY

Hi Experts,
 

Have a problem related to parallel query execution. I have a setup of Oracle 10g (10.2.0.3) on solaris 64 bit and on a 16 core CPU system.
 

When I execute parallel hint in the query the following happens.
 

Does not use PQ processes for the query:
 

select /*+ parallel(bt,4) */ from big_table bt;
 

Does use PQ processes for the query:
select /*+ parallel(bt,16) */ from big_table bt;

Please help me in understanding why this happens.
 

-- 
Regards,

Deepak
Oracle DBA 
*************************************************************************
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 Tue Oct 16 2007 - 06:58:36 CDT