SQL Query to Find Out Session wise PGA Usage


The Eucharistic Miracles of the World
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



Post a comment


acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer