Memory Usage Of SQL Query On PGA

Buğra PARLAYAN

1 year ago

In today’s article, I’ll be explaining how to find the amount of Memory Usage Of SQL Query On PGA.

We can find out using the following query.

SELECT NVL (a.username, '(oracle)') AS USER,
         a.module                   AS MODULE,
         a.program                  AS PROGRAM,
         a.SID,
         TRUNC (b.VALUE / 1024)     AS memory_kb,
         sql_id,
         LOGON_TIME                 AS SESSION_LOGON,
         PREV_EXEC_START            AS "LAST EXECUTED "
    FROM v$session a, v$sesstat b, v$statname c
   WHERE     a.sid = b.sid
         AND b.statistic# = c.statistic#
         AND c.name = 'session pga memory'
         AND a.program IS NOT NULL
         AND SQL_ID = 'xxxxxxxxxxxx'
ORDER BY b.VALUE DESC;

SELECT NVL (a.username, '(oracle)') AS USER,

a.module AS MODULE,

a.program AS PROGRAM,

a.SID,

TRUNC (b.VALUE / 1024) AS memory_kb,

sql_id,

LOGON_TIME AS SESSION_LOGON,

PREV_EXEC_START AS "LAST EXECUTED "

FROM v$session a, v$sesstat b, v$statname c

WHERE a.sid = b.sid

AND b.statistic# = c.statistic#

AND c.name = 'session pga memory'

AND a.program IS NOT NULL

AND SQL_ID = 'xxxxxxxxxxxx'

ORDER BY b.VALUE DESC;