RE: RAC PARALLEL

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 25 Oct 2011 13:51:13 -0400
Message-ID: <016e01cc933e$b0b43fc0$121cbf40$_at_rsiz.com>



You're doing deletes to get rid of old partitions?  

Please review Tim Gorman's "scaling to infinity" papers and see whether any form of partition exchange can meet your needs. If you're keeping some, then copying those out and swapping back in just what you need is often useful.  

(It probably can.)  

From: Sanjay Mishra [mailto:smishra_97_at_yahoo.com] Sent: Tuesday, October 25, 2011 10:34 AM To: riyaj.shamsudeen_at_gmail.com
Cc: Andrew Kerber; mwf_at_rsiz.com; tim_at_evdbt.com; oracle-l_at_freelists.org Subject: Re: RAC PARALLEL  

Update  

Thanks for Riyaj Help. After going thru the 10053 trace it was found that some of the indexes are set in new environment with Degree 8 and so it is affecting the Optimizer to use Parallel Query  

One problem still there as currently the Parallel degree policy is set to AUTO and all Tables/Indexes are no parallel. All Tables are locally partition on day basis where deletion are done every three hourly to delete some data older than 7 days and status which are both Indexed columns.Sometime the delete goes in 1-5 and other times even 20-30 min. Sometime I can see the Degree is 1 or 2 and less cost in explain but other time it is 20+ and high cost. Number of records got deleted are almost close to each other like 5K - 10K.  

This behaviour is not with one statement but several such statement against the partitoned. GC wait or Interconnect is not an issue as it not showing any bottleneck  

Any help or thoughts are greatly appreciated. Environment gain is 4 Node RAC with 11g R2 . CPU is not bottleneck as it is more than 70 idle. Parallel Min server is 10 and Parallel Max server is 256  

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-l
Received on Tue Oct 25 2011 - 12:51:13 CDT

Original text of this message