Tips_34 Monitoring and Managing Memory_ASMM

10g:

■■The System Global Area(SGA)

SGA_MAX_SIZE:The actual SGA size will always be equal to or less than this parameter.

SQL> select sum(bytes)/(1024*1024) size_in_mb from v$sgastat;

SGA components are, with the exception of the log buffer, sized in granules

A granule is an area of contiguous memory.

Linx:

Typical granule sizes are 4MB if the total SGA is no bigger than 1000MB or 16MB if it is larger.

Windows:

the granule size is 8MB for an SGA greater than 1GB

***

You can query the V$SGAINFO view to see the granule size that is being used by an instance.

SQL> select name,bytes,resizeable from v$sgainfo;

■The Shared Pool

SHARED_POOL_SIZE

***

If you find yourself with an instance that cannot start (as in this example),use the CREATE PFILE FROM SPFILE command to generate a text parameterfile, 

edit it to change the parameter that is preventing the startup, and then use CREATE SPFILE FROM PFILE to generate an spfile that will work.

SQL> select pool,name,bytes from v$sgastat;

the shared pool consists of thirty-six components.

The more important ones:

• The sql area and the library cache:This is a cache of recently executed SQL statements, both the text of the statements and the parsed forms with an execution plan.

• The row cache(the data dictionary cache):is made up of data dictionary information (table structures, users, constraints, grants, and more) that are being used to parse SQL statements.

• The ASH (Active Session History) buffers:This is information regarding recent activity by users. Used for performance diagnosis, it is regularly flushed to the Automatic Workload Repository in the SYSAUX tablespace.

• Various PL/SQL areas:PL/SQL code, including triggers, is loaded into the shared pool from the data dictionary for execution.

• The flashback generation buffers:This is memory used for the flashback database buffers, before they are flushed to the flashback logs.

All the shared pool components are sized automatically by the instance; you have no control over the size of any one component.

An oversized shared pool is also bad for performance. 

To determine how large the shared pool should be, use the Shared Pool Advisor.

To enable the advisor, set the instance parameter STATISTICS_LEVEL to TYPICAL,which is the default, and query the V$SHARED_POOL_ADVICE view.

■The Database Buffer Cache

■■ASMM(Automatic Shared Memory Management)

SGA_TARGET + STATISTICS_LEVEL

****

To disable ASMM, set the SGA_TARGET to zero

1.Four memory structures that are often referred to as auto-tune components are managed by Automatic Shared Memory Management:

• Database buffer cache default pool, DB_CACHE_SIZE

• Shared pool, SHARED_POOL_SIZE

• Large pool, LARGE_POOL_SIZE

• Java pool, JAVA_POOL_SIZE

If the parameters controlling these structures are not set or are set to zero, then Oracle has complete freedom to resize them, dynamically, up or down according to demand. 

If they have been set, the settings given specify a minimum level below which automatic management will not reduce them.

2.These other structures must always be sized manually:

• Database buffer cache keep pool, DB_KEEP_CACHE_SIZE

• Database buffer cache recycle pool, DB_RECYCLE_POOL_SIZE

• Database buffer cache nonstandard block size pools, DB_nK_CACHE_SIZE

• Streams pool, STREAMS_POOL_SIZE（in 11g been chaged）

• Log buffer, LOG_BUFFER

3.

・To enable ASMM, set the instance parameter SGA_TARGET. This specifies a total size for the SGA.

・If the instance was started with a dynamic spfile, then Oracle will write to the file the current settings of the parameters for the four automatically managed components.

・If the instance was started with a static pfile, then automatic memory management will still work, but the current settings will be lost at shutdown,and the self-tuning process will have to start from the beginning at the next startup.

4.The Memory Manager（MMAN）. 

MMAN observes the system and the workload to determine the ideal memory distribution. The checks are made every few minutes.

***

ASMM requires that the STATISTICS_LEVEL instance parameter to be on TYPICAL(the default), or ALL.Without this,the information MMAN needs will not be gathered.

・STATISTICS_LEVEL:

 BASIC: No advisories or statistics are collected.

 TYPICAL:(default ) The following advisories or statistics are collected:

   * Buffer cache advisory

   * MTTR advisory

   * Shared Pool sizing advisory

   * Segment level statistics

   * PGA target advisory

   * Timed statistics

 ALL: All of the preceding advisories or statistics are collected, plus the following:

   * Timed operating system statistics

   * Row source execution statistics

***

The SGA_TARGET is the total size of the whole SGA, including any manually managed components—not just the total size of the four automatically managed components.

5.SGA_MAX_SIZE

is the largest that the SGA can grow to.

The actual allocated SGA is specified by the SGA_TARGET.

****

To disable ASMM, set the SGA_TARGET to zero

■Using ASMM 例:

1. Connect to your database with SQL*Plus as user SYS with the SYSDBA

privilege.

SQL> connect / as sysdba;

2. Confirm that you are using an spfile.

SQL> show parameters spfile;

If this does not return a filename, convert your instance to use an spfile.

SQL> create spfile from pfile;

SQL> shutdown abort;

SQL> startup;

3. Remove the SGA memory settings from the spfile for the automatically

managed components.

SQL> alter system reset shared_pool_size scope=spfile sid='*';

SQL> alter system reset large_pool_size scope=spfile sid='*';

SQL> alter system reset java_pool_size scope=spfile sid='*';

SQL> alter system reset db_cache_size scope=spfile sid='*';

4. Determine the current size of the SGA.

SQL> select sum(bytes) from v$sgastat;

5. Set the SGA_TARGET to a value similar to that returned by the query in Step 4.

SQL> alter system set sga_target=100m scope=spfile sid='*';

6. Restart the instance, and again check the size of the SGA.

7. Display the sizes of the various SGA components.

SQL> show parameters db_cache_size;

SQL> show parameters large_pool_size;

■■The Program Global Area(PGA)

The PGA stores data and control information for each session and is used only by the server process servicing the session.

The information in the PGA includes bind information; session variables; stack space; cursors; and(perhaps most important) the sort space used for sorting, joining, and aggregating rows.

When using shared server, a large part of the PGA (the part known as the UGA),goes into the large pool (or the shared pool, if the large pool has not been configured).

■

• WORKAREA_SIZE_POLICY:default AUTO ,MANUAL(in release 9i) disable automatic PGA management(SORT_AREA_SIZE,HASH_AREA_SIZE,BITMAPMERGE_AREA_SIZE,CREATE_BITMAP_AREA_SIZE)

• PGA_AGGREGATE_TARGET:defaults to 20 percent of the SGA size 

  Ensure that all sessions have as much memory as they need, without taking the total allocation over the limit

■

To monitor PGA usage, there are two critical views.

①V$PGASTAT gives an overall picture of PGA usage:

SQL> select * from v$pgastat;

②V$PGA_TARGET_ADVICE makes predictions regarding the effect of changing the target:

SQL> select pga_target_for_estimate "size",

     pga_target_factor "factor",

     estd_extra_bytes_rw/1000000 "extra Mb r/w",

     estd_overalloc_count "over alloctions" 

     from v$pga_target_advice;

*****************************************************

11g:new feature:

■AMM(Automatic Memory Management)

・MEMORY_TARGET +STATISTICS_LEVEL

・MMAN

Automatic memory management automates memory allocation of each SGA component and aggregated PGA.