Automatic Shared Memory Management in Oracle 10g

Oracle instance in memory consists of two major areas SGA (system/shared global area)and PGA (program/private global area). The SGA is shared by all sessions and consists of a few pools for different purposes. A few of them are as follows:

1. DEFAULT buffer cache :- used to store oracle data blocks when they are read or updated.

2. DEFAULT nK buffer cache :- used to store oracle data blocks having different size then the default block size (db_block_size) - (non -ASMM)

3. KEEP buffer cache :- used to store oracle data blocks from the objects which are not supposed to age out from memory. - (non -ASMM)

4. RECYCLE buffer cache :- used to store oracle data blocks from the objects which are not supposed to be kept in the memory. - (non -ASMM)

5. log buffer cache :- used to store redo entries to reconstruct the operations in case of an instance crash. - (non -ASMM)

6. shared pool :- used to parse and store session queries, define execution plans for queries etc.

7. large pool :- used for backup/recovery operations and batch job processing etc.

8. java pool :- All session's java related activities are done here.

9. streams pool :- used for oracle streams.

Sizing these pools manually in the SGA is a great pain and it is almost impossible to use all available memory efficiently to different pools. Lets take a scenario where the database is being used for OLTP application in the daytime and there are some huge batch jobs scheduled to run every night. We have 1G of memory available for SGA out of which we have given 400m to the DB Buffer Cache, 300m to Shared Pool, 100m to Large Pool and rest of the memory i.e. 200m to other pools in the SGA.

In the daytime the DB Buffer Cache is being used extensively for OLTP transactions and a very little of Large Pool say 5 to 10 megabytes. Keeping this in view, even when DB Buffer Cache is in contention and 400m is not sufficient enough for it we are wasting a lot of memory in Large Pool where nothing is being happening.

While during nights when there is no OLTP activity and we need more memory for Large Pool, a lot of memory is being wasted in the DB Buffer cache.

Having this problem in hand now lets go through the ASMM (Automatic Shared Memory Management) feature introduced in Oracle 10g and see how it can help us with our problem.

ASMM when switched on, it controls the sizes of the certain components in the SGA by making sure they get the memory they need and it does that by shrinking the components which are not using all of memory allocated to them and growing the ones which need more then the allocated memory. ASMM adopts to the workload changes and maximize the utilization of the memory. This happens with the help of MMAN (Memory Manager) background process which is all the time capturing the workload during the instance run and uses the memory advisers to decide what should be size of components.

Components like db_nk_caches, keep/recycle buffer cache and log buffer cache are manually tuned. ASMM does the auto tuning for the following pools:

1. DEFAULT buffer cache

2. Shared Pool

3. Large Pool

4. Java Pool

5. Streams Pool (10g R2+)

When ASMM is disabled the following initialization parameters are used to set the sizes for auto tuned pools in SGA. In oracle 10g these initialization parameters are called "auto tuned parameters".

1. db_cache_size

2. shared_pool_size

3. large_pool_size

4. java_pool_size

5. streams_pool_size

To switch to ASMM you need to set the initialization parameters SGA_TARGET to a non-zero value which must be less than or equal to value of parameter SGA_MAX_SIZE.

$ sqlplus / as sysdba

SQL> alter system set sga_max_size=1G scope=spfile;

System altered.

SQL> alter system set sga_target=500m scope=both;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 Total System Global Area 1073741824 bytes

Fixed Size                  1223540 bytes

Variable Size             738198668 bytes

Database Buffers          327155712 bytes

Redo Buffers                7163904 bytes

Database mounted.

Database opened.

SQL> show parameter sga_target

 NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

sga_target                  big integer 500M

SQL> show parameter sga_max_size

 NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

sga_max_size                big integer 1G

SQL>

According to the configuration we just made the SGA_TARGET is 500m, which means that the total size of SGA will be 500m and after allocating the defined sizes to the non-auto tuned pools and memory areas Oracle will dynamically manage all the auto tuned pools in the remaining memory space. But the total size of the SGA will never exceed 500m. I have met people having a perception that sga_target is the limit for the SGA but if the current_sizes are not enough for the components in the SGA it may grow upto sga_max_size which is incorrect. The SGA will stay in the boundaries of sga_target, period.

