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 !!

About these ads

2 thoughts on “SGA Re-Sizes Occurring Despite AMM/ASMM Being Disabled – Oracle 11.2

  1. Thanks for Sharing.

    ER suggests the following text: WARNING: “IMMEDIATE autotuning memory request allocated to avoid ORA-4031″
    For not ‘so smart’ monitoring systems, which are using regular expressions to probe for ORA errors in alert log might trigger fake incidents/tickets because of such text. I think, a more meaningful way would be to have a warning something like this : “Immediate transfer of granules performed from to to avoid out of memory errors”

  2. Bad formatting, posting it again, hoping that its correct this time around:
    Immediate transfer of ‘n’ granules performed from ‘donor’ to ‘receiver’ to avoid out of memory errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s