LLI Wiki — My Digital Knowledge Base

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

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:

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

Mastodon LLIMastodon SocialFosstodon