Re: SGA Utilization

From: amit <amit.shivpuri_at_gmail.com>
Date: 25 Dec 2006 21:30:38 -0800
Message-ID: <1167111038.721594.60090@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 Mon Dec 25 2006 - 23:30:38 CST