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.htmInside Oracle, you can see the RAM used at the SQL level:
http://www.dba-oracle.com/oracle_tips_sql_resource.htmCODE
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
/