Re: RAC PARALLEL

  • From: Sanjay Mishra <smishra_97@xxxxxxxxx>
  • To: "Mark W. Farnham" <mwf@xxxxxxxx>, "riyaj.shamsudeen@xxxxxxxxx" <riyaj.shamsudeen@xxxxxxxxx>
  • Date: Tue, 25 Oct 2011 11:12:46 -0700 (PDT)

Mark
 
No we are not deleting/dropping Partition but deleting some old records which 
are processed and more than 7 days old and some status. Some partiton are daily 
based and some are weekly and Monthly. Delete are only as per application logic
 
 
Sanjay
________________________________
From: Mark W. Farnham <mwf@xxxxxxxx>
To: 'Sanjay Mishra' <smishra_97@xxxxxxxxx>; riyaj.shamsudeen@xxxxxxxxx
Cc: 'Andrew Kerber' <andrew.kerber@xxxxxxxxx>; tim@xxxxxxxxx; 
oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, October 25, 2011 1:51 PM
Subject: RE: RAC PARALLEL


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@xxxxxxxxx] 
Sent: Tuesday, October 25, 2011 10:34 AM
To: riyaj.shamsudeen@xxxxxxxxx
Cc: Andrew Kerber; mwf@xxxxxxxx; tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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<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@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

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2011