Oracle 9i SGA Sizing on 32 bit machine

[navisd]

I am in a way to tune oracle 9i Instance on 32 bit windows, system have 4 GB memory, Since i dont want to enable VLM so i want to create SGA upto 1.7 GB. Now the question i have is that how i will manage my instance within 1.7 GB. I applied following settings

SGA_MAX_SIZE = 1552.57 MB
DB_CACHE_SIZE = 1200 MB
SHARED_POOL_SIZE = 256 MB
LARGE_POOL_SIZE = 56 MB
JAVA_POOL_SIZE = 8 MB
LOG_BUFFER = 8 MB
PGA_AGGREGATE_TARGET = 300 MB

when i tried to give 600MB to pga_aggregate_target and restart the instance it crashed, not enough memory available on system to accomodate pga_aggregate_target size.
With above setting i successfully restart the server again, cache_hit_ratio is 60%.

Now my question is that how i will size the oracle instance within 1.7 GB memory. This 1.7 GB limit is with pga_aggregate_target i mean
ORACLE_INSTANCE + PGA_AGGREGATE_TARGET < 1.7G ?

if yes then how i will come to know that how much memory i will give to
shared_pool
db_cache_size
pga_aggregate
sga_max_size
Please suggest me for 1.7GB SGA.

[davey23uk]

they are all completely application dependant, how much memory you need for your shared pool depends on how much sql you use. buffer cache - how much data you are dealing with.

So there is no answer to your question, you need to know how your aplication behaves

[yxez]

yeah i agree...it doesnt mean that 1.7Gb memory will make ur system
attain peak performance....but maybe it will suffice at 500MB...then
gradually adjust it from there as u tune it. Tuning is sometimes
trial n error.

I hope this helps

[navisd]

I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.

[yxez]

hmnn...maybe u have to increase gradually by 10Mb its effect to the
whole sum might be great.

[davey23uk]

I need to adjust SGA/PGA to keep in my mind that i have limit of 1.7GB on windows, regarding performance i dont care right now about perfromance. I just want to know that how i will distribute the memory with in 1.7GB.

if you dont care about performance, why do you need to adjust it at all?

[navisd]

Performance is the next step first thing i need to utilize memory upto 1.7 G as system have 4 GB memory.

[davey23uk]

why do you 'need' to, if you arent changing it for performace why are you changing at all

[navisd]

Well performance is one concern but i again say i want to utilize maximum memory on windows box as there is 4 GB ram and most of it is free.Right now OS utilizing some memory and oracle utilizing only 1 GB of ram, 2-2.5GB is free most of the time. So i decided to utilize the free memory. This is the reason behind all this. I know how to enable VLM. As my favourite OS is Linux, I have configured 12GB instance on 32 bit machine with 16GB RAM. I successfully hit more than 1000 active users on that server and it didn't gave any error. So here my goal is to create 1.7GB oracle instance on 32 bit windows plateform which will work efficiently. Please suggest me how i will size my sga with in 1.7GB Limit and also how much memory i will give to PGA. Remember I need efficient, effective, optimized settings with 1.7GB restriction.

[davey23uk]

you are coming at it from totally the wrong direction, you need to work out what you need, how your application works and how much memory you need.

You cant just say give Xmb to shared pool, Ymb to buffer cache, it just doesnt work like that.

It makes no sense to use memory just cos you have it - why not use it for something that actually needs it