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;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips