SGA resizing history
                                                 Last update (2011-05-13 21:42:31)
                                                                                                                   Date added (2010-09-15 12:49:45)

Summary
You can find all the resizing history of the SGA by querying the view V$SGA_RESIZE_OPS. As you know if you have set the parameter sga_target then Oracle automatically SHRINKs and GROWs the underlying pools.

If you want to change how frequently resizing happens, then you have to set the hidden parameter _memory_broker_stat_interval=600 (30 seconds is the default value and 600 is 10 minutes)

Watch an example below how resizing happens:
SELECT component, oper_type, oper_mode, parameter, 
initial_size/1024/1024 init_MB, 
target_size/1024/1024 target_MB, 
final_size/1024/1024 final_MB,
status, start_time, end_time 
FROM V$SGA_RESIZE_OPS;

COMPONENT            OPERATION  OPER_MODE PARAMETER            INIT_MB  TARGET_MB   FINAL_MB STATUS    START_TIME    END_TIME
-------------------- ---------- --------- ----------------- ---------- ---------- ---------- -------
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20256      20240      20240 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3888       3904       3904 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20240      20224      20224 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3904       3920       3920 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20224      20208      20208 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3920       3936       3936 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20208      20192      20192 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3936       3952       3952 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20192      20176      20176 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3952       3968       3968 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20176      20160      20160 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3968       3984       3984 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20160      20144      20144 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3984       4000       4000 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20144      20128      20128 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4000       4016       4016 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20128      20112      20112 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4016       4032       4032 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20112      20096      20096 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4032       4048       4048 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20096      20080      20080 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4048       4064       4064 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20080      20064      20080 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4064       4080       4064 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20080      20064      20064 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4064       4080       4080 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20064      20048      20048 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4080       4096       4096 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20048      20032      20048 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4096       4112       4096 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20048      20032      20048 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4096       4112       4096 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20048      20032      20032 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4096       4112       4112 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20032      20016      20032 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4112       4128       4112 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20032      20016      20032 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        4112       4128       4112 COMPLETE  10/09/2010 10/09/20
shared pool          SHRINK     DEFERRED  shared_pool_size        4112       4016       4016 COMPLETE  10/09/2010 10/09/
DEFAULT buffer cache GROW       DEFERRED  db_cache_size          20032      20128      20128 COMPLETE  10/09/2010 
shared pool          SHRINK     DEFERRED  shared_pool_size        4016       3920       3920 COMPLETE  10/09/2010 10/09/
DEFAULT buffer cache GROW       DEFERRED  db_cache_size          20128      20224      20224 COMPLETE  10/09/2010 
shared pool          SHRINK     DEFERRED  shared_pool_size        3920       3824       3824 COMPLETE  10/09/2010 10/09/
DEFAULT buffer cache GROW       DEFERRED  db_cache_size          20224      20320      20320 COMPLETE  10/09/2010 
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20320      20304      20304 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3824       3840       3840 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20304      20288      20288 COMPLETE  10/09/201
shared pool          GROW       IMMEDIATE shared_pool_size        3840       3856       3856 COMPLETE  10/09/2010 10/09/20
DEFAULT buffer cache SHRINK     IMMEDIATE db_cache_size          20288      20272      20272 COMPLETE  10/09/201
Frequent pool resizing (for example every second) is something that should be avoiding. If you notice SGA constantly resizing you can check for the wait event: SGA: allocation forcing component growth

This wait event means that the process is waiting on an immediate mode memory transfer(because SGA is auto tuned) after an ORA-04031: unable to allocate x bytes of shared memory error for MMAN to get the memory and post it.

if you want to monitor how much memory is available for resizing use the query:
select * from v$sga_dynamic_free_memory;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search