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

  • From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
  • To: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • Date: Mon, 13 Nov 2017 20:30:39 +0300

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

Other related posts: