Oracle Memory Allocation

[Acxiom Unix Team]

Hi,

I am wondering if anyone knows of any documents/whitepapers that have some advisory on the recommended amount of memory you should have on a server to run Oracle databases.

We have a clients server that has 13 Oracle databases on (mixture of Oracle 8 & 9)but they decided to allocate only 8Gb to the partition.

So i have been charged in trying to find out if any recommendations exist on how much memory you should have to run an oracle database. If anyone knows of any such documents or has any advise on this i would be grateful.

Many Thanks

[Jean-Luc Oudart]

Hi

you did not give much information. What is the server, the OS , OS version.

As for Oracle you will have minimum requirement for memory (cf. installation guide) but this will probably not suffice.

If these databases already exist you have a baseline with a set of parameters and also you can collect information regarding current performance (with STATSPACK http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm ), hence a baseline for current oracle databases.

8GB looks like a bit tight for 13 databases unless they are quite small or little activity.

Regards
Jean-Luc

[Hein van den Heuvel]

Are those DB's all new, or are they currently active elsewhere? Can you not use the current size as starting point?

The starting point should really be the DBA working with the applications team to come up with a number of expected connections and a reasonable SGA/PGA estimate for each database.

Add it all up multiplying the connections with some number of megabytes between 5 MB and 15 MB or os based on similar experiences. Add an other Gigabyte for the OS and file system buffers and there's you desired memory size.

As per Jean-Luc, once you have the databases active for a while, tools like statspack can give an indication how much you can reduce the SGA without too much extra IO or how much more you'd like to have to reduce the IO.

Now if that 8GB, that some pulled from a place where the sun don't shine, is what you have to play with, then you have tell the DBA(s?) to fight it out but that they only have 1/2 GB on average to play with.

Hope this helps some,
Hein

[Eric Antunes · Accepted solution]

Hi Andrew,

Yes, there is a measure. Run this query after the database is open for a while:

select to_number(value) shared_pool_size,
sum_obj_size,
sum_sql_size,
sum_user_size,
(sum_obj_size + sum_sql_size+sum_user_size)* 1.25 min_shared_pool_size
from (select sum(sharable_mem) sum_obj_size
from v$db_object_cache where type <> 'CURSOR'),
(select sum(sharable_mem) sum_sql_size
from v$sqlarea),
(select sum(250 * users_opening) sum_user_size
from v$sqlarea), v$parameter
where name = 'shared_pool_size';

min_shared_pool_size should be the minimum size of your shared_pool_size.

Best Regards,

Eric Antunes

[Acxiom Unix Team]

Thanks for the ideas. I am going to feed this all back to our DBA team and leave it with them to investigate the link sent by Jean-Luc and the suggestions from Hein & Eric.

Thanks again.