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