What is the better session memory used stat; UGA or PGA?

From: Marquez, Chris <cmarquez_at_collegeboard.org>
Date: Tue, 25 Oct 2005 13:26:00 -0400
Message-ID: <B30C2483766F9342B6AEF108833CC84E05BD6029@ecogenemld50.Org.Collegeboard.local>

ENV;

• 9205
• Dedicated Server Mode.

UGA is part of the PGA, and PGA part of the SGA. Which is the better stat for session memory (currently) used? I think and am finding UGA as the answer...but don't want to ignore the higher PGA number I see, considering what the PGA holds.

SELECT

	e.SID,
	e.username,
	e.status,
	a.UGA_MEMORY,
	b.PGA_MEMORY

FROM
-- Current UGA size for the session.

(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' UGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session uga memory') a,
-- Current PGA size for the session.

(select y.SID, TO_CHAR(ROUND(y.value/1024),99999999) || ' KB' PGA_MEMORY from v$sesstat y, v$statname z where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b,

	v$session e
WHERE	e.sid=a.sid
AND	e.sid=b.sid
ORDER BY
	e.status,
	a.UGA_MEMORY desc

/

       SID USERNAME      STATUS                   UGA_MEMORY           PGA_MEMORY
---------- ------------- ------------------------ -------------------- --------------------
       190 SCOTT         ACTIVE                       14723 KB             17243 KB
        25 SYS           ACTIVE                        2205 KB               401 KB
        80 WEBUSERS      ACTIVE                         422 KB              1616 KB
        50               ACTIVE                          37 KB             11532 KB
       222               ACTIVE                          37 KB              8412 KB
       169               ACTIVE                          37 KB              6370 KB
       217               ACTIVE                          36 KB               115 KB
        16               ACTIVE                          36 KB              4255 KB
         3               ACTIVE                          36 KB              2030 KB
        15               ACTIVE                          36 KB              4255 KB
        19               ACTIVE                          36 KB              1366 KB
         1               ACTIVE                          36 KB               115 KB
        11               ACTIVE                          35 KB              3443 KB
         9               ACTIVE                          34 KB              1558 KB
         2               ACTIVE                          33 KB               208 KB
         4               ACTIVE                          33 KB              2951 KB
         5               ACTIVE                          33 KB              3918 KB
        10               ACTIVE                          33 KB              8439 KB
         7               ACTIVE                          33 KB              3918 KB
       133 WEBUSERS      INACTIVE                      1653 KB              2546 KB
        61 WEBUSERS      INACTIVE                      1422 KB              2336 KB
       194 WEBUSERS      INACTIVE                       565 KB              1901 KB
       154 WEBUSERS      INACTIVE                       511 KB              1902 KB
        22 WEBUSERS      INACTIVE                       491 KB              1853 KB

Thanks,

Chris Marquez
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 25 2005 - 13:16:00 CDT