topic Re: Oracle user memory consumption in Operating System - HP-UX

Oracle user memory consumption

Jason Berendsen — Thu, 27 Dec 2001 13:08:35 GMT

We were told that each user logging into Oracle only consumes memory set aside for the SGA. Unfortunately, we beleived this and have now come to find it is incorrect. We are finding that each user that logs into the Oracle database is using about 3meg of memory outside of the SGA. Is there any rule of thumb as to what a user should consume?

Re: Oracle user memory consumption

Praveen Bezawada — Thu, 27 Dec 2001 13:24:37 GMT

Hi
Oracle documentation defines SGA as the 'a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, the data in the instance's SGA is "shared" among the user'
Apart from it each user process uses an area called PGA 'it is a memory region containing data and control information for a single process (server or background). Consequently, a PGA is sometimes called a "process global area."
A PGA's size is fixed and operating-system specific.
And HPUX I am not sure what this fixed value is.
Hope ths helps
...BPK...

Re: Oracle user memory consumption

Tom Geudens — Thu, 27 Dec 2001 13:41:57 GMT

Hi,
The amount of memory a databaseconnection consumes depends on the oracle version AND what the connection is doing. In my experience (and I'm looking at glance right now) this is not a static number. Also take into account that part of this use is "shared". That part is static. You will indeed find that most processes consume +/- 3Mb. Only +/- 1.5 Mb is private, the rest is shared. In "normal" online transactions (and I'm looking at a heavily used database right now) processes will not go above 10Mb. In batch transactions (or Datawarehouse queries) this can be a lot more. You would benefit from "dissecting" a couple of oracleprocesses with the "Memory Regions" menu in glance to learn more about this.

Hope this helps,
Tom Geudens

Re: Oracle user memory consumption

A. Clay Stephenson — Thu, 27 Dec 2001 17:38:10 GMT

Hi Jason,

I'm sure that you are aware that there is no real answer to this question except 'it depends'. You really violated Rule Number One - Never believe the vendor. The amount of memory that each client consumes depends very heavily upon the application. The good news is that much of the memory tends to be shared text. Whenever I am sizing systems, I always allow 5MB per client plus the estimated SGA plus the estimated size of the server executables. At 5MB per client, I have yet to be burned and my rule of thumb is always buy more memory than you think you will need.

Re: Oracle user memory consumption

Wodisch — Thu, 27 Dec 2001 23:33:09 GMT

Hello Jason,

*they* were perhaps talking about "shared servers", but you are using "dedicated servers" (as most of us do)?

Just my $0.02,
Wodisch

Re: Oracle user memory consumption

harry d brown jr — Fri, 28 Dec 2001 00:09:13 GMT

Jason,

If oracle told you that, then I agree with clay, and as I like to say: all salespeople are pond scum.

With resource pigs like oracle, I like to make sure I have more memory than I think I need (usually I calculate amount needed, then multiply by 1.5), the same goes for CPU's.

live free or die
harry