Memory allocation

[mooks]

Hi

We are using Redhat AS3 with 8GB RAM. How do I allocate the memory for Oracle? Will I have a shortage of memory with the following allocation?

Shared pool : 728 MB
Buffer cache : -1911 MB
Java pool : 32 MB
Large pool : 539 MB
PGA : 1941 MB
Oracle proccess size : 40 MB

Total memory for Oracle : 1369

Sort area size : 1,048,576 bytes

Please advice, thanks!

[davey23uk]

That doesnt add up, if you have 1911Mb buffer cache then you have more then 1369M total

Also your shared pool is wayyyy oversized - just a waste of memory

If you use pga_aggregate_target then sort area size is obselete and your large pool is umm rather large

And whats this oracle process size you mention?

[mooks]

Thanks for the reply. That's the standard parameters that are being allocated by Oracle upon creation of the database and its funny why the buffer cache is negative!

Let assume that I want to allocate a total of 3GB to Oracle database, will this be sufficent? And how much do I allocate for each parameters?

I just moved to Oracle 9i and thanks for mentioning on the pga_aggregate_target! Any recommendations on allocating of the pga_aggregate_target?

[davey23uk]

well pga_aggregate_target is the total amount of memory available to processes, so you need to know how many processes will be in your database and how much sorting they will do, it is made up of all the *_area_size parameters, so add up what you think you will need, then throw a bit more in for good luck.

Missed that negavtive sign, should be postive :-)

As for how big all the parameters should be,m only you can answer that

shared pool is dependant on how much pl/sql you have, are you gonns use rman? if so you need a lrage pool, will you be running from java from within the database, if so you will need java pool set.

Only you can say how much you will need as only you know your app/ system

[mooks]

My current database is about 2 GB and there will be around 100-200 processes. Will not be using RMAN currently.

Thanks!

[waitecj]

All the memory you have available, minus what you have given to the SGA.

[mooks]

Hi waitecj

Are you referring to the pga_aggregate_target?

If my SGA only needs 2-3GB, then the remaining 5-6 GB should be allocated? Wouldn't it cause the OS processes to be slowed down??

[waitecj]

Hi,

Any memory being used by OS processes, I wouldn't consider to be "free", so no, I did mean all memory minus SGA and OS etc. Sorry, should have been clearer.

But then if your server is a dedicated to your DB then I would allocate a large portion of whatever you have available after your SGA and OS processes have taken their share. Its a great new to 9i feature that allows you to allocate PGA without setting (potentially inefficiently) the AREA_SIZE params.

[mooks]

Thanks all!