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.

Help in sizing oracle 10g database sga
We've an Oracle 10g r2 database, size = 4.5TB
ASM is used to manage the storage. It uses "External Redundancy" option on "Raw Device" (i.e. no os file system caching).
Server Configuration is Intel Xeon 4CPU (8core per cpu) i.e total 32 cores; Total Internal Memory is 128GB. Operating System is Solaris 10 update 9.

The SGA size is 80GB, if we increase the SGA to 84GB~85GB the cpu utilization shoots upto 99%~100%. With 80GB SGA the cpu utilization is 20%~50% depending upon the load.

What is the relation between cpu cores and sga size? I used 4GB memory per core, is this a correct estimate for the Intel Xeon platform
Why does the cpu util shoot to 100% with an increase in SGA?
Is there a rule of thumb for sizing the oracle sga for oltp environment?

vmstat output:
r   b   w   swap          free         sr   s0 s1 s2 s3 us
0 26 27 128146676 6448872 115 0 3 0 94 19
1 74 31 123050360 4031544 0 0 2 0 56 16
0 83 31 122930884 3921080 0 0 0 0 31 10
1 75 31 122934416 3921480 0 0 0 0 61 13
0 78 31 122787016 3802200 0 0 0 0 104 25
0 84 31 122595368 3626344 0 0 0 0 110 22
0 91 31 122717320 3759480 0 0 0 0 108 25
0 88 31 122831348 3889744 0 0 6 0 111 27
0 79 31 122581408 3647472 0 0 0 0 101 44
1 84 31 122306816 3388964 0 0 0 0 71 27

The "sr" column reports zero for most of the entries (but the first where it shows a high value 115)
Moreover the vmstat shows an unusually high blocked and swapped out processes.

share|improve this question

migrated from serverfault.com Aug 25 '11 at 18:44

This question came from our site for system and network administrators.

    
I merged your accounts 'Jack' thought you'd like that. –  Chopper3 Aug 25 '11 at 18:39
    
What is the other 48GB allocated to? Are you reducing that allocation before increasing the SGA size? Note that high CPU can be caused by relieving an i/o bottleneck - do you have any other metrics indicating that this has slowed rosponse down? –  Jack Douglas Aug 25 '11 at 19:58
    
The remaining 48GB is allocated as follows: PGA 12GB remaining 36GB for OS. Pardon me, but i did not understand what you meant by "other metrics indicating response has slowed down". –  Jack Aug 25 '11 at 20:09
    
What other reason than the high CPU utilization measurement do you have for being worried - are queries/response times measurably slower for your users? –  Jack Douglas Aug 25 '11 at 20:45
    
Our major concern is, the ASM background process could not get cpu which resulted in a db crash. Another concern is the drastically reduced application performance. Both these are a direct result of very high cpu util –  Jack Aug 25 '11 at 22:01

2 Answers 2

This question is more complicated than how SGA relates to cpu utilization. Many other factors can cause a high CPU usage even if there is adequate SGA. Start looking at the other Oracle diagnostics for the usage of the shared pool, large pool and buffer.
Even more important you need to investigate the application front end, number of sessions, type of data operations your users are up to... What kind of SQL is getting fed to the database, does it use bind variables, does it require full table scans?
Oracle tuning is more successful when you look at the whole picture. Tell us more...

share|improve this answer
    
The Sql queries makes extensive use of Bind Variables, FTS and Views. These are dynamic queries generated by the application. A few times two or more composite views are joined. How can we circumvent this problem? –  Jack Aug 27 '11 at 18:39

When the CPU is high, what is in the sr column in vmstat, compared to when it is low? This is the "scan rate".I bet you will find that the OS is working hard to find free pages in that scenario.

Have a play with the Memory Advisor and see what it says - adding 4G to an 80G SGA may not be worth it depending on your actual workload, especially on an OLTP system unless you have masses of reference data.

share|improve this answer
    
i've uploaded the vmstat output to the original question. The CPU was reporting 30% to 75% util during this time. –  Jack Aug 25 '11 at 21:47

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.