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

• 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

> 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.com
> Version: 10.0.1390 / Virus Database: 1520/3851 - Release Date: 08/22/11

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