The reason why sga_max_size is usually larger then sga_target is the relationship between these two and the static nature of the sga_max_size parameter. Lets say you have set both sga_target and sga_max_size to 500m initially but later on after a couple of months you find out that 500m is not enough memory for your components to be managed in an efficient manner. Now if you want to increase the sga_taget to 1G, you will have to increase the sga_max_size to 1G also because sga_target cannot be larger then sga_max_size. But othe other hand if initially you set sga_target to 500m and sga_max_size to 1G then you have a window of at lease 500m to increase your sga_target without shutting down your database. sga_max_size is nothing more then a maximum limit which defines how big your sga_target can be, it doesn't effect memory allocation for the SGA in the oracle instance. Whenever an oracle instance is started it allocates the SGA memory equal to the value of sga_target, so it doesn't really matter how big you set your sga_max_size.

Now lets come back to our example where we have set sga_target to 500m, it doesn't mean all of this 500m will be used for auto tuned pools. The memory that will be used for the auto tuned pools is (sga_target - sum of non-auto tuned areas sizes). ASMM is not suppose to touch the size of manually tuned memory areas. If the total size of all non-auto tuned areas (log buffer cache, keep/recycle buffer cache etc) is 100m, then rest of 400m will be used for adjusting the sizes of auto tuned pools according to the workload.

After we enable the automatic memory management Oracle start managing the pools for us and set reasonable sizes for all the pools according to their nature and the type of work they do. Lets have a look at the current allocation of the auto tuned pools:

$ sqlplus / as sysdba

 SQL> show parameter db_cache_size

 NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

db_cache_size               big integer 0

SQL> show parameter pool_size

 NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

global_context_pool_size    string

java_pool_size              big integer 0

large_pool_size             big integer 0

olap_page_pool_size         big integer 0

shared_pool_size            big integer 0

streams_pool_size           big integer 0

SQL>     

           

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

 COMPONENT                          SIZE_MB

------------------------------- ----------

shared pool                            172

large pool                               4

java pool                                4

streams pool                             0

DEFAULT buffer cache                   292

Notice all the auto tuned parameters are set to 0 (we will discuss about this later). v$sga_dynamic_component shows us the current sizes of all these components in SGA. If we sum them up (172 + 4 + 4 + 292 = 472) is the total size where auto tuning is suppose to happen. Rest of 28m is for other areas in SGA like log buffer, keep/recycle cache sizes and any db_nk_cache_sizes if configured. Now lets open another console and connect with a normal oracle user to put the ASMM to the test.

 /*  This is another console where we login with user scott. The sysdba session is still intact in the other console. */

$ sqlplus scott/tiger@mydb

 SQL> create or replace package myPack

  2     is

  3      TYPE myType is table of char(2000) index by binary_integer;

  4      myTable myType;

  5     end;

  6       /

 Package created.

 SQL> begin

  2    for i in 1..100000 loop

  3       myPack.myTable(i) := i; 

  4    end loop;

  5  end;

  6  /

 PL/SQL procedure successfully completed.

 SQL> exit 

I established this session using shared server mode, so any variables I declare will be stored in the Large Pool where my UGA is being maintained. I created a packaged PL/SQL table of type char(2000) and inserted 100000 records in it. Being char(2000) each element's size is 2000 bytes no matter hat I assign to it. Hence after the population of the PL/SQL it is going to be around 200m(200*100000 bytes) in size. Since it is a packaged variable, so it is gonna stay in my Large Pool until I exit out the session. Once I exit from the session the variable should be cleaned out releasing space from Larg Pool.

/* Now we are back to the sysdba session */

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

 COMPONENT                             SIZE_MB

---------------------------------- ----------

shared pool                               132

large pool                                232

java pool                                   4

streams pool                                0

DEFAULT buffer cache                      104

