ASMM

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm#CNCPT810

Automatic Shared Memory Management (ASMM)

In oracle 10g you can now make the memory management automatic, Oracle will allocate and deallocate memory for each of the memory pools based on changing database workloads. The benefits of ASMM are

• • Reduces the change of running out of shared pool memory

  • Uses available memory optimally

  • Improves database performance by constantly matching memory allocations and instance memory needs

There are two system parameters that configure ASMM

Oracle requires a number of pools to work

Under automatic memory control the following memory components will be managed automatically, you can override the ASMM by specifying the size of the component in the init.ora file or setting its value (spfile), remember that the values will be deducted from the SGA_TARGET value. The values also state the minimum value that will be allocated.

• • Buffer cache (DB_CACHE_SIZE)

  • shared pool (SHARED_POOL_SIZE)

  • large pool (LARGE_POOL_SIZE)

  • java pool (JAVA_POOL_SIZE)

  • streams pool (STREAMS_POOL_SIZE)

When using ASMM you still need to manually configure

• • log buffer (LOG_BUFFER)

  • db buffer cache keep pool (DB_KEEP_CACHE_SIZE)

  • db buffer recycle pool (DB_RECYCLE_CACHE_SIZE)

  • db buffer nK block size pools (DB_nK_CACHE_SIZE)

Oracle first subtracts the total value of all manually size memory components from the sga_target value and then allocates the remainder of the memory amount the auto-tuned memory components.

Oracle stores the last ASMM configuration in the spfile so that they are ready for next time you start the instance. MMAN (memory manger) works with ASMM to determine the memory requirements, MMAN requires that the STATISICS_LEVEL be set to at least typical, otherwise MMAN will not be able to obtain the information it needs.

SGA components are sized in granules (not the log buffer), a granule is a contiguous area of memory. The granule size is platform specific normally they are 4MB if SGA is less than 1GB or 16MB if it is larger (8MB in windows). The granules allocated will always be rounded up to the next granule.

Database Buffer Cache

Every database buffer will be in one of three states:

• • Pinned means the buffer is in use, a process is working on the block in the buffer, no other process will be able to access it as a lock is placed on the buffer.

  • Dirty buffer is a buffer containing a block whose image in the buffer is not the same as the image on the disk, you can update the block (pin the block) but you cannot read in another block until it as been written to disk which is called cleaned.

  • Free buffer basically means the block is used or clean.

LRU (least recently used) is a list of every buffer address sorted by when the buffer was last accessed this is achieved by using a touch count algorithm on the block, when requiring a free buffer it searches from the bottom of the list until it find one. This means it keeps the most popular data in the buffer. There is a second list called the checkpoint queue, this is a list of all the dirty buffers waiting to be written to disk, dirty buffers are added to the list when searching for a free buffer if it finds a dirty buffer it is added, so the checkpoint queue is a list of all dirty buffers not recently used (when searched it never gets to the top of the LRU list). From time to time the DBWn copies the buffers to disk from the checkpoint queue making then available for use again. The checkpoint queue gets clean because of one or two reasons, server process may take too long to find a free buffer (more than 3 seconds) and the checkpoint queue becomes to long.

Remember all buffers get written to disk when

• • shutting down

  • running the command alter system checkpoint

The database buffer cache has three pools:

• • Default sized by DEFAULT_CACHE_SIZE and is required,

  • Keep pool sized by DB_KEEP_CACHE_SIZE and used to keep buffers in memory mainly the most used buffers.

  • Recycle pool sized by DB_RECYCLE_CACHE_SIZE used when you know that buffers will not be used again so best to get them flushed and get the space back.

You can use non-standard block size pools but this is only used when you want to change block sizes within a tablespace. It can also be used when you might be storing multimedia items that require large block sizes. It is only supported by Oracle for transporting tablespaces not performance.

The log buffer is a very short term staging area for all changes applied to blocks in the database buffer cache by server processes before they are streamed to disk by the LGWR process. The log buffer is sized by the LOG_BUFFER parameter, 1 cpu = 256k, 2 = 512K and so on.

Shared Pool

Shared Pool is sized by the SHARED_POOL_SIZE, there are 595 components in the shared pool listed are the 6 most important ones, each is dynamically adjust by Oracle.

• • sql area and library cache - cached recently used sql statements, both text of the statement and the parsed forms with an execution plan

  • row cache - this cache (data dictionary) is made up of data dictionary info (table structures, users, constraints, grants and more) that are being parsed to sql statements

  • ASH (Active Session History) - used for performance diagnosis, regularly flushed to AWR in the sysaux tablespace

  • Various PL/SQL areas - PL/SQL code including triggers is loaded into the shared pool from the data dictionary

  • flashback generation buffers - memory used for flashback database buffers before they are flushed to the flashback logs

Large Pool

The large pool is to reduce the strain on the shared pool, the following will use the large pool if configured:

• • Shared server processes the UGA’s will be stored here

  • Parallel execution servers if you have enabled parallel query, the parallel servers communicate via the large pool

  • I/O slave processes if you have enabled db writer slaves or tape i/o slaves.

  • RMAN the rman channel processes use the large pool for caching buffers during backup and restore operations

Java Pool

Java stored procedures are loaded from the data dictionary into the shared pool, the purpose of the Java pool is to provide room for the runtime memory structures used by a Java application. It is controlled by three instance parameters:

• • Java_pool_size dynamically create the java pool

  • Java_max_sessionspace_size maximum amount of space aloud for any one session (static)

  • Java_soft_sessionspace_limit a message is written to the trace file when the limit is breached, can help with monitoring. (static)

Streams Pool

The streams pool is not covered in OCP exam, but they can be used to propagate redo logs changes to other databases. As soon as i get any experience with Streams i will update this section.