Oracle: sizing and monitoring SGA and PGA memory
SGA, PGA, ASMM and AMM basics, monitoring queries, advisors, sizing rules of thumb and HugePages.
Cheat sheet to understand, measure and size the memory of an Oracle instance (SGA, PGA, AMM/ASMM, HugePages).
Reminder: SGA and PGA
- SGA (System Global Area): memory shared between sessions. All data that is read or modified goes through it (cached blocks, shared SQL areas...).
- PGA (Program Global Area): private memory of a server or background process (sorts, hashes, PL/SQL variables). Each process has its own.
| SGA component | Parameter |
|---|---|
| Buffer cache | DB_CACHE_SIZE |
| Redo log buffer | LOG_BUFFER |
| Shared pool | SHARED_POOL_SIZE |
| Java pool | JAVA_POOL_SIZE |
| Large pool | LARGE_POOL_SIZE |
| Streams pool | STREAMS_POOL_SIZE |
Memory management modes
- Manual (8i/9i): each component is sized by hand; exceeding the planned size raises an error.
- ASMM (10g): if
SGA_TARGET> 0, Oracle distributes memory between the SGA components. A component with a non-zero value acts as a minimum.LOG_BUFFERis not part of it. The PGA is still managed separately withPGA_AGGREGATE_TARGET. - AMM (11g and later): if
MEMORY_TARGET> 0, Oracle distributes memory between the SGA and the PGA (MEMORY_MAX_TARGETsets the ceiling).
Do not confuse ASMM (Automatic Shared Memory Management), AMM (Automatic Memory Management), ASM (Automatic Storage Management) and ASSM (Automatic Segment Space Management). AMM is not compatible with HugePages.
SGA_MAX_SIZE is an upper bound for the SGA only: it does not limit process memory (sorts, hashes, PL/SQL variables). SGA_MAX_SIZE >= SGA_TARGET. Oracle does not swap, the operating system does.
Check the current configuration and usage
show parameter sga
show parameter pga
show parameter target
show parameter memory_target
show parameter pga_aggregate_limit
show parameter use_large_pages
-- SGA distribution
select * from v$sgainfo;
select name, round(bytes/(1024*1024*1024),0) as "Size(GB)", round(bytes/(1024*1024),0) as "Size(MB)", resizeable
from v$sgainfo order by 2 desc;
-- Total SGA size
select sum(value) from v$sga;
-- Free memory in the SGA
set lines 200 pages 300
select round(sum(bytes)/1024/1024,0) "Total Free memory(MB)" from v$sgastat where name like '%free memory%';
select pool, round(bytes/1024/1024,0) "Free memory(MB)" from v$sgastat where name like '%free memory%';
-- PGA
select name, value/1024/1024 inMB from v$pgastat where name in ('total PGA allocated','maximum PGA allocated');
select name, value from v$pgastat where name like 'total PGA a%';
-- Current usage (PGA in use, SGA size)
select (value/1024/1024) CURRENT_PGA_IN_USE_IN_MB from v$pgastat where name = 'total PGA inuse';
select sum(bytes/1024/1024) CURRENT_SGA_SIZE_IN_MB from v$sgastat;
Other useful views: v$sgastat, v$memory_dynamic_components, v$sga_dynamic_free_memory.
Advisors
-- PGA
select pga_target_for_estimate, pga_target_factor, estd_extra_bytes_rw from v$pga_target_advice;
-- SGA
select sga_size, sga_size_factor, estd_db_time from v$sga_target_advice;
-- MEMORY_TARGET
select memory_size, memory_size_factor, estd_db_time from v$memory_target_advice;
ADDM and AWR reports (@?/rdbms/admin/addmrpt and @?/rdbms/admin/awrrpt) complete the analysis.
Who is using the PGA?
-- Current and maximum size per session
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);
-- ASH history: statements that allocated the most PGA over 2 days
select sample_time, session_id sid, session_serial# serial#, sql_id, sql_plan_hash_value hash,
sql_plan_operation, event, pga_allocated
from dba_hist_active_sess_history
where sql_id is not null and sample_time > sysdate - 2
order by pga_allocated desc;
-- Sum per sql_id (samples > 200 MB)
select sql_id, sum(pga_allocated)/1024/1024 as "pga_allocated MB", count(*)
from dba_hist_active_sess_history
where sql_id is not null and sample_time > sysdate - 2 and pga_allocated > (1024*1024)*200
group by sql_id;
PGA history (AWR, last 60 days)
WITH
pgastat_denorm_1 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
snap_id,
dbid,
instance_number,
SUM(CASE name WHEN 'PGA memory freed back to OS' THEN value ELSE 0 END) pga_mem_freed_to_os,
SUM(CASE name WHEN 'aggregate PGA auto target' THEN value ELSE 0 END) aggr_pga_auto_target,
SUM(CASE name WHEN 'aggregate PGA target parameter' THEN value ELSE 0 END) aggr_pga_target_param,
SUM(CASE name WHEN 'bytes processed' THEN value ELSE 0 END) bytes_processed,
SUM(CASE name WHEN 'extra bytes read/written' THEN value ELSE 0 END) extra_bytes_rw,
SUM(CASE name WHEN 'global memory bound' THEN value ELSE 0 END) global_memory_bound,
SUM(CASE name WHEN 'maximum PGA allocated' THEN value ELSE 0 END) max_pga_allocated,
SUM(CASE name WHEN 'maximum PGA used for auto workareas' THEN value ELSE 0 END) max_pga_used_aut_wa,
SUM(CASE name WHEN 'maximum PGA used for manual workareas' THEN value ELSE 0 END) max_pga_used_man_wa,
SUM(CASE name WHEN 'total PGA allocated' THEN value ELSE 0 END) tot_pga_allocated,
SUM(CASE name WHEN 'total PGA inuse' THEN value ELSE 0 END) tot_pga_inuse,
SUM(CASE name WHEN 'total PGA used for auto workareas' THEN value ELSE 0 END) tot_pga_used_aut_wa,
SUM(CASE name WHEN 'total PGA used for manual workareas' THEN value ELSE 0 END) tot_pga_used_man_wa,
SUM(CASE name WHEN 'total freeable PGA memory' THEN value ELSE 0 END) tot_freeable_pga_mem
FROM dba_hist_pgastat
WHERE name IN
('PGA memory freed back to OS'
,'aggregate PGA auto target'
,'aggregate PGA target parameter'
,'bytes processed'
,'extra bytes read/written'
,'global memory bound'
,'maximum PGA allocated'
,'maximum PGA used for auto workareas'
,'maximum PGA used for manual workareas'
,'total PGA allocated'
,'total PGA inuse'
,'total PGA used for auto workareas'
,'total PGA used for manual workareas'
,'total freeable PGA memory'
)
AND snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time > sysdate -60)
GROUP BY
snap_id,
dbid,
instance_number
),
pgastat_denorm_2 AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h.dbid,
h.instance_number,
s.startup_time,
MIN(h.pga_mem_freed_to_os) pga_mem_freed_to_os,
MIN(h.bytes_processed) bytes_processed,
MIN(h.extra_bytes_rw) extra_bytes_rw
FROM pgastat_denorm_1 h,
dba_hist_snapshot s
WHERE s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
GROUP BY
h.dbid,
h.instance_number,
s.startup_time
),
pgastat_delta AS (
SELECT /*+ MATERIALIZE NO_MERGE */
h1.snap_id,
h1.dbid,
h1.instance_number,
s1.begin_interval_time,
s1.end_interval_time,
ROUND((CAST(s1.end_interval_time AS DATE) - CAST(s1.begin_interval_time AS DATE)) * 24 * 60 * 60) interval_secs,
(h1.pga_mem_freed_to_os - h0.pga_mem_freed_to_os) pga_mem_freed_to_os,
h1.aggr_pga_auto_target,
h1.aggr_pga_target_param,
(h1.bytes_processed - h0.bytes_processed) bytes_processed,
(h1.extra_bytes_rw - h0.extra_bytes_rw) extra_bytes_rw,
h1.global_memory_bound,
h1.max_pga_allocated,
h1.max_pga_used_aut_wa,
h1.max_pga_used_man_wa,
h1.tot_pga_allocated,
h1.tot_pga_inuse,
h1.tot_pga_used_aut_wa,
h1.tot_pga_used_man_wa,
h1.tot_freeable_pga_mem
FROM pgastat_denorm_1 h0,
pgastat_denorm_1 h1,
dba_hist_snapshot s0,
dba_hist_snapshot s1,
pgastat_denorm_2 min /* to see cumulative use (replace h0 with min on select list above) */
WHERE h1.snap_id = h0.snap_id + 1
AND h1.dbid = h0.dbid
AND h1.instance_number = h0.instance_number
AND s0.snap_id = h0.snap_id
AND s0.dbid = h0.dbid
AND s0.instance_number = h0.instance_number
AND s1.snap_id = h1.snap_id
AND s1.dbid = h1.dbid
AND s1.instance_number = h1.instance_number
AND s1.snap_id = s0.snap_id + 1
AND s1.startup_time = s0.startup_time
AND s1.begin_interval_time > (s0.begin_interval_time + (1 / (24 * 60))) /* filter out snaps apart < 1 min */
AND min.dbid = s1.dbid
AND min.instance_number = s1.instance_number
AND min.startup_time = s1.startup_time
)
SELECT snap_id,
TO_CHAR(MIN(begin_interval_time), 'YYYY-MM-DD HH24:MI') begin_time,
TO_CHAR(MIN(end_interval_time), 'YYYY-MM-DD HH24:MI') end_time,
ROUND(SUM(pga_mem_freed_to_os) / POWER(2, 30), 3) pga_mem_freed_to_os,
ROUND(SUM(aggr_pga_auto_target) / POWER(2, 30), 3) aggr_pga_auto_target,
ROUND(SUM(aggr_pga_target_param) / POWER(2, 30), 3) aggr_pga_target_param,
ROUND(SUM(bytes_processed) / POWER(2, 30), 3) bytes_processed,
ROUND(SUM(extra_bytes_rw) / POWER(2, 30), 3) extra_bytes_rw,
ROUND(SUM(global_memory_bound) / POWER(2, 30), 3) global_memory_bound,
ROUND(SUM(max_pga_allocated) / POWER(2, 30), 3) max_pga_allocated,
ROUND(SUM(max_pga_used_aut_wa) / POWER(2, 30), 3) max_pga_used_aut_wa,
ROUND(SUM(max_pga_used_man_wa) / POWER(2, 30), 3) max_pga_used_man_wa,
ROUND(SUM(tot_pga_allocated) / POWER(2, 30), 3) tot_pga_allocated,
ROUND(SUM(tot_pga_inuse) / POWER(2, 30), 3) tot_pga_inuse,
ROUND(SUM(tot_pga_used_aut_wa) / POWER(2, 30), 3) tot_pga_used_aut_wa,
ROUND(SUM(tot_pga_used_man_wa) / POWER(2, 30), 3) tot_pga_used_man_wa,
ROUND(SUM(tot_freeable_pga_mem) / POWER(2, 30), 3) tot_freeable_pga_mem,
0 dummy_15
FROM pgastat_delta
GROUP BY
snap_id
ORDER BY
snap_id;
Cache ratios and contention
-- Overall cache hit ratio
select 1 - (phy.value / (cur.value + con.value)) "Cache Hit Ratio"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
-- Hit ratio per buffer pool
select name, (1 - physical_reads / (db_block_gets + consistent_gets)) "HIT RATIO"
from v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0;
-- Contention: a high value is a bad sign
select name, value from v$sysstat where name = 'free buffer inspected';
select event, total_waits
from v$system_event
where event in ('free buffer waits', 'buffer busy waits');
Sizing rules of thumb
- Non-parallel environment:
PGA_AGGREGATE_TARGET = (total RAM * 80%) * 50%. - Parallel environment:
PGA_AGGREGATE_TARGET = 100 MB * parallel_max_servers. PGA_AGGREGATE_LIMIT: for example, on a 32 GB server with a 13 GB SGA, you could go up to 25 GB (120% of RAM = 38 GB, minus the SGA).
alter system set sga_max_size = 7G scope=both;
alter system set sga_target = 7G scope=both;
alter system set pga_aggregate_limit = 15G scope=both;
Enabling AMM
Example for a server with 8 GB of RAM (about 7 GB for Oracle). First back up the pfile, test the configuration, then re-create the spfile.
-- Copy the pfile
create pfile='<ORACLE_HOME>/dbs/pfile_backup.ora' from spfile;
# Working file to edit
cp pfile_backup.ora pfile_new.ora
vim pfile_new.ora
-- Test the configuration
startup pfile='<ORACLE_HOME>/dbs/pfile_new.ora'
-- If everything is fine, save the parameters
create spfile from pfile='<ORACLE_HOME>/dbs/pfile_new.ora';
CONN / AS SYSDBA
-- Static parameter: leave some room for growth without a restart.
ALTER SYSTEM SET MEMORY_MAX_TARGET=7G SCOPE=SPFILE;
-- Dynamic parameters (Oracle has full control of memory).
ALTER SYSTEM SET MEMORY_TARGET=6G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;
-- Previous values (for rollback):
-- MEMORY_MAX_TARGET=0, MEMORY_TARGET=0, PGA_AGGREGATE_TARGET=195M, SGA_TARGET=3G
-- Restart the instance
SHUTDOWN IMMEDIATE;
STARTUP;
Reference: Automatic Memory Management (Oracle-Base).
HugePages (Linux)
Use with ASMM (not with AMM). The calculation script is provided by Oracle (My Oracle Support, note 401749.1).
# Current state
grep Huge /proc/meminfo
# /etc/sysctl.conf
vm.nr_hugepages=12300
sysctl -p
# /etc/security/limits.d/ : memlock in KB, i.e. vm.nr_hugepages * 2048
oracle soft memlock 12107776
oracle hard memlock 12107776
Check:
show parameter use_large_pages
grep Huge /proc/meminfo
ipcs -m
ipcs -m | awk '{ print $5}' | awk '{a+=$0}END{print a}'
Notes
- Numeric values (sizes, number of HugePages) are examples to adapt to the machine.
FAST_START_MTTR_TARGETcan be set like this:ALTER SYSTEM SET FAST_START_MTTR_TARGET = 300 SCOPE=BOTH;.