Memory used by a single oracle server process

https://community.oracle.com/thread/2274306

Memory used by a single oracle server process

This question is Not Answered.

Sreejith_Nair Aug 23, 2011 4:09 AM

We have memory free very less in one of our Solaris 10 server running Oracle 11g R2 (not cluster ). 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.

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          -

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 menmory consumption ?


Cheers,
Sreejith

• 1281 Views
• Tags:

Average User Rating: No ratings (0 ratings)

Average User Rating

No ratings

(0 ratings)

• 1. Re: Memory used by a single oracle server process

  

  Jonathan Lewis Aug 23, 2011 5:50 AM (in response to Sreejith_Nair)

  SSNair wrote:

  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  /
  
  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

  I've just posted a response on Oracle-L, but for the benefit of readers here I'll just repeat that you're probably better off looking at v$process because that shows the max allocated, allocated, and used - which may capture your discrepancy. (v$process_memory then gives a breakdown of the use by process).
  
  Regards
  Jonathan Lewis

  • Like Show 0 Likes(0)
  • Actions 

• 2. Re: Memory used by a single oracle server process

  

  Sreejith_Nair Aug 23, 2011 5:20 AM (in response to Jonathan Lewis)

  Hi Jonathan,
  
  Thank you so much for the hint.
  
  Checking the views you suggested shows me this

  SQL> select PGA_USED_MEM/1024/1024 used_mb,PGA_ALLOC_MEM/1024/1024 alloc_mb,PGA_FREEABLE_MEM/1024/1024 free_mb,PGA_MAX_MEM/1024/1024 max_mb from v$process where spid=27430;
  
     USED_MB   ALLOC_MB    FREE_MB     MAX_MB
  ---------- ---------- ---------- ----------
  71.9989109  87.611393     12.625 220.923893
  
  SQL> select CATEGORY,ALLOCATED/1024/1024 allocated_mb,USED/1024/1024 USED_MB,MAX_ALLOCATED/1024/1024 max_mb from  v$process_memory where pid=114;
  
  CATEGORY        ALLOCATED_MB    USED_MB     MAX_MB
  --------------- ------------ ---------- ----------
  SQL               .136795044      .004920959 153.053238
  PL/SQL            60.8475571     .006774902 66.7705688
  JAVA              4.70097351      4.69393921 6.37259674
  Freeable              12.625               0
  Other             9.30106735           9.30106735

  This comes close to what I got from 'session_pga_memory' which is close to 71 MB.
  
  Still, I did not get the memory I derived from this calculation .
  
  shared memory= 4710400 ( shmid from pmap )
  RSS = 4901764 from pmap and ps .
  
  So RSS - shared memory = PGA used by this process + (code + data ) ?
  
  If Yes then PGA + (code + data ) = 4901764 - 4710400 = 191364 kb = 186 MB.
  
  I am getting PGA as 71 MB, so rest ( 186 - 71 ) is the memory for code + data ?
  
  I suspect, I might be missing something here ?
  
  Kind Regards,
  Sreejith
  
  Edited by: SSNair on Aug 23, 2011 3:20 AM

  • Like Show 0 Likes(0)
  • Actions 

• 3. Re: Memory used by a single oracle server process

  

  Jonathan Lewis Aug 23, 2011 5:52 AM (in response to Sreejith_Nair)

  SSNair wrote:
  Hi Jonathan,
  
  I am getting PGA as 71 MB, so rest ( 186 - 71 ) is the memory for code + data ?
  
  I suspect, I might be missing something here ?
  

  I am not extremely familiar with pmap - but you mention (code + data) - won't pmap be reporting the code size for the Oracle executable ? If so doesn't that account for something lik 100MB for you version and platform ?
  
  You could post the whole pmap output for this process.
  
  Regards
  Jonathan Lewis