Re: SGA Utilization

From: Rachid <rachidtamba_at_hotmail.com>
Date: Thu, 28 Dec 2006 21:46:18 -0500
Message-ID: <0k%kh.8973$Nh.87521@wagner.videotron.net>

The Total System Global Area 524288000 bytes is what is reserved for Oracle instance in your server's RAM.
In another word , Oracle can use up to SGA_MAX_SIZE=500M in your case. SQL> select sum(bytes)/1024/1024 " SGA size used in MB" from v$sgastat

         where name!='free memory';

  SGA size used in MB

---

251.23645

251.23645 MB is the amount of memory Oracle is actually consumed from 500MB at the time the query above is issued.

Regards,

Rachid
DBA "amit" <amit.shivpuri_at_gmail.com> wrote in message news:1167111038.721594.60090_at_h40g2000cwb.googlegroups.com...
> hi archy,
> thanks for ur inputs. Please look into below:
> ====================================================
> SQL> show sga
>
>
>
> Total System Global Area 524288000 bytes
>
> Fixed Size 2046008 bytes
>
> Variable Size 423626696 bytes
>
> Database Buffers 92274688 bytes
>
> Redo Buffers 6340608 bytes
>
> SQL>
> SQL> show parameter sga_max_size
>
>
>
> NAME TYPE VALUE
>
> ------------------------------------ -----------
> ------------------------------
> sga_max_size big integer 500M
>
> SQL>
> SQL> select sum(bytes)/1024/1024 " SGA size used in MB" from v$sgastat
> where name!='free memory';
>
>
> SGA size used in MB
>
> --------------------
>
> 251.23645
> ====================================================
> If the sga_max_size and result of show sga is 500m, then how come my
> total sga size is 251MB.
>
>
>
> Archy wrote:
>> 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 Thu Dec 28 2006 - 20:46:18 CST