Check the instance Memory Parameter include SGA, PGA Size

Check the instance Memory Parameter include SGA, PGA size

Check Total SGA plus PGA Size

select sum(bytes)/1024/1024 as "Total PGA+SGA Mbytes" from
(select value as bytes from v$sga union all
select value as bytes from v$sesstat s,
v$statname n where
n.STATISTIC# = s.STATISTIC# and
n.name = 'session pga memory');

Find out the instance memory parameters

col name for a30
col VALUE for a30
select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size',
'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target',
'workarea_size_policy', 'streams_pool_size' ) ;

Check parameters at Session and Instance level

col Parameter for a30
col "Session Value" for a25
col "Instance Value" for a25
select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size',
'__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');

Find out the PGA allocated memory in MB for particulars SIDs

COLUMN USED_MEMORY FORMAT 99999D9
COLUMN ALLOC_MEMORY FORMAT 99999D9
COLUMN FREE_MEMEOY FORMAT 99999D9
COLUMN MAX_MEMORY FORMAT 99999D9
COLUMN username FORMAT a12
COLUMN program FORMAT a20
COLUMN sid FORMAT a5
COLUMN spid FORMAT a8
SET LINESIZE 300
SELECT s.username, SUBSTR(s.sid,1,5) sid, p.spid, logon_time,
SUBSTR(s.program,1,22) program , s.process pid_remote,
s.status,
ROUND(pga_used_mem/1024/1024) "USED_MEMORY",
ROUND(pga_alloc_mem/1024/1024) "ALLOC_MEMORY",
ROUND(pga_freeable_mem/1024/1024) "FREE_MEMEOY",
ROUND(pga_max_mem/1024/1024) "MAX_MEMORY"
FROM v$session s,v$process p
WHERE p.addr=s.paddr
ORDER BY pga_max_mem,logon_time;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.