Re: RAC PARALLEL
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- Date: Mon, 17 Oct 2011 11:35:57 -0700 (PDT)
Riyaj
One important thing found is that several simple queries are using PX.... in
Explain when moved to 11g RAC and taking much more time than non-rac 10g.In 10g
it is simple NL and Index scan but in 11g has much more in explain plan using
the same Indexes. Also Cost comes out in two digit in 10g which is 5 digit in
11g.
Sanjay
________________________________
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
To: smishra_97@xxxxxxxxx
Cc: Andrew Kerber <andrew.kerber@xxxxxxxxx>; "mwf@xxxxxxxx" <mwf@xxxxxxxx>;
"tim@xxxxxxxxx" <tim@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Sent: Monday, October 17, 2011 10:37 AM
Subject: Re: RAC PARALLEL
Sanjay
There are few observations:
1. Reviewing top 5 wait events, notice that there is no direct path read
waits. If you have parallel queries performing full table scans, then direct
path read would show up in the top 5 wait events.
So, your application might be using a special case of PQ where the
slaves perform nested loops join (yes, this is a possible execution plan). If
that is the case, then slaves will read the blocks in to buffer cache and
access them. This type of PQ execution stats are markedly different from the
traditional full table scan reading the blocks in to their PGA (aka direct
reads). Also, It goes without saying that, this type of execution plans will
induce more cache fusion traffic for the cache fusion clients (as against PQ
client), leading to the fact that keeping them in the same instance will
improve performance.
On the other hand, I don't know, why parallel hints are added to these
type of queries. Can you review the execution plans for these queries and
determine if they have much NL join? use dbms_xplan.display_cursor please.
Also, what are the stats indicate in dba_hist_ic_client_stats during the
problem time frame?
2. Further, setting parallel_force_local has side effects and bugs. Notably
bug "Bug 9671271 - All active instances used in calculation of dop when
parallel_force_local=true / High version count on PX_MISMATCH [ID 9671271.8]".
I had an issue with client database throwing spurious ORA-4031 errors, above
bug as a final root cause.You should services to control slave allocation.
HTH
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC
and EBS11i
Blog: http://orainternals.wordpress.com
OakTable member http://www.oaktable.com
Co-author of the books: Expert Oracle Practices, Pro Oracle SQL, Expert PL/SQL
Practices
On Fri, Oct 14, 2011 at 10:46 AM, Sanjay Mishra <smishra_97@xxxxxxxxx> wrote:
Changing parallel_force_local to True has brought back the SQL to same and even
better performance level. Thanks to all for the help and suggestions. Will be
doing more regression testing to make sure if this is the only issue.
>
>
>Sanjay
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