Notice the current size of all these pools. It is clear now that when Large Pool needed space Oracle squeezed both buffer cache and shared pool and gave required space to large pool. Also notice (132 + 232 + 4 + 104 = 472). Now these sizes will stay like this until any pool needs more memory then it has and that is when these sizes will adjust again.

Lets go back to our normal session and create a heavy work load on Default buffer cache

/* Open another console and connect with a normal oracle user. The sysdba session is still open in the other console. */

$ sqlplus scott/tiger@mydb

SQL> create table big_table as select * from all_objects;

Table created.

SQL> insert into big_table select * from big_table;

40697 rows created.

SQL> /

81394 rows created.

SQL> /

162788 rows created.

SQL> /

325576 rows created.

SQL> /

651152 rows created.

SQL> /

1302304 rows created.

SQL> commit;

Commit complete.

SQL> analyze table big_table compute statistics;

Table analyzed.

SQL> select table_name , round(blocks*8192/1024/1024,2) size_mb

  2  from user_tables

  3  where table_name = 'BIG_TABLE';

TABLE_NAME                        SIZE_MB

------------------------------ ----------

BIG_TABLE                          283.21

SQL> 

We have processed about 1.3 million rows here in the table big_table which is 283m in size i.e quite larger then the whole buffer cache as its current size is 104m. When these queries are processed there is an extensive aging out and loading of rows from big_table in buffer cache causing high physical reads and low cache hit ratio. MMAN (memory manager) captures it and signals the adjustment in the size of buffer cache as there is a plenty of free space in Large Pool. Lets go back to sysdba session and see what are the current sizes of these components.

/* Now we are back to the sysdba session */

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

COMPONENT                              SIZE_MB

----------------------------------- ----------

shared pool                                108

large pool                                   4

java pool                                    4

streams pool                                 0

DEFAULT buffer cache                       356

See the buffer cache is larger the all others. The free space has been taken away from Large Pool and even some from Shared Pool also to accommodate heavy workload on buffer cache. As the current component sizes stand (108 + 4 + 4 + 356 = 472) Oracle is managing then within the boundary of 472m.

The dynamically adjusted sizes are retained through instance shutdowns if you are using server parameter file (i.e. spfile).

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  1223540 bytes

Variable Size             671089804 bytes

Database Buffers          394264576 bytes

Redo Buffers                7163904 bytes

Database mounted.

Database opened.

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

COMPONENT                            SIZE_MB

--------------------------------- ----------

shared pool                              108

large pool                                 4

java pool                                  4

streams pool                               0

DEFAULT buffer cache                     356

Note:- Even after a shutdown and restart the components are of the same size. 

 SQL> SELECT

  2     a.ksppinm "Parameter",

  3     b.ksppstvl "Value"

  4  FROM

  5     x$ksppi a,

  6     x$ksppsv b

  7  WHERE

  8     a.indx = b.indx AND

  9     a.ksppinm LIKE '/_/_%' escape '/' AND

 10    (a.ksppinm LIKE '%db_cache_size%' 

 11     OR a.ksppinm LIKE '%pool_size%');

Parameter                      Value

------------------------------ ----------

__shared_pool_size             113246208

__large_pool_size              4194304

__java_pool_size               4194304

__streams_pool_size            0

__db_cache_size                373293056

How is this happening? Actually whenever these components size is changed it is updated in undocumented siblings of auto tuned parameters. On the instance start up these undocumented parameters are read and the components are allocated accordingly. The above query shows how to read undocumented oracle parameters and their values from tables x$ksppi (contains parameter names) and x$ksppsv (contains parameter values for the instance) owned by sys

Here are some other useful columns in the view v$sga_dynamic_components where you can see the Operations have been happening with these components along their minimum and maximum sizes.

SQL> set lines 10000

SQL> column component format a20

SQL> select component , round(current_size/1024/1024,2) size_mb, 

  2  LAST_OPER_TYPE, OPER_COUNT, MIN_SIZE, MAX_SIZE

  3  from v$sga_dynamic_components

  4  where component like '%pool' 

  5  OR component ='DEFAULT buffer cache';

