©2015 -
How To Check Oracle Database Memory and Storage Size
On certain occasions, I will be asked for an estimated resource requirements for a database that will be setup for a development environment or as a standby database. The following are my methods to calculate the memory and storage size based on the existing production database. The calculation on the storage size are meant for UNIX and LINUX.
1. Get the estimated memory footprint of an existing database.
-
SQL> SELECT sum(value)/1024/1024 "TOTAL SGA (MB)" FROM v$sga;
-
select sum(pga_max_mem)/1024/1024 "TOTAL MAX PGA (MB)" from v$process;
A more detailed breakdown of PGA memory usage:
SELECT spid, program,
pga_max_mem max,
pga_alloc_mem alloc,
pga_used_mem used,
pga_freeable_mem free
FROM V$PROCESS;
NOTE: The resulting program name having no associated process name (ex. PMON, SMON, RECO,..) is a session process.
Depending on the settings of the init parameter processes, it could be that the queries above only captures the active connections.
-
The query below gives the max allocated memory by a user session. We’ll use this to calculate the remaining memory requirements for the unconnected processes.
select max(p.pga_max_mem)/1024/1024 "PGA MAX MEMORY OF USER SESSION (MB)"
from v$process p, v$session s
where P.ADDR = S.paddr and s.username is not null;
Get the maximum number of processes that an Oracle instance can handle.
select name, value from v$parameter
where name = 'processes';
Let's count the number of connected sessions:
select count(1) from v$session
where username is not null;
Get the total available connections by subtracting the connected sessions count from the processes parameter value.
The resulting value shall be multiplied by the resulting max allocated memory by a session done by the previous query.
This would then give you the estimated amount of reserve memory needed to accommodate additional connections.
ESTIMATED TOTAL MEMORY REQUIREMENT
SGA + PGA = EST MEMORY REQUIREMENT FOR CURRENT CONNECTIONS
SGA + PGA + UNCONNECTED SESSIONS = EST MEMORY REQUIREMENT AT MAXIMUM PROCESS UTILIZATION
where:
Unconnected Sessions (MB) = (processes -
2. Get the estimated storage sizing requirements of a database.
The procedure below in determining the storage size are meant for UNIX and LINUX environment. As for windows, just right click and select the properties on each of the drive letters given by the results of the queries.
@primary server,
-
select unique 'df -
from ( select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-
from dba_data_files
union
select substr(FILE_NAME,0,instr(FILE_NAME,'/',1,2)-
from dba_temp_files
union
select substr(MEMBER,0,instr(MEMBER,'/',1,2)-
from v$logfile
union
select substr(NAME,0,instr(NAME,'/',1,2)-
from v$controlfile
) a;
-
get the mount point size of file destinations
select name, value
from v$parameter
where (regexp_like(name, '^log_archive_(dest|dest\_([1-
-
select 'df -
An alternative for a df command is to grep all the valid database related mount point in a single string to get a much more concise report.
example:
$ df -