Q

SGA sizing in Oracle

An Oracle user asks about a discrepency in SGA sizing.

This Content Component encountered an error
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.
This was first published in April 2008

Dig deeper on Oracle database administration

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close