Hi all, I hope you can help me. Senior DBA doesn't seem to think that the size of SGA is causing performance problem on a Solaris 2.6, But comming from Sys Admin Background to an Oracle position, I think the size of SGA is way to small, h ere's what the values are:
Total System Global Area 104807824 bytes
Fixed Size 64912 bytes
Variable Size 47218688 bytes
Database Buffers 57344000 bytes
Redo Buffers 180224 bytes
This is too small for a system with 2GB of Memory, 2 cpu, and over 769 Mb of swap(I didn't set the system up, personally I would make swap biger). I notice when the database starts up initially and running within a day or so, the memory usages is under 45%, whereas after several days, it maxs out the memory, we only have about 36Mbytes free. The only apps running on this box is oracle81 version one. How do I determined if the SGA needs to be resized and what is a good rule of thumb. How do I get my point across that SGA definitely needs to be look at. Thanks.
Since SGA is not big, what else is causing your system to tkae up your memory.
Oracle does not know about the physical RAM, only the SGA.
You need to find out which process is causing physical RAM to be used up.
One thing which may cause is heavy sorting.
Anyway SGA does seem to be to small.
DBA has to see various waits, hit ratios and may be the end users might be getting
ORA 4031 errors.
I think you hv to keep on checking your performance parameters. like. library cache, reads/call, hitratios etc.
and depending on those you hv to resize your SGA.
thatz the general rule which is being followed.
lets c what gurus tell.
The Time has come ....
Well itīs small or not that small depending itīs use
Check PGA, sort area size etc
I would set up STATSPACK and compare performance stats over a period of time. You mentioned you start to see some degradation after several days, so compare how it looks when it seems fine and when it starts to go down hill. This may help you get an idea of what areas are suffering most. (be sure TIMED_STATISTICS is set to TRUE)
It also gives you some ground to stand on when you make your suggestions to the senior DBA.
But definitely find out what other processes are running on the server. We normally turn off all the bells and whistles on our database servers so we can maximize the use of memory.
Click Here to Expand Forum to Full Width