Problem solve
Get help with specific problems with your technologies, process and projects.
SGA sizing in Oracle
An Oracle user asks about a discrepency in SGA sizing.
Do you know why there is an SGA discrepancy?
1. Oracle 10.2.0.1.0
2. The SGA was inadequately sized... suggest 1480M
3. sho parameter sga
sga_max_size big_integer 1148
sga_target big_integer 1148
4. alter system set sga_max_size=1480M scope=spfile;
System altered
5. startup force
[good]
6. sho parameter sga
sga_max_size big_integer 1776M
sga_target big_integer 1776M
How did 1776M occur? Why isn't it 1480M?
And I did not have to do an "alter sga_target" and yet sga_target "automatically" changed?
I'm not unhappy about this as the intent was to increase sga_target, but I've done the SGA alters on another server and did not run into this.
Oracle does not always obey your SGA sizing to the letter you explicitly state. Oracle has to allocate memory in chunks called "granules." When you tell Oracle the SGA size should be X MB in size, Oracle rounds this up to the nearest granule size, which is at least X MB in size. If the SGA is less than 128MB in total size, the granule size is 4MB, otherwise, it is 16MB. Notice that 1776MB is an exact multiple of 16MB, whereas 1480MB is not. Additionally, different memory structures in the SGA cannot cross granule boundaries. So if you have specified the log buffer size, the log buffer may increase to be fully contained in a number of granules. The shared pool cannot use the log buffer space, so it needs its own granules. Because each SGA memory area cannot cross granule boundaries, it is likely that Oracle computed more than one additional granule was needed to hold at least 1480MB of total SGA.
