SQL Query to Find Out Session wise PGA Usage

advertisements

_____________________________________________________________________________________________________________________

SQL Query

 

set lines 2000 Pages 200
col program for a40
col module for a40col username for a20
col USE_MD 99999.99
col name for a30
SELECT s.SID, s.program, s.module, s.username, b.NAME, ROUND(a.VALUE/(1024*1024),2) USE_MB FROM
v$sesstat a, v$statname b, v$session s
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# 
AND a.sid=s.sid order by ROUND(a.VALUE/(1024*1024),2) desc    

Sample Output - (Details are masked)

       SID PROGRAM                                  MODULE                                   USERNAME             NAME                               USE_MB
---------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------ ----------
      4404 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx         XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX         XXXXXXXXXXXXESS       session pga memory max            1200.83
      4440 DS/CSVD/XXXXXXXXXXXXXXXXXXX              DS/XXXXXXXXXn/XXXXXXXXXXXX1              XXXXXXXXXXXXXXX       session pga memory max            1200.83
      4408 DS/CSVD/XXXXXXXXXXXXXXXXXXX              DS/XXXXX/XXXXXXXXXXXXXXXXXX              ADMIN                 session pga memory max            1200.83

_____________________________________________________________________________________________________________________

Website Stats