1

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.

Improve this question
4
  • Execute "ipcs -m | grep oracle" and see how big the shared memory segment is. That's the best way of seeing how big it is from the Unix side of things
    – Philᵀᴹ
    Feb 11, 2012 at 17:51
  • Hi Phil, thanks for the comment. I ran the command but I got no result back. Does this mean I can't see the shared resources or do you need to be login as an admin to see a result on system and root processes?
    – Alexei Blue
    Feb 14, 2012 at 10:01
  • Do you have limits set on the various parts of the SGA (Shared Pool, Buffer Cache, Large Pool, etc.)?
    – Leigh Riffel
    Mar 12, 2012 at 17:16
  • Which exact Oracle version (4 digits) is that? What are the settings for MEMORY_TARGET, MEMORY_MAX_TARGET and SGA_TARGET?
    – user1822
    Aug 9, 2012 at 19:08

1 Answer 1

Reset to default
1

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..

Improve this answer
1
  • According to the guys who run our DB the current settings are: PRODUCTION: SGA_MAX_SIZE 6G, SGA_TARGET 6G, PGA_AGGREGATE_TARGET 4G; TEST: SGA_MAX_SIZE 10G, SGA_TARGET 9G, PGA_AGGREGATE_TARGET 4G. The memory supposed to be set to allow for multiple instances of oracle so I'm not sure if if this will be enough memory but still looking at the process information in the console the amount of memory set in production is correct but in test it doesn't appear that way. I'll keep bugging the DB guys to find out what's going on lol.
    – Alexei Blue
    Feb 14, 2012 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.