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.

set linesize 120
set pagesize 60          

set trimspool on          

break on timed_at skip 1          

column oper_type format a14
column component format a24
column parameter format a21          

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
;          

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.