I have an Oracle set up on a remote server that's maintained by a third party. Recently it became corrupt and we replaced it with a new instance. Since then it's been running slower when performing some searches and even timing out. I've been told by the people that maintain the database that the memory allocation is set as follows:
SGA: 6GB
PGA: 4GB
However when I run top in Linux I can see that all except one of the processes only use ~2.5GB as indicated by the RES column.
I know that there are other instances of oracle running on the server however there's 32GB of physical memory in the system and so it should be able to handle it.
Is there any reason why processes wouldn't be using as much of memory as should be expected?
NB: When the system was brought back up the people that maintained it put half the physical memory (16GB) in the server box that should have been put in. This was rectified a month ago and another 16GB added but didn't fix the problem.
Best Answer
set SGA_max_size=30g (size is greater then sga_target value).. you must set SGA_target=16777216k or 16384m or 16g
Now instance will allocate up to 16g you can alter sga_target upto 30g with out shut db..