Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 11 '12 at 15:50

This question came from our site for professional and enthusiast programmers.

    
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 '12 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 '12 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 '12 at 17:16
    
Which exact Oracle version (4 digits) is that? What are the settings for MEMORY_TARGET, MEMORY_MAX_TARGET and SGA_TARGET? –  a_horse_with_no_name Aug 9 '12 at 19:08
add comment

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

share|improve this answer
    
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 '12 at 10:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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