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----- Original Message ----- From: "Sreejith S Nair" <Sreejith.Sreekantan@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, August 23, 2011 10:21 AM Subject: Memory used by a single oracle server process in Solaris
Hi Friends, We have memory free very less in one of our Solaris 10 server running Oracle 11g R2. The memory free will be sometimes 2 GB out of 24 GB. I was looking at the memory consumption. ps -efo pmem,rss,pid,pcpu,args | sort -r | head -20 19.3 4730640 27430 0.0 oracleTBDB (LOCAL=NO) 19.3 4727152 5282 0.0 oracleTBDB (LOCAL=NO) 19.3 4724896 5362 0.0 oracleTBDB (LOCAL=NO) 19.3 4723324 27422 0.0 oracleTBDB (LOCAL=NO) 19.3 4722212 5346 1.0 oracleTBDB (LOCAL=NO) .... Let's take the first PID =27430. This process was reported to hold 4730640 KB, which means 4.7 GB. I know this includes shared memory. So I gave a pmap for this. pmap -x 27430 | grep -v 000 ibsjpsrv3 oracle [TBDB]:pmap -x 27430 | grep -v 000 27430: oracleTBDB (LOCAL=NO) Address Kbytes RSS Anon Locked Mode Mapped File ---------------- ---------- ---------- ---------- ---------- total Kb 5592364 4901764 92792 - pmap -x 27430 | grep shmid ibsjpsrv3 oracle [TBDB]:pmap -x 27430 | grep shmid 0000000060000000 5244928 4710400 - - rwxs- [ dism shmid=0x14 ] As per metalink document [ SOLARIS: Determining Background Process Size using pmap [ID 107750.1]], the memory used by a BG process, here it is not a BG process but a server connection. shared memory= 4710400 I was thinking how I can derive memory used by this process alone. So RSS - shared memory = PGA + (code + data ) ? If Yes then PGA + (code + data ) = 4901764 - 4710400 = 191364 kb = 186 MB. Let's see the PGA used by this from database. SQL> select s.sid from v$session s ,v$process p where p.addr=s.paddr and p.spid='&1'; Enter value for 1: 27430 old 1: select s.sid from v$session s ,v$process p where p.addr=s.paddr and p.spid='&1' new 1: select s.sid from v$session s ,v$process p where p.addr=s.paddr and p.spid='27430' SID ---------- 520 SQL> SET LINESIZE 145 SQL> SET PAGESIZE 9999 SQL> SQL> COLUMN sid FORMAT 999 HEADING 'SID' SQL> COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right SQL> COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right SQL> COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC SQL> COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC SQL> COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory' SQL> COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max' SQL> COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory' SQL> COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX' SQL> 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 Here I got PGA as 76 MB. Can i relate this to my previous calculation which has PGA + (code + data ) = 186 MB ? I would like to have a second opinion on this to confirm whether this is correct, or is there any better way to see / tackle high memory consumption ? Cheers, Sreejith DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." ----- No virus found in this message. Checked by AVG - www.avg.comVersion: 10.0.1390 / Virus Database: 1520/3851 - Release Date: 08/22/11
-- //www.freelists.org/webpage/oracle-l