Oracle: estimating SGA/PGA memory and storage
Queries to estimate the memory and storage needs of an Oracle database from an existing one.
To size a development or standby database, you can estimate its memory and storage needs from an existing production database. This note gives the queries to do it on Linux/UNIX.
Reminders
- The SGA is shared memory, allocated when the instance starts.
- The PGA (Process Global Area) is private memory of each process. It is not pre-allocated: it is taken from RAM as needed.
Inspect SGA and PGA
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%';
show parameter sga;
show parameter pga;
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;
Sizing guidelines
| Item | Guideline |
|---|---|
| RAM reserved for the OS | about 10% on Linux, 20% on Windows |
| Memory for connections | pga_aggregate_target (sorts and hash joins) |
| SGA | sga_max_size / sga_target |
Estimate the memory footprint of an existing database
SGA footprint:
SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
Maximum PGA consumed by all processes:
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)" from v$process;
PGA detail per process:
SELECT spid, program, pga_max_mem max, pga_alloc_mem alloc, pga_used_mem used, pga_freeable_mem free
FROM V$PROCESS;
A program name without a background process name (PMON, SMON, RECO...) is a session process. Depending on the processes parameter, these queries only capture the connections that exist at that moment.
Memory reserve for unconnected sessions
Largest PGA used by a user session:
select max(p.pga_max_mem)/1024/1024 "PGA MAX MEM USER SESSION(MB)"
from v$process p, v$session s
where p.addr = s.paddr and s.username is not null;
Maximum number of processes, and number of connected sessions:
select name, value from v$parameter where name = 'processes';
select count(1) from v$session where username is not null;
Then compute:
- Estimated memory for current connections = SGA + PGA
- Unconnected sessions (MB) = (
processes- connected sessions) x max PGA of a user session - Estimated memory at maximum process utilisation = SGA + PGA + unconnected sessions
Estimate the storage requirements
The following query generates one df -k command per mount point used by datafiles, tempfiles, redo logs and control files. Run the generated commands in a shell on the primary server.
select unique 'df -k '||a.MTPOINT MOUNT_POINT
from ( select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-1) MTPOINT
from dba_data_files
union
select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-1) MTPOINT
from dba_temp_files
union
select substr(MEMBER,0,instr(MEMBER,'/',1,2)-1) MTPOINT
from v$logfile
union
select substr(NAME,0,instr(NAME,'/',1,2)-1) MTPOINT
from v$controlfile
) a;
Check whether the file destinations (archive logs, dumps, diagnostic destination) are on other mount points:
select name, value
from v$parameter
where (regexp_like(name, '^log_archive_(dest|dest\_([1-9]))$', 'i') or name like '%dump_dest' or name like '%file_dest' or name like 'diag%dest' or name ='utl_file_dir') and value is not null;
And the database directories:
select 'df -k '||substr(DIRECTORY_PATH,0,instr(DIRECTORY_PATH,'/',1,2)-1) MTPOINT from dba_directories;
For a more concise report, filter the df output on the relevant mount points:
df -k | grep -E '/u01|/u02|/utldir|/opt'
Notes
- Based on this article.
- Related reading: multithreaded model with threaded_execution and switching from AMM to ASMM.