3.3. Memoria Compartida

Oracle Automatic Shared Memory Management

Oracle 11g Grid & Real Application Clusters by Rampant TechPress is written by four of the top Oracle database experts (Steve Karam, Bryan Jones, Mike Ault and Madhu Tumma). The following is an excerpt the book.

Automatic Shared Memory Management

Automatic Shared Memory Management (ASMM) adjusts the sizes of the SGA components on the fly as the workload changes. Automatic Shared Memory Management (ASMM), a 10g and 11g feature, is meant to simplify SGA management. Do not confuse ASMM with AMM (Automatic Memory Management). ASMM was introduced in Oracle 10g. AMM is new in 11g and will be addressed in this chapter.

The DBA specifies the amount of memory available to an instance via the sga_target parameter. The Oracle database periodically redistributes memory between the components according to workload requirements. This solves the allocation issues that are normally faced when using the manual method. Under-sizing memory settings can lead to poor performance and out-of memory errors (ORA-4031).

If a non-zero value is specified for sga_target, the following six memory pools are automatically sized by Oracle:

Oracle Automatic Shared Memory Management By Burleson Consulting

Notes:

Oracle has always tried to provide advisory tools for the Oracle DBA who wants to monitor and re-size their data buffers (db_block_buffers, db_cache_size):

AEnabling Automatic Shared Memory Management (in 10g ONLY)

The Oracle Automatic Shared Memory Management is a feature that automatically readjusts the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) based on existing workloads. Oracle Automatic Shared Memory Management is enabled by setting:

Oracle10g has introduced special double underscore hidden parameter to control ASMM. Once you see these, AMM is being used.:

Once enabled, Oracle Automatic Shared Memory Management will morph the pool areas within the confines of sga_max_size.

When using AMM you have to consider the interaction of these parameters:

memory_target sga_target sga_max_size pga_aggregate_target Behavior

memory_target sga_target sga_max_size pga_aggregate_target Behavior
non-zero 0 0 60% of memory_target to SGA, 40% to PGA
non-zero non-zero non-zero Minimum values
non-zero non-zero un-set pga_aggregate_target = memory_target - sga_target
non-zero un-set un-set sga_target is set to memory_target - pga_aggregate_target

AMM and the memory_target parameter

In Oracle 11g and beyond, automatic memory management is enabled by using the memory_target and memory_max_target initialization parameters. The memory_target parameter specifies the amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. The memory_max_target AMM parameter specifies the max size that memory_target may take. The Oracle documentation notes that the memory_target parameter specifies a target value for system-wide RAM memory, both PGA and SGA RAM:

'MEMORY_TARGET specifies the Oracle system-wide usable memory.

The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.'

Disabling Automatic Shared Memory Management

Simply re-set sga_target to zero. Be aware that the data buffer cache (db_cache_size) will take-on the current value the spfile.

Using Automatic Shared Memory Management

You need to note that Automatic Shared Memory Management does not change the multiple block sizes and the KEEP pool. In an article titled Automatic Shared Memory Management we see:

Some pools in SGA are not subject to dynamic resizing, and must be specified explicitly. Notable among them are the buffer pools for nonstandard block sizes and the non-default ones for KEEP or RECYCLE. If your database has a block size of 8K, and you want to configure 2K, 4K, 16K, and 32K block-size pools, you must set them manually.

Their sizes will remain constant; they will not shrink or expand based on load. You should consider this factor when using multiple-size buffer, KEEP, and RECYCLE pools.

In addition, log buffer is not subject to the memory adjustment—the value set in the parameter log_buffer is constant, regardless of the workload. ( In 10g, a new type of pool can also be defined in the SGA: Streams pool, set with parameter streams_pool_size. This pool is also not subject to automatic memory tuning.)

the bestselling book 'Oracle 10g New Features', we see how Oracle monitors the pool workloads::

A new background process named Memory Manager (MMAN) manages the automatic shared memory. MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations.

Monitoring Automatic Shared Memory Management

MOSC Note:295626.1, How To Use Automatic Shared Memory Management (ASMM) In Oracle10g, has some script for monitoring Oracle Automatic Shared Memory Management:

SELECT 
    component, 
    oper_type, 
    oper_mode, 
    initial_size/1024/1024 'Initial', 
    TARGET_SIZE/1024/1024  'Target', 
    FINAL_SIZE/1024/1024   'Final', 
    status 
  
    v$sga_resize_ops;

