Re: RAC PARALLEL
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: "Sanjay Mishra" <smishra_97@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Oct 2011 19:51:03 +0000
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
-----Original Message-----
From: Sanjay Mishra [mailto:smishra_97@xxxxxxxxx]
Sent: Wednesday, October 12, 2011 12:42 PM
To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: Re: RAC PARALLEL
Thanks Tim. This is working fine in non-RAC but surely the hitter in the RAC
environment. So look like tuning in RAC setup is required. Also as I mentioned
earlier all of the topmost in Ordered by Read are using same PARALLEL Hints.
Is there any good practices for Parallel_xx parameter setting for RAC
environment like Parallel_instance_group or so
Sanjay
------------------------------------------------------------
From: Tim Gorman <tim@xxxxxxxxx>
To: Sanjay Mishra <smishra_97@xxxxxxxxx>; oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, October 12, 2011 2:18 PM
Subject: Re: RAC PARALLEL
Sanjay,
You've a lot of SQL tuning to do, my friend! :-)
Next step, go to the "SQL Ordered by Reads" section of the same AWR report and
start tuning the first couple of SQL statements on that list. Enjoy!
Thanks!
-Tim
-----Original Message-----
From: Sanjay Mishra [mailto:smishra_97@xxxxxxxxx]
Sent: Wednesday, October 12, 2011 12:14 PM
To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Subject: Re: RAC PARALLEL
Tim
Thanks for the detailed update. Here is top events which are not indicating
much on the gc events.
Top 5 Timed Foreground EventsEventWaitsTime(s)Avg wait (ms)% DB timeWait
Classdb file sequential read32,512,212200,101869.58User I/ODB CPU40,11211.30db
file scattered read2,123,18929,2891310.51User I/Odb file parallel
read161,0914,058252.01User I/OPX Nsq: PQ load info query
20,7023,6891891.23Other
Sanjay
------------------------------------------------------------
From: Tim Gorman <tim@xxxxxxxxx>
To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Wednesday, October 12, 2011 1:51 PM
Subject: Re: RAC PARALLEL
Sanjay,
RAC is an amplifier of waits on I/O and enqueues. Assuming that you've gone
"vanilla" in your 11g upgrade and not propagated forward any strange settings
in initialization parameters from your previous environment, then whereever you
might have seen waits on I/O (i.e. "db file sequential read", "db file
scattered read", etc) in your non-RAC environment, you'll now see those waits
along with corresponding RAC global cache (GCS) waits in addition. Whereever
you might have seen waits on enqueues (i.e. events beginning with "enq:") in
your non-RAC environment, you'll now see corresponding RAC global enqueue (GES)
waits in addition. These additional GCS and GES waits would be the "price of
RAC" from the standpoint of performance, and are to be expected. If this is the
situation you are seeing, then your response is the same in a RAC environment
as in a non-RAC environment - tune the SQL to reduce I/O, and
analyze/understand/minimize enqueue activity. As the waits on the basic I/O and
enqueue events decrease, so will the waits on the dependent RAC-related GCS and
GES events decrease.
The main point is that wait-event analysis will tell you whether you're
experiencing "normal" additional waits on GCS and GES piled on top of the
non-RAC waits you were accustomed to seeing in your non-RAC environment. If you
are seeing any other excessive waits on events, or any pattern to your waits
other than that described above, then please consider posting information from
an AWR report to the list (particularly the "Top Five Timed Events" section of
the AWR report), and we'll do our best to help you diagnose it with you.
Hope this helps...
Thanks!
-Tim
-----Original Message-----
From: Sanjay Mishra [mailto:smishra_97@xxxxxxxxx]
Sent: Wednesday, October 12, 2011 11:23 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RAC PARALLEL
HiWe have one environment on Sun Infrastructure where we are planning to move
to RAC on 11g. So move is from non-RAC to 4-Node 11g R2 RAC. Database is OLTP,
Partitioned Tables/Indexes and Apps is using heavily PARALLEL and
PARALLEL_INDEX hints.What is the point to consider for the setup. Saw that we
hit some big performance in Test setup and look like it may be due to Paralell
setup.any good sugestion or best practices on RAC
setupTIASanjay--//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
