Memory

[ramya_ram]

Hi
How much of memory will be allocated for each oracle client?
How do I determine what should be my Server memory based on the number of clients?

[padmam]

Hi, 14th May 2001 14:09 hrs *******

We can allow up to 250 bytes in the shared pool per user per open cursor.This cab be tested during the peak times with the following query

>select sum(250*users_opening) from v$sqlarea;

In a test environments you can measure the it by selecting the number of open cursors for a test user.You can then multiply the resulting value by the total number of users.

>select 250*value bytes_per_user from v$sesstat s,V$statname n
where s.statistic#=n.statistic#
and n.name='open cursors current'
and s.sid= 10;

Your application should have library cache as large as the sum of the numbers above and a additional allowance for dynamic sql.

====Sizing UGA============

Here once gain it depends also upon the MTS or DS.

In MTS user session and cursor state information is stored in the shared pool instead of private user memory.Sort areas and user sql statements are included in the session information.This is because the shared servers work on a per statement basis.

The total memory req for MTS the shared_pool_size should be increased and proportinally the PGA will be less.

UGA space used by the test connection
=========================

>select sum(value)|| 'bytes' "Total session memory"
from v$mystat,v$statname
where name='session uga memory'
and v$mystat.statistic#=v$statname.statistic#

UGA space used by all MTS users
=====================

>select sum(value)|| 'bytes' "Total session memory"
from v$sesstat,v$statname
where name='session uga memory'
and v$sesstat.statistic#=v$statname.statistic#

Maximum UGA space used by all MTS users
============================

>select sum(value)|| 'bytes' "Total session memory"
from v$sesstat,v$statname
where name='session uga memory max'
and v$sesstat.statistic#=v$statname.statistic#

For all MTS connections you need to compute the amount of required space for all shared server users to put thier session memory in the shared pool.

Cheers

Padmam.