Automatic memory management

Starting with the Oracle database 11g version, there is an useful and Oracle recommended option to manage automatically the memory. When you modify some parameters of the instance, it can be implemented easily.

With this option, you just have to analyze the memory size that you want to establish and Oracle will administer its structures automatically. Its name is Automatic Memory Management (AMM).

ASMM, the predecessor

Before the 11g version, Oracle had implemented the Automatic Shared Memory Management (ASMM) in the 10g version. This consisted to assign a value to two parameters of the database’s instance, to facilitate the job to Oracle to control the other memory’s parameters like the Shared Pool or the Database Buffer Cache automatically.

Both parameters are the sga_target and sga_max_size, they control the initial limit for the memory usage in the SGA and maximum memory usage respectively.

Additional and separately, you must had to modify the memory space usage by the Program Global Area (PGA), it means, the memory that is distributed between the connected sessions in the database’s instance. It is configured with the parameters pga_aggregate_target and pga_aggregate_limit that act similar to the previous parameters from the previous paragraph.

The new AMM

Now with the Automatic Memory Management (AMM) option, you can configure the whole memory used by the database’s instance as one entity, the PGA is integrated in this memory configuration:

To implement the automatic memory management, you must follow the following enlisted steps:

1. To verify that the lock_sga doesn’t have the TRUE value, because it blocks the AMM:

SQL> show parameter lock_sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE

2. To determine the size of the current memory by the sga_target parameter:

SQL> show parameter sga_target

NAME                          TYPE        VALUE
----------------------------- ----------- ------------------------------
sga_target                    big integer 1G

3. To verify the real size used by the PGA through the V$PGASTAT view:

SQL> select value/1024/1024/1024 used_pga
  2    from v$pgastat
  3   where name = 'maximum PGA allocated';

USED_PGA
 ----------
 .2336092

1 row selected.

4. To calculate the total memory that will be administered by the AMM. The memory_target must be configured based in the following formula:

memory_target = sga_target + max (used_pga, pga_aggregate_target)

In the example that I’m showing:

memory_target = 1GB + max (.233GB, 0.100GB)
memory_target = 1GB + .233GB
memory_target = 1.233GB    <- This is the objective size.

5. To change the parameters that controls the memory. The memory_max_target parameter must be configured to the maximum memory usage, can be the same size as the memory_target or more:

alter system set memory_max_target=cantidadM scope=spfile;

6. To verify the parameters that are administered automatically by the AMM:

show parameter db_cache_size
show parameter java_pool_size
show parameter large_pool_size
show parameter memory_max_target
show parameter memory_target
show parameter pga_aggregate_target
show parameter sga_max_size
show parameter sga_target
show parameter shared_pool_size
show parameter streams_pool_size

or with the following query:

  select name,
         value / 1024 / 1024 total_mb
    from v$parameter
   where name in ('db_cache_size',
                  'java_pool_size',
                  'large_pool_size',
                  'memory_max_target',
                  'memory_target',
                  'pga_aggregate_target',
                  'sga_max_size',
                  'sga_target',
                  'shared_pool_size',
                  'streams_pool_size')
order by name;

7. To backup the spfile to a pfile, in case that something wrong happens:

create pfile from spfile;

8. To configure the AMM changing the memory_target and memory_max_target parameters with the pre-calculated value and the rest  to zero (0).

If you wish that some of these parameters have a minimum value, then you must configure it with the desired value. In the next example, I configured a maximum size of 2.5GB and 2GB for the memory size:

alter system set        db_cache_size=0     scope=spfile;
alter system set       java_pool_size=0     scope=spfile;
alter system set      large_pool_size=0     scope=spfile;
alter system set    memory_max_target=2560m scope=spfile;
alter system set        memory_target=2048m scope=spfile;
alter system set pga_aggregate_target=0     scope=spfile;
alter system set         sga_max_size=0     scope=spfile;
alter system set           sga_target=0     scope=spfile;
alter system set     shared_pool_size=0     scope=spfile;
alter system set    streams_pool_size=0     scope=spfile;

9. Once the parameters were changed, then you must reboot your instance:

shutdown immediate
startup

and you can confirm that the memory parameters taked the value, in the next example, you can observed that Oracle already assign a value for the  sga_max_size parameter automatically:

NAME                              TOTAL_MB
------------------------------ -----------
db_cache_size                            0
java_pool_size                           0
large_pool_size                          0
memory_max_target                 2,560
memory_target                     2,048
pga_aggregate_target                     0
sga_max_size                      1,228
sga_target                               0
shared_pool_size                         0
streams_pool_size                        0

