SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled – Oracle 11.2

Its been observed that after upgrading the database to 11gR2 (11.2.0.2 onwards) SGA components are getting resized even when automatic memory management (MEMORY_TARGET=0) and automatic shared memory management (SGA_TARGET=0) is disabled. Resize are happening to an extent that the component sizes are shrinking below the minimum size set by DBAs.

This typically appears as growth in the __SHARED_POOL_SIZE value and a reduction in the __DB_CACHE_SIZE value being used in the instance, such that __DB_CACHE_SIZE may be shrunk below the DB_CACHE_SIZE value specified in the init.ora/spfile.

(In case if you are not aware __<param name> are the dynamic value of the component after database start up)

Checking V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS shows an IMMEDIATE re-size having occurred

SQL>select COMPONENT, PARAMETER, INITIAL_SIZE/1024/1024 "INITIAL_SIZE",FINAL_SIZE/1024/1024 "FINAL_SIZE", STATUS from v$SGA_RESIZE_OPS where component in ('DEFAULT buffer cache','shared pool') order by component, start_time;

COMPONENT PARAMETER INITIAL_SIZE FINAL_SIZE STATUS
---------------------------------------- ------------------------------ ------------ ---------- ---------
DEFAULT buffer cache db_cache_size 3280 3264 COMPLETE
DEFAULT buffer cache db_cache_size 3280 3264 COMPLETE
DEFAULT buffer cache db_cache_size 0 3280 COMPLETE
DEFAULT buffer cache db_cache_size 3264 3248 COMPLETE
DEFAULT buffer cache db_cache_size 3248 3232 COMPLETE
DEFAULT buffer cache db_cache_size 3232 3216 COMPLETE
DEFAULT buffer cache db_cache_size 3216 3200 COMPLETE
DEFAULT buffer cache db_cache_size 3200 3184 COMPLETE
DEFAULT buffer cache db_cache_size 3184 3168 COMPLETE
shared pool shared_pool_size 0 1024 COMPLETE
shared pool shared_pool_size 1024 1040 COMPLETE

COMPONENT PARAMETER INITIAL_SIZE FINAL_SIZE STATUS
---------------------------------------- ------------------------------ ------------ ---------- ---------
shared pool shared_pool_size 1040 1056 COMPLETE
shared pool shared_pool_size 1056 1072 COMPLETE
shared pool shared_pool_size 1072 1088 COMPLETE
shared pool shared_pool_size 1088 1104 COMPLETE
shared pool shared_pool_size 1104 1120 COMPLETE
shared pool shared_pool_size 1120 1136 COMPLETE

17 rows selected.

SQL>

You might think this is a bug, but this is an expected behavior. This is done to prevent an ORA-4031 error from being raised. So if shared_pool falls short of memory, Oracle extracts memory from db_buffer_cache and allocates the same to shared_pool.

But, enhancement Bug:13340694 has been logged to provide a warning message in the alertlog when such allocations occur to provide the DBA with an indication of a memory problem, as otherwise this can result in the buffer cache being shrunk below the specified minimum value, and hence lead to performance degradation

Is it possible to disable this expected behavior ?

Yes, this feature can be disabled by setting _MEMORY_IMM_MODE_WITHOUT_AUTOSGA=false. This can be done using alter system command.


connect / as sysdba

alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;

exit

References:

1269139.1 – https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=BULLETIN&id=1269139.1

Hope this helps !!

Advertisements

Oracle Database 11g new feature – Automatic Memory Management

Automatic Memory Management was a new feature introduced in 10g. With 10g release oracle has come up with anew parameter called sga_target which was used to automatically manage the memeory inside SGA.
The components which were managed by sga_target are db_cache_size, shared_pool_size, large_pool_size, java_pool_size and streams_pool_size

With 11g, Oracle went a step further to manage both SGA as well as PGA automatically. Oracle database 11g introduced 2 new parameters – memory_target and memory_max_target

The memory_target Parameter

The memory_target parameter is somewhat a combination of the sga_target parameter value and the pga_aggregate_target parameter, representing the total amount of memory that Oracle has to allocate between the various SGA and PGA structures. The memory_target parameter is dynamic and can be changed up to and including the value of memory_max_target, which we discuss next.

The memory_max_target Parameter

The memory_max_target parameter allows you to dynamically change the value of the parameter memory_target within the confines of memory_max_target. Thus you can adjust the total amount of memory available to the database as a whole at any time.

NOTE: The memory_target and memory_max_target parameters cannot be used when LOCK_SGA has been set. Also memory_target and memory_max_target cannot be used in conjunction with huge pages on Linux.

Following fig show the hierarchy of memory parameters and components that it tunes. This fig is taken from Robert Freeman book – Oracle database 11g: New features for Administrator.

Also below fig from same book show the effect of setting memroy_target, memroy_max_target and sga_target.

While setting these new parameters (memroy_target and max_memory_target), one has to be careful. A general advice would be to set sga_target and pga_aggregate_target to a minimum fixed value and set memrory_target. Oracle will automatically increase sga_target and pga_aggregate_target to the desired level.

If you are upgrading the existing 10g database to 11g and want to keep the current value of sga_target and pga_aggregate_target, than make sure you set the value of memroy_target >= (sga_target + pga_aggregate_target).

With new hirarchy in memeory management, Oracle has also introduced new memroy advisory. You can view V$MEMORY_TARGET_ADVICE view to get advice on the optimal value of memroy_target parameter. This view will show advisory data only after you set memory_target parameter.

Hope this helps !!