How much memory is been utilized by the SGA

We are running SAP 4.6c with ORACLE 9.2.0.5 on AIX. Database is configured with Dynamic SGA, and we have set SGA_MAX_SIZE also.

When we look at the memory management at OS level it shows high utilization of memory.

We don’t know whether the memory allocated to SGA is completely used or not.
How to find out how much memory is been utilized by the SGA, whether it needs to be allocated more memory or can be reduced from the SGA.

v$sga will give you the current sga stats

Run statspack to analyze whether you reqiure more sga or not

You can also look at V$SGASTAT view. Try running the following script (note the part about free memory):

CODE

set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off


col num              format 999      heading "Nbr"
col name             format a20      heading "Name"
col type             format 999      heading "Type"
col value            format a10      heading "Value"
col meg              format 99.99    heading "Size|Meg"
col isdefault        format a10      heading "IsDefault"
TTITLE "SHARED POOL PARAMETERS (DICTIONARY AND LIBRARY CACHE)"
select num, name, type, value, (to_number(value) / 1024) / 1024 meg,
isdefault
from v$parameter
where name = 'shared_pool_size';


col AA format 99.99    heading "Size MB"
col BB format 99.99    heading "Free MB"
col CC format 99.99    heading "% Free"
col DD format 99999999 heading "Executions"
col EE format 999999   heading "Reloads"
col FF format 999.99   heading "% Reload"
col GG format a20        heading "Parameter"
col HH format 99,999,999 heading "Count|(entries)"
col II format 99,999,999 heading "Usage|(valid entries)"
col JJ format 99,999,999 heading "Gets|(memory)"
col KK format 9,999,999  heading "Misses|(disk)"
col LL format 99.99      heading "% Misses"


TTITLE CENTER 'V$PARAMETER (SHARED POOL SIZE) AND V$SGASTAT (FREE MEMORY)
REPORT '
select (to_number(VP.value) / 1024) / 1024 AA,
      VS.bytes / 1048576 BB,
      (VS.bytes / to_number(VP.value)) * 100 CC
from  v$parameter VP, v$sgastat VS
where VS.name = 'free memory'
and   VP.name = 'shared_pool_size';


PROMPT 

PROMPT More than 5% Free = lower shared_pool_buffer parameter


TTITLE CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT' STITLE
CENTER 'V$LIBRARYCACHE (SHARED SQL AREA) SUMMARY REPORT'
select sum(VL.pins) DD,
      sum(VL.reloads) EE,
      (sum(VL.reloads) / sum(VL.pins)) * 100 FF
from v$librarycache VL;


PROMPT
PROMPT More than 1% Reloads = raise shared_pool_size parameter


TTITLE CENTER 'V$ROWCACHE ENTRIES DETAIL REPORT (DICTIONARY)'
select parameter GG,
      /* count HH, */
      /* usage II, */
      gets JJ,
      getmisses KK,
      (getmisses / (gets + getmisses + 1)) * 100 LL
from v$rowcache
order by parameter;


PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.


TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
      sum(getmisses) KK,
      (sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;


PROMPT
PROMPT Not tunable at this level of detail, provided for information
only.


TTITLE CENTER 'V$ROWCACHE ENTRIES SUMMARY REPORT (DICTIONARY)'
select sum(gets) JJ,
      sum(getmisses) KK,
      (sum(getmisses) / (sum(gets) + sum(getmisses) + 1)) * 100 LL
from v$rowcache;


PROMPT
PROMPT More than 5% Misses (summary) = raise shared_pool_buffer parameter





exit;