memory resize operations in 11G

http://blogs.oracle.com/UPGRADE/2011/03/new_hidden_parameters_in_oracl.html



since Oracle 11.2.0.1 even though you haven't set neither SGA_TARGET nor MEMORY_TARGET (or set it to 0) the database might still do memory resize operations.
Reason why this behaviour has been changed: Prevention of ORA-4031 errors.
ORA-04031: unable to allocate string bytes of shared memory

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

But on databases with extremly high loads this can cause real trouble. Further information can be found in MOS Note:1269139.1 . And the parameter set to TRUE by default is called

_MEMORY_IMM_MODE_WITHOUT_AUTOSGA=TRUE

Metalink note :

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

SGA re-sizes are occurring after upgrade to 11.2 despite the fact that automatic memory management (AMM/ASMM) is disabled via the MEMORY_TARGET and SGA_TARGET parameters being set to zero.

Checking V$MEMORY_RESIZE_OPS / V$SGA_RESIZE_OPS shows an IMMEDIATE re-size having occurred, e.g.:

COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME
---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- ------------ ----------- ---------- --------- -------------------- --------------------
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 7.4357E+10 7.4088E+10 7.4088E+10 COMPLETE 12-OCT-2010 14:26:51 12-OCT-2010 14:26:51
shared pool GROW IMMEDIATE shared_pool_size 2684354560 2952790016 2952790016 COMPLETE 12-OCT-2010 14:26:51 12-OCT-2010 14:26:51

Changes

The database has been upgraded to 11.2.

Cause

This is expected behavior in 11.2 for immediate memory allocation requests, which added this as a new feature when automatic memory management was disabled.

Solution

Ignore the resize operation which is performed to prevent an ORA-4031 error from being raised, or set the parameter _MEMORY_IMM_MODE_WITHOUT_AUTOSGA to false in the instance to disable this feature with the consequence that in future an ORA-4031 error would be raised, e.g.:

connect / as sysdba
alter system set "_memory_imm_mode_without_autosga"=FALSE scope=both;
exit