COMPONENT            SIZE_MB LAST_OPER_TYP OPER_COUNT 

-------------------- ------- ------------- ---------- 

shared pool              112 GROW                   1  

large pool                 4 SHRINK               114

java pool                  4 STATIC                 0

streams pool               0 STATIC                 0

DEFAULT buffer cache     352 GROW                 115

SQL> 

A few more things to know about ASMM:

When the ASMM is switched all auto tuned parameter sizes are managed by the oracle it self and any sizes manually defined for these parameters are no more considered as the max size that component may have, rather it is considered as a minimum size for that component.

$ sqlplus / as sysdba

 SQL> show parameter db_cache_size

 NAME                       TYPE        VALUE

-------------------------- ----------- --------------------

db_cache_size              big integer 0

SQL> show parameter pool_size

NAME                       TYPE        VALUE

-------------------------- ----------- --------------------

global_context_pool_size   string

java_pool_size             big integer 0

large_pool_size            big integer 0

olap_page_pool_size        big integer 0

shared_pool_size           big integer 0

streams_pool_size          big integer 0

SQL>

Here , All auto tuned parameter values are set to 0, which means 0 is the minimum size for this component and this component can be re-sized to 0 to allow other components use all of its memory. If we change them as follows:

SQL> alter system set db_cache_size=100m scope=both;

System altered.

SQL> show parameter db_cache_size

NAME                       TYPE        VALUE

-------------------------- ----------- --------------------

db_cache_size              big integer 100M

SQL> 

This means 100m is the minimum size for the buffer cache, no matter what happens to the other components this 100m will never be taken away from the Default buffer cache. Manual settings of the auto tuned parameters is useful when you don't want one of components to suffer too much because of auto adjustments in sizes.

When setting the auto tuned parameters manually if you set a size larger then its current size and the increase in the size can be supported by shrinking other components then the change is done immediately.

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

COMPONENT               SIZE_MB

-------------------- ----------

shared pool                 112

large pool                    4

java pool                     4

streams pool                  0

DEFAULT buffer cache        352

SQL> alter system set shared_pool_size = 120m scope=both;

System altered.

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

COMPONENT               SIZE_MB

-------------------- ----------

shared pool                 120

large pool                    4

java pool                     4

streams pool                  0

DEFAULT buffer cache        344

We increased the size of shared pool from 112m to 120m and there was space in buffer cache that was available for allocating to shared pool so our change came into effect immediately. And when there is no space available to grow the pool to the size you specified, you will get an error like this.

SQL> alter system set shared_pool_size = 450m scope=both;

alter system set shared_pool_size = 450m scope=both

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified  value is invalid

ORA-04033: Insufficient memory to grow pool

SQL>

When an auto tuned parameters are set to a value lower then their current size then the change is not done immediately.

SQL> alter system set shared_pool_size = 100m scope=both;

System altered.

SQL> select component , round(current_size/1024/1024,2) size_mb

  2  from v$sga_dynamic_components

  3  where component like '%pool' 

  4  OR component ='DEFAULT buffer cache';

COMPONENT               SIZE_MB

-------------------- ----------

shared pool                 120

large pool                    4

java pool                     4

streams pool                  0

DEFAULT buffer cache        344

SQL> 

Now according to the workload if a situations comes where Oracle has to shrink shared pool then the shared will be squeezed to 100m and the shrinking will stop since 100m is the minimum value for shared pool.

If you disable the ASMM then all the components that are being managed by the auto tuning will be freezed at their current sizes and become static unless you enable the ASMM again.

SQL> alter system set sga_target=0 scope=both;

System altered.

SQL> show parameter db_cache_size

NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

db_cache_size               big integer 344M

SQL> show parameter pool_size

NAME                        TYPE        VALUE

--------------------------- ----------- ---------------------

global_context_pool_size    string

java_pool_size              big integer 4M

large_pool_size             big integer 4M

olap_page_pool_size         big integer 0

shared_pool_size            big integer 120M

streams_pool_size           big integer 0

SQL>