COMPONENT                      OPER_TYPE     OPER_MODE INITIAL    TARGET     FINAL      STATUS
 ------------------------------ ------------- --------- ---------- ---------- ---------- ---------
 DEFAULT buffer cache           SHRINK        MANUAL    160         148       148        COMPLETE
 streams pool                   GROW          MANUAL      0          12        12        COMPLETE

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' 
  
    v$sga_dynamic_components;

COMPONENT                      CURRENT_SIZE MIN_SIZE   USER_SPECIFIED_SIZE TYPE
 ------------------------------ ------------ ---------- ------------------- -------------
 shared pool                    80           80         80                  STATIC
 large pool                      8            8          8                  STATIC
 java pool 4                     8           48         48                  STATIC
 streams pool                   12            0         12                  GROW
 DEFAULT buffer cache           48           24         24                  SHRINK
 KEEP buffer cache               0            0          0                  STATIC
 RECYCLE buffer cache            0            0          0                  STATIC
 DEFAULT 2K buffer cache         0            0          0                  STATIC
 DEFAULT 4K buffer cache         0            0          0                  STATIC
 DEFAULT 8K buffer cache         0            0          0                  STATIC
 DEFAULT 16K buffer cache        0            0          0                  STATIC
 DEFAULT 32K buffer cache        0            0          0                  STATIC
 OSM Buffer Cache                0            0         24                  STATIC

Scripts for monitoring the shared pool for free RAM chunks. You can also issue these queries to see free RAM in Oracle10g:

SELECT *  X$KSMLRU WHERE ksmlrsiz >

Oracle also published a script using x$ksmsp to give a listing of the RAM heap to see how free space is allocated within the shared pool, and the sizes of available chunks on the freelist for he shared pool, RAM.

SELECT 
    '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 
    10*TRUNC(KSMCHSIZ/10) '', 
    COUNT(*) 'COUNT' ,
    MAX(KSMCHSIZ) 'Biggest', 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total' 
    x$ksmsp
 WHERE 
    KSMCHSIZ < 140 
 AND
    KSMCHCLS='free'
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 10*TRUNC(KSMCHSIZ/10) 
 UNION ALL 

 SELECT 
    '1 (140-267)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX,
    20*TRUNC(KSMCHSIZ/20) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total' 
    x$ksmsp
 WHERE 
    KSMCHSIZ between 140 and 267 
 and 
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 20*TRUNC(KSMCHSIZ/20) 
 UNION ALL 
 SELECT 
    '2 (268-523)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    50*TRUNC(KSMCHSIZ/50) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total' 
    x$ksmsp 
 WHERE 
    KSMCHSIZ between 268 and 523 
 AND
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 50*TRUNC(KSMCHSIZ/50) 
 UNION ALL 
 SELECT 
    '3-5 (524-4107)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    500*TRUNC(KSMCHSIZ/500) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total' 
  
    x$ksmsp 
 WHERE 
    KSMCHSIZ between 524 and 4107 
 and 
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 500*TRUNC(KSMCHSIZ/500) 
 UNION ALL 
 SELECT 
    '6+ (4108+)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    1000*TRUNC(KSMCHSIZ/1000) , 
    COUNT(*), 
    MAX(KSMCHSIZ), 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize',
    TRUNC(SUM(KSMCHSIZ)) 'Total',
    x$ksmsp 
 WHERE 
    KSMCHSIZ >= 4108 
 AND
    KSMCHCLS = 'free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 1000*TRUNC(KSMCHSIZ/1000);

Oracle also published a script using x$ksmsp to give a listing of the RAM heap to see how free space is allocated within the shared pool, and the sizes of available chunks on the freelist for he shared pool, RAM.

