Trying to understand PGA usage (20GB) for ONE process/session
Date: Mon, 13 Nov 2017 11:23:51 -0600
Message-ID: <CAP79kiTpEZrxeVySY1x5YrXe3UUguMobE=TPTNtwOPQLKPjzvA_at_mail.gmail.com>
We have a known problem with a particular report. Each session that runs this report will get a ORA-4030 (or 4036) error after chewing on the SQL for some time.
What I'm confused about is how the PGA usage can be so high for one process?
Here's the results from the query below:
OSUSER SPID SID SERIAL MACHINE MODULE ACTION CLIENT_INFO SQL_ID STATUS EVENT WAIT_TIME_SECS NAME MAXMEM_GB redacted
161947
100
14257
redacted
redacted
5cy9r4xbwqayb
ACTIVE
SQL*Net message from client0.002819
session pga memory
25.236848
_pga_max_size 214732800
How can I have a session with 25GB of pga memory allocated? Just trying to
understand how one session can chew up so much.
Here's the SQL used for the above result (maybe it is flawed? - or my
select s.osuser, p.spid,s.logon_time,se.sid,s.serial# serial,s.machine,s.module,s.action, s.client_info, s.sql_id, s.status, s.event, s.wait_time_micro/1000000 as wait_time_secs, n.name,
max(se.value)/1024/1024/1024 maxmem_GB
from v$sesstat se,
v$statname n
,v$session s
,v$process p
where n.statistic# = se.statistic#
and n.name in ('session pga memory')
and s.sid=se.sid
and s.paddr = p.addr
and s.type != 'BACKGROUND'
group by n.name,p.spid,s.logon_time,se.sid,s.osuser,s.serial#, s.machine, s.module,s.action,s.client_info, s.sql_id, s.status, s.event, s.wait_time_micro/1000000
order by maxmem_GB desc
FETCH FIRST 1 ROWS ONLY /
Any thoughts are appreciated.
Chris Taylor
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 13 2017 - 18:23:51 CET