That query confirms the usage:
SID
SPID
TOTAL_ALLOCATED
PID
SERIAL#
CATEGORY
ALLOCATED
USED
MAX_ALLOCATED
CON_ID
100
161947
27389800644
805
172
SQL
69598064
24
87929160
0
100
161947
27389800644
805
172
Other
27320114452
27320114452
0
100
161947
27389800644
805
172
PL/SQL
88128
9216
95288
0
Now I need to understand how one session can be allocated that much
(outside of a bug/memory leak).
Chris
On Mon, Nov 13, 2017 at 11:30 AM, Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hi Chris,
You can use the following query to check it:
https://github.com/xtender/xt_scripts/blob/master/pga/
pga_usage_by_sid.sql
SELECT
s.sid,p.spid
,sum(pm.allocated) over(partition by sid) total_allocated
,pm.*
FROM
v$session s
, v$process p
, v$process_memory pm
WHERE
s.paddr = p.addr
AND p.pid = pm.pid
AND s.sid = &1
ORDER BY
3 desc
/
Or use https://github.com/xtender/xt_scripts/blob/master/pga/details.sql
to get more details.
On Mon, Nov 13, 2017 at 8:23 PM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
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
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
