Oracle 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

SGA_MAX_SIZE

Oracle will limit the SGA_TARGET value up until the SGA_MAX_SIZE, however you need to restart the instance for the change to take affect.

SGA_TARGET

Oracle will only use up to the SGA_TARGET you have to manually adjust the SGA_TARGET size to increase the SGA memory, however the SGA_TARGET cannot exceed the SGA_MAX_SIZE.

Note that Oracle will not automatically adjust the SGA_TARGET up to the SGA_MAX_SIZE this is manual process, however you do not need to restart the instance.

Oracle requires a number of pools to work

Required pools	shared pool, redo buffer, buffer cache
Not Required Pools	large pool, java pool, streams pool, buffer cache keep pool, buffer cache recycle pool, db buffer cache nK block size pool

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.

SGA
Change the SGA	alter system set sga_max_size = 4000M; alter system set sga_target = 2000M; Note: sga_target should never exceed the sga_max_size value
Size the SGA	select (select sum(value) from v$sga) - (select current_size from v$sga_dynamic_free_memory) from dual;
SGA size	select * from v$sga; select sum(value) from v$sga; select sum(bytes) from v$sgastat; select sum(current_size) from v$sga_dynamic_components; select * from v$sga_dynamic_free_memory;
SGA memory Components
Change a automatic memory component	alter system set large_pool_size = 800M;
Change a manual memory component	alter system set db_keep_cache_size = 20M;
Display memory component configuration	show parameter large_pool_size; show parameter db_keep_cache_size;
Display the automatic memory components	select component, current_size from v$sga_dynamic_components; select pool, sum(bytes)/1024/1024 from v$sgastat group by pool;
Flush specific components	alter system flush buffer_cache; alter system flush shared_pool;
Monitoring ASSM	select component, oper_type, oper_mode, initial_size/1024/1024 "Initial", TARGET_SIZE/1024/1024 "Target", FINAL_SIZE/1024/1024 "Final", status from v$sga_resize_ops order by component; select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE", last_oper_type "TYPE" from v$sga_dynamic_components order by component;
Free space within the shared pool	select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10trunc(KSMCHSIZ/10) "From", count() "Count" , max(KSMCHSIZ) "Biggest", trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ<140 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 10trunc(KSMCHSIZ/10) UNION ALL select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX, 20trunc(KSMCHSIZ/20) , count() , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 140 and 267 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 20trunc(KSMCHSIZ/20) UNION ALL select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50trunc(KSMCHSIZ/50) , count() , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 268 and 523 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 50trunc(KSMCHSIZ/50) UNION ALL select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500trunc(KSMCHSIZ/500) , count() , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ between 524 and 4107 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 500trunc(KSMCHSIZ/500) UNION ALL select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000trunc(KSMCHSIZ/1000) , count() , max(KSMCHSIZ) , trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total" from x$ksmsp where KSMCHSIZ >= 4108 and KSMCHCLS='free' group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
Useful Views
V$SGA	displays summary information about the system global area (SGA).
V$SGAINFO	displays size information about the SGA, including the sizes of different SGA components, the granule size, and free memory.
V$SGASTAT	displays detailed information on the system global area (SGA).
V$SGA_DYNAMIC_COMPONENTS	displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operations since instance startup. All sizes are expressed in bytes.

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.

Determine the granule size

select * from v$sgainfo where name = 'Granule Size';

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.

Set a table to use the recycle pool	alter table emp(storage buffer_pool recycle); (need to check)
Set a index to use the keep pool	alter index emp_name_idx(storage buffer_pool keep); (need to check)
Buffer cache size factoring	select name, size_for_estimate, size_factor, estd_physical_reads from v$db_cache_advice;
Display the 20 hottest blocks	select tch, file#, dbablk, case when obj = 4294967295 then 'rbs/compat segment' else (select max( '('\|\|object_type\|\|') ' \|\| owner \|\| '.' \|\| object_name ) \|\| decode( count(*), 1, '', ' maybe!' ) from dba_objects where data_object_id = X.OBJ ) end what from ( select tch, file#, dbablk, obj from x$bh where state <> 0 order by tch desc ) x where rownum <= 20 /

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

shared pool size factoring

select shared_pool_size_for_estimate "size", shared_pool_size_factor "factor", estd_lc_time_saved "saving" from v$shared_pool_advice;

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

Large pool configuration	show parameter large_pool_size;
Large Pool Size	select * from v$sgastat where pool='large pool';

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)

Java pool configuration	show parameter java_pool_size;
Java Pool Size	select * from v$sgastat where pool='java pool';

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.