Re: 10g RAC and defined services performance problem

From: Dan Norris <dannorris_at_dannorris.com>
Date: Thu, 11 Sep 2008 16:11:30 -0500
Message-ID: <48C98982.8050805@dannorris.com>

Jon,

First, I'd always create at least one service in addition to the default one. I never use the "standard" db-named service for anything other than maintenance purposes. I absolutely advocate your pattern of creating multiple services for managing your workload (and I think Oracle recommends the same). Creating services shouldn't have any negative effect on performance.

I have a few questions or observations regarding what you've said so far:
--"In previous tests..." What was the nature of the testing? Did that testing include the same stress level you expected to have in production? Were all copies (you said there are about 20) of the application tested simultaneously?
--Bigger is not always better with respect to SGA sizing. If you have lots of literal SQL, it may be faster to shrink the shared pool to speed up parse time since less time will be spent looking for matches in the shared pool (since you won't find any matches anyway). I learned this a long time ago and I think it still applies today, but I'm not positive. This may require you to use individual SGA sizing parameters instead of the sga_target if you're using that today. The AWR data should give you some idea of lots of parsing is an issue (or *the* issue) in your case.

It sounds like you haven't been able to dig very deeply yet, so I assume you're looking for some high-level ideas of things that might be areas to focus on. Hope this helps. Please let us know if you get some specific data (like wait events) that may provide more information on what's holding things up.

Dan

Crisler, Jon wrote:

Good question- in fact we did do that- each service runs the same application, but accesses different schemas; this way a particular schema would be located on one node.  In previous tests the cluster seemed to work quite well, but now that they are live we are getting high cpu utilization.   We have all the normal tricks in place for performance tuning- large SGA, fast disks, gige RAC interconnect with bonding etc.  Since I have not been told about the time periods that it tends to choke I have not been able to do much with AWR etc but I am trying to get that info.

 

---

From: Andrew Kerber [mailto:andrew.kerber@gmail.com]
Sent: Thursday, September 11, 2008 10:57 AM
To: Crisler, Jon
Cc: oracle-l; oracle-rac@freelists.org
Subject: Re: 10g RAC and defined services performance problem

 

I cant think of any reason why the services per-se could be the problem.  However, it is not that unusual for applications not to scale well across multiple nodes.  Did you do any analysis to try and partition the workload across the nodes so that each node would access a different subset of data?

On Thu, Sep 11, 2008 at 9:51 AM, Crisler, Jon <Jon.Crisler@usi.com> wrote:

We have a 3-node 10.2.0.3 RAC Server on Red Hat 4, and have defined about 20 additional services with preferred nodes set to 1 specific node, with available nodes set for the other 2 nodes.  Workload is divided evenly with about 7 services on each node.   Has anybody run into specific problems with performance when using a lot of services, as opposed to having the application just connect to the main db load-balanced service ?

 

This particular application has a reputation from the developers of not scaling well across RAC nodes, and we have recently been advised of a lot of performance problems that I am now starting to investigate, but I want to get some opinions on the wisdom of using many different services rather than just a few or one.

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

-- http://www.freelists.org/webpage/oracle-l Received on Thu Sep 11 2008 - 16:11:30 CDT