What is the better session memory used stat; UGA or PGA?
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Oct 2005 13:26:00 -0400
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
Other related posts:
