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

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Mon, 13 Nov 2017 20:30:39 +0300
Message-ID: <CAOVevU4OWvNgJdN8-S4YvrgnA2XPrzuU=wGE9zWvsphpCaxDNA_at_mail.gmail.com>



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
Received on Mon Nov 13 2017 - 18:30:39 CET

Original text of this message