Re: RAC PARALLEL

  • From: "Tim Gorman" <tim@xxxxxxxxx>
  • To: "Sanjay Mishra" <smishra_97@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 Oct 2011 18:18:14 +0000

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: