Re: RAC PARALLEL
Date: Mon, 17 Oct 2011 13:04:24 -0700 (PDT)
Message-ID: <1318881864.54222.YahooMailNeo_at_web121015.mail.ne1.yahoo.com>
Hi Sanjay,
I have some concerns regarding your approach to the issue in the ongoing email trail. It appears that you are searching for a "silver bullet" to your problem via parameter changes with the new environment and I don't think you are going to find one and I don't think it would be wise, even if you did find an acceptable parameter change. I am of the belief that it is crucial for DBA's to know how to locate performance issues and address them in their databases. May I suggest that the advice of others be taken and run awr reports, specific ones are available for RAC, (This post from my own blog will tell you a bit about running one: http://dbakevlar.com/2011/04/awr-for-rac/ ) and take a bit of time to understand how to read the reports- it will be well worth your and your database environment's time...
Kellyn Pot'Vin
Sr. Database Administrator and Developer
DBAKevlar.com
From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com> To: Sanjay Mishra <smishra_97_at_yahoo.com> Cc: Andrew Kerber <andrew.kerber_at_gmail.com>; "mwf_at_rsiz.com" <mwf_at_rsiz.com>; "tim_at_evdbt.com" <tim_at_evdbt.com>; "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Monday, October 17, 2011 12:48 PM
Subject: Re: RAC PARALLEL
Hello Sanjay
You say, several queries are using parallelism? What is your
parallel_degree_policy set to ? If it is set to auto, then
parallel_min_time_threshold is too low for your environment. If the
optimizer determines that execution time of a query exceeds 10 seconds, then
it can reparse the query with parallel hints. That might trigger parallel
queries in your environment inadvertently.
Specific examples will be helpful to understand the issue better.
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<http://tinyurl.com/book-expert-oracle-practices/>,
Pro Oracle SQL, Expert PL/SQL
Practices<http://tinyurl.com/book-expert-plsql-practices>
On Mon, Oct 17, 2011 at 1:35 PM, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
> 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_at_gmail.com>
> *To:* smishra_97_at_yahoo.com
> *Cc:* Andrew Kerber <andrew.kerber_at_gmail.com>; "mwf_at_rsiz.com" <
> mwf_at_rsiz.com>; "tim_at_evdbt.com" <tim_at_evdbt.com>; "oracle-l_at_freelists.org" <
> oracle-l_at_freelists.org>
> *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<http://tinyurl.com/book-expert-oracle-practices/>,
> Pro Oracle SQL, Expert PL/SQL Practices<http://tinyurl.com/book-expert-plsql-practices>
>
>
>
> On Fri, Oct 14, 2011 at 10:46 AM, Sanjay Mishra <smishra_97_at_yahoo.com>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
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 17 2011 - 15:04:24 CDT