Ask the Expert

SGA sizing in Oracle

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.

    Requires Free Membership to View

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.

This was first published in April 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: