How to find PGA usage by Oracle session
The following query will find any sessions in an Oracle dedicated environment using over 20mb pga memory:
set linesize 110
column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a25
column logon_time format a10
select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and PGA_USED_MEM/1024/1024 > 20 -- pga_used memory over 20mb
order by PGA_USED_MEM;
As a DBA why do I care if see database sessions using alot of PGA memory
PGA (Program Global Area) - it is the session memory that is assigned to a particular program inside of Oracle.I have seen OLTP applications using over 100mb of PGA per session and have over 20 connections into the database. That is about 2gb of pga memory being used by one application. I have PGA_AGGREGATE_TARGET set to 1gb for my entire database instance how could 20 connection be consuming 2gb of pga memory what about the other sessions. The PGA_AGGREATE_TARGET parameter is a recommended setting not a hard setting.
This was happening in an Oracle database RAC cluster with tight memory constraints. If you have supported an Oracle RAC environment you will know that if a node stops responding for any reasons it will be evicted from the cluster. A typical reason why a node get's evicted from the cluster is the machine slow's down due to the maching paging memory in and out of swap. Even though this cluster had tight memory constraints it would only evict a node once a month when the memory constraints were pushed.
In a later blog I will go into more detail around best practices supporting an Oracle RAC environment and troubleshooting node evictions.
In a non Oracle RAC environment the server would just come to a very slow crawl.
In this particular case I showed the developer how to monitor his session for PGA memory usage. He found out why his application was using so much memory and made the appropiate code changes.
How to find your current sessions PGA memory usage
You can find you current sessions pga memory usage by either of the following query's. The first query everyone should be able to run:column osuser format a15
column name format a20
select s.osuser osuser,s.serial# serial,se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
,v$session s
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
and s.sid=se.sid
and s.sid = sys_context('USERENV','SID')
group by n.name,se.sid,s.osuser,s.serial#
order by 2;
This query requires you to have access to v$session and v$process data dictionary views. Since you are only try to find about your current session there is no need to use the global data dictionary views(gv$) that are used to go across the cluster.
column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a25
column logon_time format a10
select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from v$session s
, v$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.sid = sys_context('USERENV','SID');
