Memory Usage Of SQL Query On PGA

Buğra PARLAYAN June 21, 2023 ORACLE

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;

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Memory Usage Of SQL Query On PGA

About Buğra PARLAYAN

Leave a Reply Cancel reply