MEMORY, SGA,PGA,AMM,ASMM,MMM IN ORACLE
Before configuration of SGA or PGA or Memory in the database, it is very important to configure the kernel parameters optimally to hold the database memory from OS.
[oracle@xhydra ~]$ cat /etc/sysctl.conf |egrep 'fs.|net.|kernel.'|grep -v '#'
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
kernel.shmall and kernel.shmmax are crucial for SGA allocation
Learning 1:
How to calculate the sga and pga size as per the current workload in the database ?
Formula is to to find the difference between total sga allocated and the current sga. This is the target value for sga(sga_target) as per usage and not the max value (sga_max_size)
SQL> WITH sga as (SELECT sum(value) tot_sga from v$sga),sga_d_c as (SELECT current_size cur_sga from v$sga_dynamic_free_memory)
SELECT (TOT_SGA - CUR_SGA)/1048576 sga_target_mb FROM sga,sga_d_c;
SGA_TARGET_MB
-------------
260
Similarly, calculate the pga target with the formula,
Maximum value out of max value allocated for pga in the instance currently and the value of pga_aggregate_target parameter.
SQL> WITH max_pga as (SELECT value/1048576 MB from v$pgastat where name='maximum PGA allocated'), pga_agg_target as (SELECT value/1048576 PMB from v$parameter where name='pga_aggregate_target')
2 SELECT MB,PMB,greatest(MB,PMB) PGA_AGGREGATE_TARGET FROM max_pga,pga_agg_target;
MB PMB PGA_AGGREGATE_TARGET
---------- ---------- --------------------
348.155273 52 348.155273
Now let us combine both the values in a single query to get sga and pga target values respectively.
SQL> WITH sga as (SELECT sum(value) tot_sga from v$sga),sga_d_c as (SELECT current_size cur_sga from v$sga_dynamic_free_memory),max_pga as (SELECT value/1048576 MB from v$pgastat where name='maximum PGA allocated'), pga_agg_target as (SELECT value/1048576 PMB from v$parameter where name='pga_aggregate_target')
SELECT (TOT_SGA - CUR_SGA)/1048576 sga_target_mb,MB,PMB,greatest(MB,PMB) pga_aggregate_target_mb FROM sga,sga_d_c,max_pga,pga_agg_target;
SGA_TARGET_MB MB PMB PGA_AGGREGATE_TARGET_MB
------------- ---------- ---------- -----------------------
260 348.155273 52 348.155273
To enable AMM, we need to find out the optimal value for memory_target parameter. For setting memory_target, the value of memory_max_target parameter should be figured out. This query can give the target size for every memory component as per current usage.
SQL> WITH sga as (SELECT sum(value) tot_sga from v$sga),sga_d_c as (SELECT current_size cur_sga from v$sga_dynamic_free_memory),max_pga as (SELECT value/1048576 MB from v$pgastat where name='maximum PGA allocated'), pga_agg_target as (SELECT value/1048576 PMB from v$parameter where name='pga_aggregate_target')
2 SELECT (TOT_SGA - CUR_SGA)/1048576 sga_target_mb,MB,PMB,greatest(MB,PMB) pga_aggregate_target_mb,((TOT_SGA - CUR_SGA)/1048576 + greatest(MB,PMB)) memory_max_target_mb FROM sga,sga_d_c,max_pga,pga_agg_target;
SGA_TARGET_MB MB PMB PGA_AGGREGATE_TARGET_MB MEMORY_MAX_TARGET_MB
------------- ---------- ---------- ----------------------- --------------------
260 348.155273 52 348.155273 608.155273
To derive the outliers, minimum and maximum value from the historical statistics based on time, deeper analysis is required to know the peak time of the database.
set lines 200 pages 1000
col startup_time for a20
WITH sga as (SELECT sum(s.value) tot_sga from dba_hist_sga s group by s.snap_id),sga_d_c as (SELECT d.current_size cur_sga from dba_hist_mem_dynamic_comp d),
max_pga as (SELECT p.value/1048576 MB from dba_hist_pgastat p where p.name='maximum PGA allocated'),
pga_agg_target as (SELECT pr.value/1048576 PMB from dba_hist_parameter pr where pr.parameter_name='pga_aggregate_target')
SELECT startup_time,
(TOT_SGA - CUR_SGA)/1048576 sga_target_mb,
MB,
PMB,
greatest(MB,PMB) pga_aggregate_target_mb
FROM sga,
sga_d_c,
max_pga,
pga_agg_target,
dba_hist_snapshot;
STARTUP_TIME SGA_TARGET_MB MB PMB PGA_AGGREGATE_TARGET_MB
-------------------- ------------- ---------- ---------- -----------------------
20-OCT-23 12.20.32.0 1999.99861 297.851563 368 368
00 PM
20-OCT-23 12.20.32.0 1999.99861 297.851563 368 368
00 PM
20-OCT-23 12.20.32.0 1999.99861 297.851563 368 368
00 PM
Statistics can also unleash the hidden trends in the size of the memory areas allocation dynamically over the historical period.
SQL> set lines 200 pages 1000
col startup_time for a20
WITH sga as (SELECT sum(s.value) tot_sga from dba_hist_sga s group by s.snap_id),sga_d_c as (SELECT d.current_size cur_sga from dba_hist_mem_dynamic_comp d),
max_pga as (SELECT p.value/1048576 MB from dba_hist_pgastat p where p.name='maximum PGA allocated'),
pga_agg_target as (SELECT pr.value/1048576 PMB from dba_hist_parameter pr where pr.parameter_name='pga_aggregate_target')
SELECT
avg((TOT_SGA - CUR_SGA)/1048576) ASTMB,
avg(MB) AMPMB,
avg(PMB) APATMB,
avg(greatest(MB,PMB)) APATNMB,
median((TOT_SGA - CUR_SGA)/1048576) MSTMB,
median(MB) MMPMB,
median(PMB) MPATMB,
median(greatest(MB,PMB)) MPATNMB,
stats_mode((TOT_SGA - CUR_SGA)/1048576) SMSTMB,
stats_mode(MB) SMMPMB,
stats_mode(PMB) SMPATMB,
stats_mode(greatest(MB,PMB)) SMPATNMB
FROM sga,
sga_d_c,
max_pga,
pga_agg_target;SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
ASTMB AMPMB APATMB APATNMB MSTMB MMPMB MPATMB MPATNMB SMSTMB SMMPMB SMPATMB SMPATNMB
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1832.31505 339.319824 99.5 350.250244 1999.99629 315.630859 10 319.322266 1999.99629 297.851563 10 428.166016
The above output shows that the frequently used values are,
sga_target= 2GB and pga_aggregate_target=10 and max pga allocated = 298 MB and max pga value for pga_aggregate_target=428MB
SQL> ALTER system set SGA_TARGET=260M scope=spfile;
System altered.
SQL> alter system set memory_max_target=608M scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 637534208 bytes
Fixed Size 2927624 bytes
Variable Size 478151672 bytes
Database Buffers 150994944 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> show parameter memory%_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 608M
memory_target big integer 0
SQL> show parameter pga%_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 52M
SQL> show parameter sga%_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 260M
SQL> alter system set memory_target=608M;
System altered.
Learning 2:
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 25M
shared_pool_size big integer 500M
SQL> show parameter large_pool
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 76M
SQL> show parameter db_cache
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 600M
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
SQL> alter system set sga_target=1800m;
System altered.
SQL> startup mount;
ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0
ORA-01078: failure in processing system parameters
Configure AMM (memory_target and memory_max_target ):
To configure AMM, configure memory_target and memory_max_target to a non-zero value. memory_max_target is a static parameter which determines the maximum size of memory that can be allocated and used in the database. It has default value of 0.
memory_target is the minimum size usable for both SGA and PGA combined and this parameter cannot be set unless memory_max_target is set. This parameter is dynamic, can be modified during run time and does not need a restart of database. Value of memory_target should be less than or equal to memory_max_target.
SQL> alter system set memory_target=608M;
alter system set memory_target=608M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET- Calculate the min size of SGA and PGA with the current and historical trends with the help of queries provided above.
- Calculate memory_target which is the minimum value for AMM by adding both min SGA and PGA calculated based on the historical usage and this is not a prediction for future usage. If more memory is available from OS, then more memory can be dedicated to instance than the calculated value. But, be cautious about resource wastage.
- Before memory_target parameter is set, memory_max_target should be set as a hard limit for memory_target.
- Set sga_target and pga_aggregate_target to 0. If these values are set to a non-zero value, then those values are considered as soft limit for sga and pga respectively. Oracle uses sga and pga combined together to less than or equal to memory_target as per the requirement.
SQL> show parameter memory_tar
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 0
SQL> show parameter memory_max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 0
Configure ASMM(sga_target and pga_aggregate_target):
If currently in MMM, then,
- Set sga_target to non-zero value based on scripts provided to calculate sga. Before that, sga_max_size should be specified as hard limit. sga_target should be less than or equal to sga_max_size.
- Components inside SGA like db_cache_size, large_pool_size, shared_pool_reserved_size and shared_pool_size can or cannot be left as such as per current value. If they are left to their default values, then those values are considered as minimum starting limit for those pools. If they are resized to zero, then ASMM automatically grow or shrink the pools dynamically as per requirement. Setting these pool values to 0 can give more benefit for auto sga tuning.
- pga_aggregate_target can also be calculated as per the formula and script provided above.
If currently in AMM, then,
- Set memory_target to 0.
- sga_target is automatically sized based on current value.
- Components inside SGA like db_cache_size, large_pool_size, shared_pool_reserved_size and shared_pool_size can or cannot be left as such as per current value. If they are left to their default values, then those values are considered as minimum starting limit for those pools. If they are resized to zero, then ASMM automatically grow or shrink the pools dynamically as per requirement. Setting these pool values to 0 can give more benefit for auto sga tuning.
- pga_aggregate_target can also be calculated as per the formula and script provided above.