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

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_at_gmail.com> 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_at_gmail.com> 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
>

--
http://www.freelists.org/webpage/oracle-l