Help - Search - Members - Calendar
Full Version: RAM size used by oracle process - UNIX
Oracle DBA Forums > Oracle > Oracle Forum
Jacver57
Hi there,

I would like to know how I can have a good idea of memroy use by all oracle processes in the unix AIX environnement.
By processes, I mean client connections (local=yes and local=no).

I also need this info on Solaris environment.

Thanks in advance.
burleson
Hi,

>> I would like to know how I can have a good idea of memroy use by all oracle processes in the unix AIX environnement.

At the AIX level, you can see the RAM with "ipcs -pmb", but that's just the SGA region. Since PGA RAM is local to the session, you could use SMIT (/usr/bin/smit) to see the RAM used by all Oracle processes, at that exact moment.

In Solaris, you can see RAM usage with "prtconf|grep -i mem".

You will find this article helpful:

http://www.dba-oracle.com/art_dbazine_ram.htm

Inside Oracle, you can see the RAM used at the SQL level:

http://www.dba-oracle.com/oracle_tips_sql_resource.htm

CODE
select
   to_number(decode(SID, 65535, NULL, SID)) sid,
   operation_type              OPERATION,
   trunc(WORK_AREA_SIZE/1024)  WSIZE,
   trunc(EXPECTED_SIZE/1024)   ESIZE,
   trunc(ACTUAL_MEM_USED/1024) MEM,
   trunc(MAX_MEM_USED/1024)    "MAX MEM",
   number_passes               PASS
from
   v$sql_workarea_active
order by
   1,2;


If you can join v$sql_workarea_active into v$session, you can get the RAM used by a specific SQL in a specific session from 'session uga memory max'. Try this query:

CODE
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
/
aussie_dba
"I would like to know how I can have a good idea of memroy use by all oracle processes in the unix AIX environnement"

Memroy? This question is vague and unclear.

Oracle processes allocate and free RAM continuously, so it's not clear what you want a "good idea" of:

- The high-water mark of allocated RAM for a UNIX process?
- Physical RAM or virtual memory?
- The total amount of RAM used by a process?

Also, note that Oracle processes use RAM from many places:

- The pre-alocated RAM within the SGA.
- Native heap RAM at process start up time.
- PGA RAM usage for sorting and hash joins.

To see the address space used by a process the "pmap" command displays the resident, shared and pivate memory for the named process.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.