Oracle Monitoring and Managing Memory

Memory Management Mode	You Set	Oracle Database Automatically Tunes
Automatic memory management	Total memory size for this instance (Optional) Maximum memory size for this instance	Total SGA size SGA component sizes Instance PGA size Individual PGA sizes
Automatic shared memory management and automatic PGA memory management (Automatic memory management disabled)	SGA target size (Optional) SGA maximum size Instance PGA target size	SGA component sizes Individual PGA sizes
Manual shared memory management and automatic PGA memory management (Automatic memory management and automatic shared memory management disabled)	Shared pool size Buffer cache size Java pool size Large pool size Instance PGA target size	Individual PGA sizes

The System Global Area

* SGA components are sized in granules. A granule is an area of contiguous memory.
o The exception to this is the log buffer, which is not sized in granules.
o Regardless of what size you specify for a particular SGA component, it will be rounded up to the next whole granule.
+ The exclusion is again the log buffer
* Made up of the following required components:
o Shared Pool
o Log Buffer
o Db Buffer Cache Default Pool

* Following are optional requirements:
o Large Pool
o Streams Pool
o Java Pool
o DB Buffer Cache Keep Pool
o DB Buffer Cache Recycle Pool
o DB Buffer Cache nK Block Size Pools
* LOG_BUFFER and SGA_MAX_SIZE are two of the only static SGA parameters.

The Shared Pool

* Sized according to the SHARED_POOL_SIZE parameter. If set too low, the instance will not start.
* Has 36 components which are sized automatically.
o SQL area & Library Cache – recently executed SQL statements & execution plan
o Data Dictionary Cache (aka row cache) – data dictionary info being used to parse SQL statements
o Active Session History – recent activity by users which is flushed to the AWR
* There is a Shared Pool Advisor to determine how large it should be.

The Database Buffer Cache

The LRU List and the Checkpoint Queue

* Every buffer in the db buffer cache will be in one of three states: pinned, dirty or free.
o A pinned buffer is a buffer that is in use.
o A dirty buffer is one that has not yet been flushed to disk.
o A free or clean buffer is when the block has been copied into the cache and not yet changed or it has been changed and already flushed to disk.
* There should be many free buffers in the cache at any time. The decision on which buffer to use will be determined by the LRU List. The oldest accessed block will be overwritten.
* Another list is the Checkpoint Queue. This is a list of dirty buffers waiting to be written to disk by DBWn.
* The process that searches for free buffers in the LRU List also notifies the Checkpoint Queue of dirty buffers and adds them to the list. The LRU blocks will be written to disk first.
* When data gets written to disk:
o A server process takes too long to find a free buffer.
o The checkpoint queue is too long.
o Every 3 seconds, DBWn writes dirty buffers to disk.
* The only time that all dirty buffers get written to disk is when a checkpoint occurs (ex. SHUTDOWN, or alter system checkpoint).
* Tablespace checkpoints occur:
o tablespace is dropped
o tablespace made read-only
o tablespace put into hot backup mode
o tablespace is taken offline
* Log switches do not trigger a checkpoint.

The Default, Keep and Recycle Pools

* Each pool within the DB buffer cache has its own LRU list and checkpoint queue.
* The default pool is the only required pool and has its size determined by DB_CACHE_SIZE.
* To ensure that blocks of segments that are constantly used are always in memory, create a db buffer cache “keep” pool by setting the DB_KEEP_CACHE_SIZE parameter.
o SQL> alter index emp_name_idx(storage buffer_pool keep);
* To ensure that blocks of segments that are unlikely to be reaccessed are pushed out of memory as quickly as possible, create a db buffer cache “recycle” pool by setting the DB_RECYCLE_CACHE_SIZE parameter.
o SQL> alter table emp(storage buffer_pool recycle);

The Nonstandard Block Size Pools

* The DB_BLOCK_SIZE parameter is used for formatting the SYSTEM and SYSAUX and temporary tablespace datafiles. It can NEVER be changed.
* In order to create tablespaces with different block sizes you must set the appropriate db buffer cache pools:
o DB_2K_CACHE_SIZE
o DB_4K_CACHE_SIZE
o DB_8K_CACHE_SIZE
o DB_16K_CACHE_SIZE
o DB_32K_CACHE_SIZE
o You cannot set the above parameter if it is already your default. It will throw an error.
* By creating additional buffer pools, you will be able to copy tablespaces between databases with different block sizes.
o SQL> alter system set db_16k_cache_size=4m;
o SQL> create tablespace ts_16k datafile ‘ts_16k.dbf’ size 400m blocksize 16k;
* You cannot create a tablespace of nonstandard block size that is of type temporary.
* The keep and recycle buffers can only be of the default block size.

Sizing the Database Buffer Cache

* The cache should be large enough to cache blocks that are repeatedly accessed, but not so large that it caches blocks that are used only once.
* The Database Buffer Cache Advisor is available via V$DB_CACHE_ADVICE

The Log Buffer

* Short term staging area for changes before they are written to the redo logs.
* LGWR writes to disk:
o When a user commits a transaction
o When the log buffer is 1/3 full (even if no commit has been issued)
o Right before DBWn, writes (redo logs must have data before datafiles)
o Every three seconds
* The LOG_BUFFER parameter determines the size

Other SGA Areas

The Large Pool

