Re: RAC PARALLEL
Date: Thu, 13 Oct 2011 06:04:17 -0700 (PDT)
Message-ID: <1318511057.41804.YahooMailNeo_at_web161206.mail.bf1.yahoo.com>
Thanks Mark for very detailed update. I am also trying to put parameters to restrict several queries using Parallel 16 or so for Table and Indexes as hints to one node to make it equivalent to what it was as single Node. Ofcourse it may still have some extra burden to get some blocks from other nodes in RAC and so also monitoring interconnect traffic. Sanjay
From: Mark W. Farnham <mwf_at_rsiz.com>
To: tim_at_evdbt.com; 'Sanjay Mishra' <smishra_97_at_yahoo.com>; oracle-l_at_freelists.org Sent: Thursday, October 13, 2011 7:18 AM Subject: RE: RAC PARALLEL
I haven't seen anything particularly wrong in this thread and Tim's paragraphs below are things of beauty, but one thing missing I consider an essential practice if you are mixing parallelism with interactive usage is a way to prevent even short term resource starvation.
Now you didn't exactly say you had interactive usage, but most systems considered OLTP have some interactive usage. Parallelism by design is tilted toward using as much of a node or an entire grid as it productively can to minimize the response time of large compute problems. Oracle has done a good job of allowing you to utilize the full power of a compute complex to solve a single problem very quickly.
When you mix parallelism with interactive usage it is up to you to maintain sufficient resource (CPU, memory, network bandwidth, and i/o capacity is the usual list of example resources) headroom to still meet your service level requirements for interactive transactions. That means not only do you need to limit degrees of parallelism to less than the total CPU (core vs. thread is too long to consider here) available, but also the aggregate load of all the non-interactive and interactive queries running simultaneously.
Consider this: If you're pegging any resource to 100% (which may just be getting your money's worth on the machine if you have zero interactive load) and an interactive query that needs that resource arrives, you immediately have to add at a minimum the time until some of that resource is available to the usual response time of that query.
This can create highly variable response times, even of efficient and light weight interactive queries. If the resource which is most starved varies, this can lead not only to variable response times, but also to variation in the wait profile of any individual trace of the same interactive query.
This in turn can create uncertainty of whether the variation in wait profile is due to varying query parameters (bind variables, for example) or lack of resource and creates a situation where you must monitor whole system resource availability simultaneously with individual session traces to make sense of them.
Rotating resource starvation is one of the most frustrating root causes of performance degradation to diagnose and it can be difficult even with proper instrumentation in place.
If there are times of day when you know no important interactive queries take place, then it may be possible to use work shifts so that your degrees of parallelism vary to use the whole machine when no interactivity is present (or at least expected.) Users performing interactive queries in any such "batch" windows will need to be educated to have different response time expectations than when working in "prime time." Executives demanding quick results to complex decision support queries during prime time will need to understand they may be causing frustrating response times to all the interactive users and weigh that appropriately.
Another tactic especially for RAC is to limit parallelism to within a single node for any particular query and direct services for interactive queries to nodes where parallelism is 1. This may tend to make application affinity difficult.
These complications are avoided if you stick with Tim's "no" below and you should face these complications only if you really need parallelism to achieve your service level requirements. That is a decent starting point.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Tim Gorman
Sent: Wednesday, October 12, 2011 3:51 PM
To: Sanjay Mishra; oracle-l_at_freelists.org
Subject: Re: RAC PARALLEL
Sanjay,
You haven't posted any information about either environment except to
describe them in the most general terms. Responding in general terms,
widespread use of parallelism in an OLTP environment sounds inappropriate,
so in general terms it sounds ... inappropriate. So, I'm in total agreement
with a prior responder on this thread who commented that high use of
parallelism is not usually associated with OLTP workload. If you're setting
high parallel DEGREE attributes on tables and indexes, I wouldn't be
surprised to see lots of inefficiently high I/O-intensive operations using
parallelism, in place of far-more-efficient non-parallel indexed-based
plans. Parallelism is not the answer for everything, not by a long shot.
If the RAC environment is consuming far more I/O than the non-RAC, then it is almost certainly due to changes in execution plans. You should be able to verify that if you retained your AWR data from the non-RAC environment. Once you confirm specific examples of execution plan change, then you can focus on those specific examples to chase down any combination of the dozens of possible causes of changes in execution plan. Let's just say that, if you didn't take explicit steps to *prevent* changes in execution plan, then with a possible change in platform, a possible change in OS version, a possible change in RDBMS version, as well as the switch from non-RAC to RAC, you've got a lot of variables to wade through.
In response to your question about "best practices" with parallelism in RAC when the application is characterized as OLTP, I'd say that the "best practice" is "don't". You might consider seeing what happens when you disable parallelism, perhaps? Just an idea, once you identify some specific examples...
Just my $0.02...
Thanks!
-Tim
<snip>
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 13 2011 - 08:04:17 CDT