SGA Resizing

Some time ago, I wrote about resizing the SGA and the problems of automatic SGA management. In the follow-up, one of of the suggestions was to set a minimum value for the db cache and the shared pool to avoid the risk of the memory management thrashing memory back and forth between the two areas.

Since then, I’ve taken to checking the frequency of SGA resizing operations whenever I visit a site that uses ASMM, and using the results as a guideline to suitable minimum sizes for the different memory areas – or even as a threat indicator that ASMM should be disabled. Based on the results below, for example, I’d think about  setting the shared pool size to a minimum of 800M.

rem
rem	Script:		sga_resize_ops.sql
rem	Author:		Jonathan Lewis
rem	Dated:		December 2006
rem	Purpose:	
rem
rem	Last tested 
rem		11.2.0.4
rem		11.1.0.7
rem		10.2.0.3
rem	Not tested
rem	Not relevant
rem		 9.2.0.8
rem		 8.1.7.4
rem
rem	Notes:
rem	Quick and Dirty for 10g
rem	SGA resizing operations
rem
rem	Watch out for rapid fluctuations
rem
rem	In 10.2 check also v$sgastat for  KGH: NO ACCESS 
rem	which is memory in a shared pool (or other) granule
rem	slowly being acquired by the db cache.
rem
rem	Will have to write a new version for 11g which
rem	uses v$memory_resize_ops to deal with PGA/SGA
rem	interaction (and ASM) as well
rem
rem	Look at v$sga_dynamic_components (and in 11g the 
rem	equivalent v$memory_dynamic_components) for summary
rem	view of granule sizes (which implies a future option
rem	for varying granule sizes).
rem
rem	See also: V$SGA_DYNAMIC_FREE_MEMORY (x$ksge)

start setenv
set timing off

column component format a25
column parameter format a30 

column initial_size format 99,999,999,999 
column target_size  format 99,999,999,999 
column final_size   format 99,999,999,999 

column started_at format a15
column ended_at   format a15

set linesize 90 
set pagesize 60 

spool sga_resize_ops 

prompt	============
prompt	Full History
prompt	============

select 
        component, 
        oper_type, 
        oper_mode, 
        parameter, 
        initial_size, 
        target_size, 
        final_size, 
        status, 
        to_char(start_time,'dd-mon hh24:mi:ss') started_at, 
        to_char(end_time,'dd-mon hh24:mi:ss')   ended_at 
from 
        v$sga_resize_ops 
where
	initial_size != 0
or	target_size  != 0
or	final_size   != 0
order by 
        start_time 
; 

prompt	==============
prompt	Since midnight
prompt	==============

set linesize 120
set pagesize 60

break on starting_time skip 1

column oper_type format     a12
column component format     a25
column parameter format     a22
column timed_at  format     a10

select
	to_char(start_time,'hh24:mi:ss') timed_at,
	oper_type,
	component,
	parameter,
	oper_mode,
	initial_size,
	final_size
from
	v$sga_resize_ops
where
	start_time > trunc(sysdate)
order by
	start_time, component
;


spool off

Here’s an extract from the output I saw at one site. As you can see, there are some fairly busy moments during the day; and one really bizarre storm of activity around 2:20 in the morning. I don’t know what was going on at 2:20, or again at 14:06, but there were a couple of spikes of waits for “cursor: pin S wait on X” at about the same time.

TIMED_AT OPER_TYPE  COMPONENT            PARAMETER            OPER_MODE INITIAL_SIZE FINAL_SIZE
-------- ---------- -------------------- -------------------- --------- ------------ ----------
01:41:48 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1358954496 1392508928
         SHRINK     shared pool          shared_pool_size     DEFERRED     738197504  704643072          

01:42:54 SHRINK     DEFAULT buffer cache db_cache_size        DEFERRED    1224736768 1191182336
         GROW       shared pool          shared_pool_size     DEFERRED     872415232  905969664          

01:44:18 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1392508928 1426063360
         SHRINK     shared pool          shared_pool_size     DEFERRED     704643072  671088640          

01:44:24 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1191182336 1224736768
         SHRINK     shared pool          shared_pool_size     DEFERRED     905969664  872415232          

01:45:24 SHRINK     DEFAULT buffer cache db_cache_size        DEFERRED    1224736768 1191182336
         GROW       shared pool          shared_pool_size     DEFERRED     872415232  905969664          

01:48:54 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1191182336 1224736768
         SHRINK     shared pool          shared_pool_size     DEFERRED     905969664  872415232          

02:20:25 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1291845632 1325400064
         SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1325400064 1308622848
         SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1308622848 1291845632
         SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1291845632 1275068416
         SHRINK     shared pool          shared_pool_size     DEFERRED     805306368  771751936
         GROW       shared pool          shared_pool_size     IMMEDIATE    771751936  788529152
         GROW       shared pool          shared_pool_size     IMMEDIATE    788529152  805306368
         GROW       shared pool          shared_pool_size     IMMEDIATE    805306368  822083584          

14:01:22 SHRINK     DEFAULT buffer cache db_cache_size        DEFERRED    1241513984 1207959552
         GROW       shared pool          shared_pool_size     DEFERRED     855638016  889192448          

14:02:52 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1207959552 1241513984
         SHRINK     shared pool          shared_pool_size     DEFERRED     889192448  855638016          

14:04:22 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1241513984 1275068416
         SHRINK     shared pool          shared_pool_size     DEFERRED     855638016  822083584          

14:06:52 SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1308622848 1291845632
         GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1275068416 1308622848
         SHRINK     shared pool          shared_pool_size     DEFERRED     822083584  788529152
         GROW       shared pool          shared_pool_size     IMMEDIATE    788529152  805306368          

14:06:55 SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1291845632 1275068416
         GROW       shared pool          shared_pool_size     IMMEDIATE    805306368 822083584          

14:06:58 SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1275068416 1258291200
         GROW       shared pool          shared_pool_size     IMMEDIATE    822083584 838860800          

14:07:01 SHRINK     DEFAULT buffer cache db_cache_size        IMMEDIATE   1258291200 1241513984
         GROW       shared pool          shared_pool_size     IMMEDIATE    838860800  855638016          

14:08:53 GROW       DEFAULT buffer cache db_cache_size        DEFERRED    1241513984 1275068416
         SHRINK     shared pool          shared_pool_size     DEFERRED     855638016  822083584          

14:09:23 SHRINK     DEFAULT buffer cache db_cache_size        DEFERRED    1275068416 1241513984
         GROW       shared pool          shared_pool_size     DEFERRED     822083584  855638016          

Update Sept 2009: If you think that you are suffering problems due to excess resizing operations, check Metalink for document id 7189722.8 – which describes this behaviour as a bug “confirmed as affecting 10.2.0.4”. The bug is fixed in 10.2.0.5 and and 11.2, but there is a workaround offered through a hidden parameter:

alter session set "_memory_broker_stat_interval=999;

The interval is set in seconds, and the default is 30 seconds. I doubt if there’s any specific significance to the value 999 used in the demonstration code from the bug note.

On the downside – bug 6528336 was confirmed in 10.2.0.3 and reported fixed in 10.2.0.4 – so you may still want to be a little cautious about ASMM even in the latest releases.