* The purpose of the large pool is to reduce strain on the shared pool
* Will be used instead of the shared pool for the following:
o Shared Server configuration – UGAs will be stored in the large pool instead of shared
o Parallel execution servers
o I/O slave processes
o RMAN
* Size is determined by the dynamic LARGE_POOL_SIZE parameter.
* To monitor usage, view V$SGASTAT

The Java Pool

* Its purpose is to provide room for the runtime memory structures used by a Java application
* It’s technically “optional” but from a practical standpoint, necessary.
* Controlled by three instance parameters:
o JAVA_POOL_SIZE – dynamic; creates the Java pool and is limited by SGA_MAX_SIZE. Default is 24MB.
o JAVA_MAX_SESSIONSPACE_SIZE – static; max space for any one session
o JAVA_SOFT_SESSIONSPACE_LIMIT – static; if a session’s Java pool goes above this limit then a message is written to a trace file

The Streams Pool

* Stores changes that exist in the redo logs in order to propagate changes to other databases at remote sites.
* The changes from the redo logs are physical change records and can only be directly applied to datafiles, not applied logically to tables.

Automatic Shared Memory Management (ASMM)

* ASMM manages and auto-tunes the following components if they are set to zero or not set (if SGA_TARGET has a value):
o DB buffer cache default pool, DB_CACHE_SIZE
o Shared pool, SHARED_POOL_SIZE
o Large pool, LARGE_POOL_SIZE
o Java pool, JAVA_POOL_SIZE
* If the parameters have been set then Oracle will not lower the values below the setting
* These structures must be set manually:
o DB buffer cache keep pool, DB_KEEP_CACHE_SIZE
o DB buffer cache recycle pool, DB_RECYCLE_POOL_SIZE
o DB buffer cache nonstandard block size pools, DB_nK_CACHE_SIZE
o Streams pool, STREAMS_POOL_SIZE
o Log buffer, LOG_BUFFER
* To enable ASMM set the parameter SGA_TARGET
* ASMM will not work if the STATISTICS_LEVEL is set to BASIC. It needs to gather stats in order to monitor and adjust the SGA.
* Use of an spfile allows Oracle to remember the values of the various components for the next startup.
* You cannot change the SGA_TARGET to a value greater than SGA_MAX_SIZE while the instance is running (you will get an error). However, if set in the spfile, then the SGA_MAX_SIZE will be adjusted to SGA_TARGET on startup.
* ASMM starts an additional background process called MMAN. It controls the tuning to the various SGA components.

The Program Global Area (PGA)

* The PGA stores information private to a particular session. It is created at the start of each session and a separate PGA (each of variable sizes) exists for each session.
* Information contained in the PGA is:
o Sort space for sorting, joining, and aggregating rows (perhaps most important)
o Bind information
o Session variables
o Stack space
o Cursors
* The UGA is a part of the PGA.
* Two important parameters exist for the PGA:
o WORKAREA_SIZE_POLICY – [AUTO | MANUAL] Should be set to AUTO
o PGA_AGGREGATE_TARGET – Total amt of memory used for all PGAs (defaults to 20% of the SGA size)
o
+ If all memory in PGA_AGGREGATE_TARGET is used up, then Oracle will take memory from one PGA and distribute it to another
o
+ In the rare case that there is no memory that can be pulled away, the session will either use temporary space or break the PGA_AGGREGATE_TARGET setting.
* V$PGASTAT & V$PGA_TARGET_ADVICE are views used to monitor and size the PGA.
* If the WORKAREA_SIZE_POLICY is set to MANUAL, the PGA will rely on the following parameters (if set to AUTO the following will be ignored):
o SORT_AREA_SIZE
o HASH_AREA_SIZE
o BITMAPMERGE_AREA_SIZE
o CREATE_BITMAP_AREA_SIZE
o The problem with MANUAL policy is that PGA will grow but never shrink. Oracle will be unable to reassign memory between PGAs.

Parameters

* SGA_MAX_SIZE – SGA can’t exceed this limit
* SHARED_POOL_SIZE
* STATISTICS_LEVEL – must be set to TYPICAL in order to use V$SHARED_POOL_ADVICE
* DB_CACHE_SIZE – size of db buffer cache default pool
* DB_KEEP_CACHE_SIZE – size of db buffer cache keep pool
* DB_RECYCLE_CACHE_SIZE – size of db buffer cache recycle pool
* LOG_BUFFER – size of the log buffer in the SGA
* CPU_COUNT – (depending on the value) determines the size of the LOG_BUFFER
* LARGE_POOL_SIZE – size of the large pool
* WORKAREA_SIZE_POLICY – PGA policy which should be set to AUTO
* PGA_AGGREGATE_TARGET – Total amt of memory used for PGAs (defaults to 20% of the SGA size)

Views & Tables

* V$SGASTAT – sizes of various SGA components
* V$SGAINFO – see granule size
* V$SHARED_POOL_ADVICE – help size your shared pool
* V$DB_CACHE_ADVICE
* V$PGASTAT – see current allocations on the PGA
* V$PGA_TARGET_ADVICE – similar to other advice views; has factor field and predictions about effects

source:http://mnsinger.wordpress.com/2006/09/05/chapter-34-monitoring-and-managing-memory/
http://download-uk.oracle.com/docs/cd/B28359_01/server.111/b28301/instance004.htm