Re: RAC PARALLEL

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Fri, 14 Oct 2011 05:43:36 -0700 (PDT)
Message-ID: <1318596216.16800.YahooMailNeo_at_web161206.mail.bf1.yahoo.com>

Riyaj
The issue at my case is that application is working fine in production on old hardware with 10g. There are some perfoemance hit due to very old hardware. UAT is setup with 11.2.0.2 on RAC and database is copied. These server are several time better in architecture. Application is experiencing big performance issue in it. Based on Tim comments, when I checked all of the topmostSQL Ordered by READare using PARALLEL hint. Using Hints from SqlId in AWR, it was found that all are taking almost an 1hour for each. Execution plan are similar in both environment. Also Interconnect traffic is normal as per the AWR.

---

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: Thursday, October 13, 2011 10:22 AM Subject: Re: RAC PARALLEL

Sanjay

  Sorry to point out, still, you haven't provided much information. What exactly are you trying to achieve? What is the problem that you are trying to resolve? I see that Tim is also asking the same question. 
  I talked about PX execution in RAC, in OOW 2011. My Presentation slides in http://orainternals.files.wordpress.com/2011/09/px-execution-in-rac.pdf . (shameful plug?) I am not sure, it this presentation is relevant to you or not, since you haven't explained your goals. 
   BTW, setting parallel_force_local = true is not applicable to every one, neither limiting parallelism to one node. It all depends upon the execution plan, node resources, and interconnect bandwidth etc. Also, Parallel queueing feature interacts negatively with parallel_force_local parameter as queueing is at the database level, but parallel_force_local will limit the slaves to one node. You should use services instead if you would like intra-instance parallelism.

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 Thu, Oct 13, 2011 at 8:09 AM, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:

Thanks Andrew. I was not aware of this and found late that 11g R2 has got the following new parameters.
>1,PARALLEL_DEGREE_POLICY:
>2,PARALLEL_MIN_TIME_THRESHOLD:
>3,PARALLEL_DEGREE_LIMIT:
>4,PARALLEL_FORCE_LOCAL:
>
>This is what I found yesterday when I mentioned in the last update that Old Instrance_groups and PARALLEL,.. are deprecated in 11g R2 and even still there for backward compatability. This is what I was trying to set today to restrict the Parallel to Local Node.
>
>
>Regards
>Sanjay
>
>
>
>________________________________
>From: Andrew Kerber <andrew.kerber_at_gmail.com>
>To: mwf_at_rsiz.com
>Cc: tim_at_evdbt.com; Sanjay Mishra <smishra_97_at_yahoo.com>; oracle-l_at_freelists.org
>Sent: Thursday, October 13, 2011 8:41 AM
>Subject: Re: RAC PARALLEL
>
>
>
>I'm surprised no one mentioned this.  If this is 11g there is a
>parameter called parallel_force_local.  Make sure this is set to true. 
>If not, you can end up running parallel operations on remote nodes,
>which will both adversely affect operations on other nodes, but will
>also cause massive gc waits when data is returned unless you have a very high speed interconnect (ie, at least 10 gigabit).  On 10g this is done by setting the parallel groups and such, I would have to look it up to
>find the exact details. 
>
>As others have mentioned, it is unlikely that parallel is a good
>idea on OLTP, but but some of the other parameters you need to look at
>are parallel_min_servers, parallel_max_servers.  I have found that
>setting the parallel_min_servers saves a lot of time in the startup of
>parallel queries and parallel_max_servers will help keep the load down
>at high usage times.
>
>(note: resent because I overquoted a few minutes ago).
>
>
>--
>Andrew W. Kerber
>
>'If at first you dont succeed, dont take up skydiving.'
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Fri Oct 14 2011 - 07:43:36 CDT