I’m still a bit twitchy (i.e. nervous) about using the automatic SGA tuning – which means setting the sga_target and letting Oracle decide when to move memory between (typically) the db cache and the shared pool. I’ve come across four systems so far – two by direct contact, two by indirect report – which have had hanging problems which magically disappeared when the sga_target was disabled. It has to be said that all four systems had large SGAs – 8GB to 64GB – and they were all busy with lots of active sessions, so small office systems are probably quite safe using the sga_target.
Whilst it’s hard to be specific about why the problems occur, it seems likely that it is possible that some sort of race condition occurs where the code decides that the db cache needs to grow moments after it has started to shrink it to allow the shared pool to grow. (Or vice versa, of course).
If you think it’s worth keeping an eye what the sga_target is doing to your system, you might want to run a quick query against the v$sga_resize_ops view from time to time, to see if there are times when a couple of the heaps swap memory back and forth in rapid sequence. The following SQL was a quick script I hacked together to do this on the last site where random hangs were occurring:
column component format a20 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 set linesize 90 set pagesize 60 spool sga_ops select component, oper_type, oper_mode, parameter, initial_size, target_size, final_size, status, to_char(start_time,'dd-mon hh24:mi:ss') start_time, to_char(end_time,'dd-mon hh24:mi:ss') end_time from v$sga_resize_ops order by start_time ; spool off
Note: I only realised some days after I wrote this note that my output was only appearing in the correct order because of lucky timing. The order by clause is using my column alias of start_time not the underlying table (view) column of the same name. If I had had any data from the previous month in v$sga_resize_ops then my choice of character conversion would have made it appear at the end of the output.
At the linesize of 90 I set, this produced a two-line per row output – the following is from my laptop, not from one of the big systems:
COMPONENT OPER_TYPE OPER_MODE PARAMETER -------------------- ------------- --------- ------------------------------ INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME --------------- --------------- --------------- --------- --------------- --------------- java pool GROW IMMEDIATE java_pool_size 4,194,304 8,388,608 8,388,608 COMPLETE 04-dec 04:05:22 04-dec 04:05:22 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 402,653,184 398,458,880 398,458,880 COMPLETE 04-dec 04:05:22 04-dec 04:05:22 DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 398,458,880 394,264,576 394,264,576 COMPLETE 04-dec 04:05:24 04-dec 04:05:24 java pool GROW IMMEDIATE java_pool_size 8,388,608 12,582,912 12,582,912 COMPLETE 04-dec 04:05:24 04-dec 04:05:24
Another symptom that you might watch out for at the same time is memory in the shared pool being used for data blocks – as Tanel Poder has pointed out, when v$sgastat is showing memory in the class “KGH: NO ACCESS”, this is a case of shared pool granules partially freed for use by the db cache and waiting for the rest of the granule content to be unpinned so that the entire granule can become a cache granule.