Re: RAC PARALLEL

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@xxxxxxxx>
To: tim@xxxxxxxxx; 'Sanjay Mishra' <smishra_97@xxxxxxxxx>; 
oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Wednesday, October 12, 2011 3:51 PM
To: Sanjay Mishra; oracle-l@xxxxxxxxxxxxx
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

Other related posts: