Re: SGA Utilization

First, at instance startup, the Oracle server requests SGA_MAX_SIZE bytes of memory, in your case 2GB. because your SGA size is greater than 128MB then the granule ( unit of memory allocation ) size is 16MB.
When allocationg SGA structures, the value requested ( ex SHARED_POOL_SIZE ) is rounded up
to make an integer number of granules.
if in my init<SID>.ora I have shared_pool_size=50M, Oracle will allocate (16+16+16=48 it's not enough I need 50 so add 16 and total = 64MB ) You can confirm like this :
SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
shared_pool_size                     big integer 50331648

SQL> select sum(bytes) "Shared pool size" from v$sgastat where pool='shared pool';
Shared pool size

        67108864

As startup continues, each component ( shared pool, large pool, java pool, buffer_cache, ...) will attempt
to acquire the integer number of granules assigned.

V$SGASTAT gives you all informations you need .

The query below gives you Free SGA size :

SQL> select sum(bytes)/1024/1024 " Free SGA size in MB "   2 from v$sgastat
  3 where name='free memory';

Free SGA size in MB

          84,9717064

The total SGA utilization is given by the query below :

SQL> select sum(bytes)/1024/1024 " SGA size used in MB   2 from v$sgastat
  3 where name!='free memory';

 SGA size used in MB

           44,1239929

Note that you can increase the SGA size up to SGA_MAX_SIZE

Hope this answer your question !

Archy.
DBA "amit" <amit.shivpuri_at_gmail.com> wrote in message news:1166664296.110890.62830_at_80g2000cwy.googlegroups.com...
> Hi all,
>
> Please, can anyone put some light on how we can check the SGA
> utilzation.
>
> Scenario-
> --------------------
> Database: Oracle 9i
> SGA_MAX_SIZE: 2gb Initially
> Total SGA allocated size: 1gb Initially
>
> Now how can we check that out of 1GB how much SGA is getting utilitzed.
> If 1gb is sufficient or we need to increase the size.
>
> Thank
> amit
>
Received on Fri Dec 22 2006 - 09:23:51 CST