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>
