Trying to understand PGA usage (20GB) for ONE process/session

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
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 client

0.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 understanding is flawed?)

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-l
Received on Mon Nov 13 2017 - 18:23:51 CET

Original text of this message