SELECT 
    '0 ( < 140)' BUCKET, KSMCHCLS, KSMCHIDX, 
    10 * TRUNC(KSMCHSIZ/10) '', 
    COUNT(*) 'COUNT' , 
    MAX(KSMCHSIZ) 'Biggest', 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total', 
    x$ksmsp 
 WHERE 
    KSMCHSIZ < 140 
 and 
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 10*TRUNC(KSMCHSIZ/10) 
 UNION ALL 
 SELECT 
    '1 (140-267)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX,
    20*TRUNC(KSMCHSIZ/20) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total',
    x$ksmsp 
 WHERE 
    KSMCHSIZ between 140 and 267 
 and
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 20*TRUNC(KSMCHSIZ/20) 
 UNION ALL 
 SELECT 
    '2 (268-523)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    50*TRUNC(KSMCHSIZ/50), 
    COUNT(*), 
    MAX(KSMCHSIZ),
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total',
    x$ksmsp 
 WHERE 
    KSMCHSIZ between 268 and 523 
 and 
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 50*TRUNC(KSMCHSIZ/50) 
 UNION ALL 
 SELECT 
    '3-5 (524-4107)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    500*TRUNC(KSMCHSIZ/500) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total' 
  
    x$ksmsp 
 WHERE 
    KSMCHSIZ between 524 and 4107
 and 
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 500*TRUNC(KSMCHSIZ/500) 
 UNION ALL 
 SELECT 
    '6+ (4108+)' BUCKET, 
    KSMCHCLS, 
    KSMCHIDX, 
    1000*TRUNC(KSMCHSIZ/1000) , 
    COUNT(*) , 
    MAX(KSMCHSIZ) , 
    TRUNC(AVG(KSMCHSIZ)) 'AvgSize', 
    TRUNC(SUM(KSMCHSIZ)) 'Total',
    x$ksmsp 
 WHERE 
    KSMCHSIZ >= 4108 
 AND
    KSMCHCLS='free' 
 GROUP BY 
    KSMCHCLS, KSMCHIDX, 1000*TRUNC(KSMCHSIZ/1000)
  • SELECT SUM(value) v$sga;
  • SELECT SUM(bytes) v$sgastat;
  • SELECT SUM(current_size) v$sga_dynamic_components;
  • SELECT * v$sga_dynamic_free_memory;

Automatic Shared Memory Management Internals

With the advent of the advisory utilities in Oracle9i (v$db_cache_advice, v$shared_pool_advice, and v$pga_target_advice, we see how Oracle plots marginal benefits different pool sizes:

Robert Freeman notes the default behavior with a blank init.ora file:

I did shutdown my local 10g database and brought it up with a blank init.ora (only had the db_name parameter in it). The actual default in XP 10g is strong>db_cache_size=48m, shared_pool_size=32m<.

MOSC Note:257643.1, Oracle Database 10g Automated SGA Memory Tuning gives hints about how MMAN determines current workloads. The documentation shows that Oracle uses the memory advisories Oracle9i and applies heuristics (rules of thumb) to determine the best shift in RAM pool sizes. These heuristics consist of hypothesis testing with 'what if' scenarios, computing the ratio of the marginal reduction in physical disk reads, and choosing the size with the greatest overall marginal benefit:

The SGA Memory Broker observes the system and workload in order to determine the ideal distribution of memory. It is never complacent and performs this check every few minutes so that memory can always be present WHERE needed.

Based on workload information, automatic shared memory tuning:

  • Captures statistics periodically in the background
  • Uses the different memory advisories
  • Performs ?what-if? analyses to determine best distribution of memory
  • Moves memory to WHERE it is most needed
  • Has no need to configure parameters for the worst-case scenario
  • Resurrects component sizes last shutdown if SPFILE is used

Automatic Shared Memory Management Errors

ASMM can precipitate these errors:

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0 

alter system set sga_target = 330M
 *
 ERROR at line 1:
 ORA-02097: parameter cannot be modified because specified value is invalid
 ORA-00827: could not shrink sga_target to specified value
alter system set sga_target=160M; 
 alter system set sga_target=160M 
 * ERROR at line 1: 
 ORA-02097: parameter cannot be modified because specified value is invalid 
 ORA-00823: Specified value of sga_target greater than sga_max_size 

SQL> startup

ORA-00824: cannot set sga_target due to existing internal settings
<

Cause: If you enable automatic SGA Management by setting SGA_Target >0 and

if you are using db_block_buffers(Obsolete parameter) in your init.ora.

Startup of Database fails with ORA-00824 Error

Potential issues with ASMM

The Oracle 10g Automatic Storage Memory Management (ASMM) feature (the default on Oracle10g) should anticipate high updates and allocate additional data buffers during high update periods.

For example, here is an actual output an Oracle10g database WHERE it appears that ASMM is not allocating enough free blocks to accommodate concurrent updates:

STATUS  NUMBER_BUFFERS 
------- -------------- 
cr                 616 
free                 1  
xcur             14790

Here we see the double-underscore ASMM hidden parms:

316     6 __db_cache_size                       130,023,424
  96     6 __java_pool_size                       12,582,912
  94     6 __large_pool_size                       4,194,304
  92     6 __shared_pool_size                    142,606,336