Re: Oracle question from a Sys. admin, re: Solaris performance

Comments embedded

On 3 Mar 2005 12:55:11 -0800, tonij67_at_hotmail.com wrote:

>Hi all,
>
>
>Let me preface this by stating that I am not a DBA, so please take what
>I say with that in mind.
>
>I support several Sun systems running various versions of Oracle; 8,
>and varients of 9. We are in the process of getting them all to 9.2
>but there are still some hangers-on at 8.
>
>I have been interested in learning as much as I can about Oracle as it
>may help in supporting the hardware and OS. I have been scouring the
>net for information about performance and found asktom.oracle.com ...I
>found an article that jumped out at me, about running 4 instances of
>Oracle on a system and getting poor performance. This interested me
>because we have upwards of *50* instances on a single machine with more
>on the way. Thats not a typo, 50 as in fifty.
>
>The article I looked at basically said that supporting 4 instances
>would be a "nightmare" because you never know which instance is taking
>resources. I have a couple questions about this:
>
>- is this true? Is there really know way to determine which instance
>is using, say 99% CPU?
>

That is definitely not true. First of all you could use various utilities coming with and downloadable for Solaris like sar, top and ps.

Secondly you could get the amount of cpu being consumed from the v$sysstat dictionary view, that is if the init.ora parameter timed_statistics for all your instances set to true. The results would however be 'awk' ward to be combined.

But then please tell me, how come, when you are in this situation, you don't have any users of the other 49 instances yelling at you and screaming for your scalp? Or have they been trained to treat the lousy performance as 'fact of life'?

>- are we insane for having this many instances on a system? I think
>its the result of sale and marketing gone wild...
>

Insane is an understatement. The problem here an Oracle instance has a minimum amount of resources to operate at all. Say you need some 300M SGA at the least. And that times 50. You are now using 1.5 G of valuable RAM and you don't do yet anything, ie you have no users connected to your instance (each user session results in an Oracle process being forked on the server, dealing with that client only). So either by far the majority of those 50 instances doesn't do anything at all (and you are wasting resources, because by becoming them you would save on RAM), or the server suffocates because you force it to page and swap heavily (to be identified by sar or vmstat or whatever Unix tool), and end-users are getting very frustrated. I won't dwell on the fact there are probably logical interrelations between the various databases.

The generic guideline as far Oracle is concerned the total amount of memory allotted to SGA (System Global Area, in which Oracle cache much to avoid physical I/O) for all your instances shouldn't be set higher than 1/3rd of physical RAM, or your server will start paging and swapping, and this is potentially worse. My bet is you are way over that 33 percent.

Hth

--
Sybrand Bakker, Senior Oracle DBA