Re: RAC PARALLEL
- From: Tim Gorman <tim@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 17 Oct 2011 08:57:23 -0600
Specifically, I recall seeing "db file parallel read" in the "Top Five
Timed Events", which indicates INDEX FULL SCAN, which is rarely part of
an optimal execution plan. If you're forcing parallel index scans, then
it is possible that you're forcing this operation in parallel.
I usually regard such execution plans as action items for SQL tuning,
falling in to the "low-hanging fruit" category (i.e. easy to fix, huge
resource usage decrease, huge performance increase). Just my US$0.02...
On 10/17/2011 8:37 AM, Riyaj Shamsudeen wrote:
> 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:
