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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top