Re: Memory used by a single oracle server process in Solaris

You're probably better off looking at v$process, which reports allocated, max allocated, and used memory. (The various views telling you about process memory are not completely consistent, so a second opinion may help.)

Example of use at http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/ (The bug mentioned in the note does not apply to your version of Oracle.)

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

• Original Message ----- From: "Sreejith S Nair" <Sreejith.Sreekantan_at_ibsplc.com> To: <oracle-l_at_freelists.org> Sent: Tuesday, August 23, 2011 10:21 AM Subject: Memory used by a single oracle server process in Solaris

> SQL> SELECT
> 2 s.sid sid
> 3 , lpad(s.username,12) oracle_username
> 4 , lpad(s.osuser,9) os_username
> 5 , s.program session_program
> 6 , lpad(s.machine,8) session_machine
> 7 , (select ss.value from v$sesstat ss, v$statname sn
> 8 where ss.sid = s.sid and
> 9 sn.statistic# = ss.statistic# and
> 10 sn.name = 'session pga memory') session_pga_memory
> 11 , (select ss.value from v$sesstat ss, v$statname sn
> 12 where ss.sid = s.sid and
> 13 sn.statistic# = ss.statistic# and
> 14 sn.name = 'session pga memory max') session_pga_memory_max
> 15 , (select ss.value from v$sesstat ss, v$statname sn
> 16 where ss.sid = s.sid and
> 17 sn.statistic# = ss.statistic# and
> sn.name = 'session uga memory') session_uga_memory
> 18 19 , (select ss.value from v$sesstat ss, v$statname sn
> 20 where ss.sid = s.sid and
> 21 sn.statistic# = ss.statistic# and
> 22 sn.name = 'session uga memory max') session_uga_memory_max
> 23 FROM
> 24 v$session s
> 25 WHERE s.sid=&1
> 26 ORDER BY session_pga_memory DESC
> 27 /
> Enter value for 1: 520
> old 25: WHERE s.sid=&1
> new 25: WHERE s.sid=520
>
> SID Oracle User O/S User Session Program Machine PGA Memory PGA
> Memory Max UGA Memory UGA Memory MAX
> ---- ------------ --------- ------------------ -------- --------------
> -------------- -------------- --------------
> 520 APP_USER_MB appuser JDBC Thin Client zone2 76,082,400
> 229,895,392 68,541,416 215,665,464
>

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