RE: 2-node RAC faster than 6-node!

I've been reading along and there have been several good ideas kicked around. However as we all know Oracle is a wonderful and complex beast. I've found the best approach to this type of problem solving is to rely on Oracle's instrumentation of the database. ie. the Wait Events. I'm a little surprised that there was only brief mention of running a AWR report (from Alex I believe). Just about everything you need to diagnose this problem can be found there. You should use a consistent work load, run the report for a narrow period of time (perhaps 15-30 minutes) and run one for each configuration (1 node, 2 nodes, 3 nodes, ...). You may find that running 10046 session traces will also be helpful. Remember you are looking for your top waits. Where is the database spending most of its time.  

Take a look at your top 5 wait events and do a little online research. Other areas of interest would be the RAC Statistics -- Avg global cache cr block receive time and Avg global cache current block receive time in particular. Also take a quick look at your IO Stats and Top SQL Statements. Focus on the statements sorted by the type of wait Oracle spends most of its time on. For example if CPU show on the top of your top 5 wait events then look at SQL sorted by CPU.  

You will find alot of good information out on the Web regarding thresholds and what to look for. For example what the normal range and upper bounds on the interconnect statistics.  

Hope this helps.  

    -Randy

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Alex Gorbachev Sent: Thursday, April 03, 2008 10:49 PM
To: Oracle-L Freelists
Subject: Re: 2-node RAC faster than 6-node!

I'm going to drag this into slightly different direction (yeah right - what else can you expect from me?;)

This thread is full of assumptions leading to interesting guesses. Pretty much all of them have their chance to happen and many more exist. Though, there were few suggestions to proceed with rather standard performance troubleshooting. However, OP expressed his interest in silver bullets - "I was just trying to see if anyone can help pinpoint us on areas to focus on as possible culprits". Everything is possible and many things are arguably probable.

This is not to discourage the answers and ideas in the cases that don't really have a clear investigation paths and methods to gather more information. But in this case there is only vague description of a complex system and not a single performance metric collections ("improved significantly" and "in some cases 20X faster" don't count). Any RAC system is quite complex (and 6 nodes cluster is not the least complex) and tuning by silver bullets would traditionally be longer/more expensive/complex/prone to errors. There are much better investigation paths to take.

My rant is over... please continue. :-)

Alex

On 3-Apr-08, at 7:44 PM, 조동욱 wrote:

I doubt if OP has a mal-configured network setting. It's possible that 6-node RAC performs poorer than 2-node RAC. But 20X time? No way.

If the application access pattern and workload are same, the only culprits are poor interconnect or storage. I recommend to monitor OS level network statistics, configuration and plus, Oracle cluster wait events.

Dion Cho

--
http://www.freelists.org/webpage/oracle-l