When the instance is up and running, you can monitor the memory usage as the time pass with the following queries:

Current memory components usage in MB

  select component,
         min_size / 1024 / 1024            min_size,
         current_size / 1024 / 1024        current_size,
         max_size / 1024 / 1024            max_size,
         user_specified_size / 1024 / 1024 user_specified_size,
         granule_size / 1024 / 1024        granule_size
    from v$memory_dynamic_components
order by component;

Example of how appears after the parameters change, this will appear different when the database and its instance are in use:

COMPONENT                  MIN_SIZE   CURRENT_SIZE   MAX_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE
-------------------------- ---------- ------------ ---------- ------------------- ------------
ASM Buffer Cache                    0            0          0                   0            4
DEFAULT 16K buffer cache            0            0          0                   0            4
DEFAULT 2K buffer cache             0            0          0                   0            4
DEFAULT 32K buffer cache            0            0          0                   0            4
DEFAULT 4K buffer cache             0            0          0                   0            4
DEFAULT 8K buffer cache             0            0          0                   0            4
DEFAULT buffer cache              912          912        912                   0            4
KEEP buffer cache                   0            0          0                   0            4
PGA Target                        820          820        820                   0            4
RECYCLE buffer cache                0            0          0                   0            4
SGA Target                       1228         1228       1228                   0            4
Shared IO Pool                      0            0          0                   0            4
java pool                           4            4          4                   0            4
large pool                          0            4          4                   0            4
shared pool                       296          296        296                   0            4
streams pool                        0            0          0                   0            4

Memory size recommendation

  select memory_size,
         memory_size_factor * 100  memory_size_factor,
         estd_db_time,
         estd_db_time_factor * 100 estd_db_time_factor
    from v$memory_target_advice
order by memory_size;

With this query you can see how efficient had been and can be the memory usage based in its current size (100%) and an estimate from the 25% through the 200% of its size:

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR
----------- ------------------ ------------ -------------------
        512                 25           13              100.02
       1024                 50           13                 100
       1536                 75           13                 100
      2048               100          13                100
       2560                125           13                 100
       3072                150           13                 100
       3584                175           13                 100
       4096                200           13                 100

Where:

MEMORY_SIZE shows different memory sizes around the current size in MB.

MEMORY_SIZE_FACTOR shows the percenteage factor of each memory size. In blacks is that one that I configured, that’s why appears with the 100%, here you can see the different percenteages that you can have around the current size.

ESTD_DB_TIME it’s an estimate in answer time when the information is queried based on the memory size. This column is important because you’re searching that this number is the lowest. So if I have a case where the 150% of the memory offers me a lowest number, it will indicate me that I must raised the memory size to that value. In the example, all the times are the same because I just changed the parameters.

ESTD_DB_TIME_FACTOR it’s the percenteage factor for the ESTD_DB_TIME column.

System Global Area (SGA) size recommendation

Similar to the previous query, you can have a recommendation based on the SGA size, this can be reviewed with the following query:

   select sga_size,
          sga_size_factor * 100     sga_size_factor,
          estd_db_time,
          estd_db_time_factor * 100 estd_db_time_factor,
          estd_physical_reads
     from v$sga_target_advice
 order by sga_size;

Here you can see what size must have the SGA to improve its answer time. In the next example, appears that with a 50% of the current size we have an acceptable answering time:

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       307              25          151              431.43               17298
       614              50           35                 100               16750
       921              75           35                 100               16750
      1228             100           35                 100               16750
      1535             125           35                 100               16750
      1842             150           35                 100               16750
      2149             175           35                 100               16750
      2456             200           35                 100               16750

Current SGA information per component

Finally, a classic query to see the current size for each component of the SGA:

   select name,
          bytes/1024/1024 current_size,
          resizeable
     from v$sgainfo
 order by name;

In the following example, you can see how some of the components had changed its size after Oracle is automatically administering the memory:

NAME                                CURRENT_SIZE RES
----------------------------------- ------------ ---
Buffer Cache Size                            912 Yes
Fixed SGA Size                                 2 No
Free SGA Memory Available                      0
Granule Size                                   4 No
Java Pool Size                                 4 Yes
Large Pool Size                                4 Yes
Maximum SGA Size                           1,223 No
Redo Buffers                                   4 No
Shared IO Pool Size                            0 Yes
Shared Pool Size                             296 Yes
Startup overhead in Shared Pool               84 No
Streams Pool Size                              0 Yes

Please, leave a comment if this post was useful for you or if you have any doubt about the contents, I will OrAnswer you as soon as possible.