For UP-UX, try glance (if installed)
[which may not be as handy as pmap on Solaris or svmon (or 'ps vg') on AIX, especially if one needs to know the whole memory of Oracle processes]

For Linux, view the file /proc/<pid>/status

Thanks Tom!

Hi Tom,

I need to define how much memory we need to configure in a unix box in order to support an oracle database that we will be moving to this new box. Is it correct to say that the total RAM we need to have available in the new unix box is the sum of the (SGA + v$sysstat.session pga memory max) of the database we need to move?

Thanks in advance for your help on this.

Hi Tom,

Thanks for answering. I understand what you said about being careful with what I take as a baseline for the SGA size, in our case the SGA has been set up correctly so we have an accurate number for the SGA.

I have another question regarding this formula:
(SGA + sum(pga memory max) + some_extra_for_good_luck_and_the_OS/other_stuff).
Is there any other checks that I should do in Unix (like using pmap) that will report memory used by the oracle processes and that they need to be added to that formula ? Or is that formula taking into account all the memory that needs to be available in the Unix box to support the OS and the Database activity (based of course on that the SGA was size correctly)?

Thanks for any help on this.

Hi Tom,

I am using Hp-Ux 11.23 Itanium with SGeRAC(11.23) Oracle 9.2.0.6 RAC (2node) and got 8 GB of memory installed on both system. I am facing out of memory issue on HP-UX.
My SGA size is 2.3 GB, when i connect with 400 session system goes out of memory at O.S level and shows 100% memory utilization which mean 5.7 GB memory used by 400 user session. but when i try oracle views to check how much memory its using 500MB in total( uga and pga memory+ workarea memory ) for all 400 session. where is this rest of memory gone. Can you please suggest me some kernel or O.S level parameter to fix this high memory allocation at O.S level or any workaround to fix this issue.
I connect more then 400 session once i hit the memory limition and new connection fail with listener error

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:proto adapter error
TNS-00510: Internal limit restriction exceeded
HPUX Error: 12: Not enough space

maxuproc is 4000. nfiles 65536.

All kernel parameters are optimized as per hp and oracle recommendation.

If you want any further information i will be happy to provide.

Thanks in advance.

This should give the PGA memory used by sessions whcih might help you to find which session is consuming most memory ( at the bottom)
col value format 999999999999999999

select
ssst.sid,
stn.name ,
round(ssst.value/1024/1024,2) memory ,
USERNAME,
COMMAND,
OSUSER,
MACHINE,
TERMINAL,
PROGRAM ,
ssst.STATISTIC#
from v$statname stn,v$sesstat ssst , v$session ses
where stn.STATISTIC# = ssst.STATISTIC# and
ssst.sid = ses.sid and
name like 'session%pga%memory%'
order by 3 asc ;

Hi Tom,

i am using hp-UX and to check physical memory usages i am using swapinfo -a which shows swap and physical memory used on system.
once i start the instance with size 2.3GB it shows me 45% memory is used in total (sga+ memory used by os and rest process).

once i connect say 300 session its grows up to 100% in swapinfo -a command and then i got the above errors. we are sure memory is not used by process as oracle views report few mb used by all process in total ,which means memory is allocated but its not used.


Yes i am working with support on this issue and but didn't get any satisfactory reply till now.

any clue or workaround will be helpful.

thanks

Hi Tom,

From v$sesstat we can get the memory used by all currently connected sessions. Is there any way to find out the Memory used by all sessions for every snapshot id (from AWR)? I am using oracle 10.2.0.3. I tried to get this information from AWR but not getting it correct.

Thanks,
Deepak

Hi Tom,

Yes that's what I want "memory used by all sessions" at particular point and I guess I have asked the same, memory utilization by Snap Id. I might not be clear in asking it. Sorry about that.

We had a crash recently, and OS memory utilization was 100% during that time. I wanted to find out what was the memory used by all sessions at that time (for closest snapshot id).

Thanks,
Deepak

Hi,

Continuing from above. Actually when I joined dba_hist_sysstat with dba_hist_snapshot for below four stat values, I am getting output as below. I am not sure I am looking at correct values as I can see SESSION_UGA_MEMORY_MAX = 1004882485784 for one snapid which seems to be very huge.

Am I looking at correct statistics? Please suggest.

select
to_char(sn.end_interval_time,'yyyy-mm-dd_HH24') Snap_Time,
s1.value session_pga_memory,
s2.value session_pga_memory_max,
s3.value session_uga_memory,
s4.value session_uga_memory_max
from
dba_hist_sysstat s1,
dba_hist_sysstat s2,
dba_hist_sysstat s3,
dba_hist_sysstat s4,
dba_hist_snapshot sn
where
s1.snap_id = sn.snap_id
and s2.snap_id = sn.snap_id
and s3.snap_id = sn.snap_id
and s4.snap_id = sn.snap_id
and s1.stat_name = 'session pga memory'
and s2 2 .stat_name = 'session pga memory max'
and s3.stat_name = 'session uga memory'
and s4.stat_name = 'session uga memory max'
order by 1
;

SNAP_TIME "SESSION PGA "SESSION_PGA_ "SESSION_UGA "SESSION_UGA
MEMORY" MEMORY_MAX" MEMORY" MEMORY_MAX"
------------- --------------- -------------- --------------- -------------
2008-09-04_21 3576753408 13589593952 8617053632328 921290361440
2008-09-04_22 3468840896 13562125536 8672969531480 927063917304
2008-09-04_23 3549870592 13759661184 8729042717240 932863500608
2008-09-05_00 3568166688 13927655872 8785057777192 938789332064
2008-09-05_01 3590611040 13946971552 8836787503472 944218545336
2008-09-05_02 3681178208 14077532736 8892888186304 949640746080
2008-09-05_03 3581758944 14110496192 8953103040608 954880665536
2008-09-05_04 3619144480 14155173280 9009122212224 960372838872
2008-09-05_05 3636057152 14226119040 9065148805776 965821025280
2008-09-05_06 3677128384 14449887584 9121173839696 971502262976
2008-09-05_07 3705611456 14499589952 9177191146664 977340468360
2008-09-05_08 3723289952 14443885184 9237484630408 987379379048
2008-09-05_09 3848603008 14727350976 9293584834216 993484634632
2008-09-05_10 4143844320 15152470272 9349662901480 999220106168
2008-09-05_11 4096556832 15085570464 9405666436712 1004882485784
2008-09-05_12 4181704928 15282656736 9461715456704 1010470352016
2008-09-05_13 4114055968 15258538048 9517745203920 1016116029784
2008-09-05_14 4145635232 15383523168 9573773874112 1021741477112
2008-09-05_15 4178269504 15472945728 9629812056584 1027423406944
2008-09-05_16 4172291744 15530617792 9685830856576 1032899398328
2008-09-05_17 4152512448 15522159168 9741816658712 1038271769424



Thanks,
Deepak

Thanks Tom,

I understood my mistake and I got when you said it is point in time value and AWR covers range of values. For more clarity and close to accurate figure I am getting this value from v$sesstat and v$session view periodically. After plotting graph of these observations for fair amount of days, I could analyze the process memory usage more correctly.

Thanks again.

Hi Tom,
I am a fan of your books and posts, I saw one post (deepak's) in this thread that to find out the "session uga memory" and "session pga memory" we should not use the tables like dba_hist_sysstat ( if I have understood his post), He wrote "For more clarity and close to accurate figure I am getting this value from v$sesstat and
v$session view periodically. After plotting graph of these observations for fair amount of days, I could analyze the process memory usage more correctly." But Tom, what if we wanted to plot the graphs for last 72 hours trend ? and my DB is restarted becoz of some issues/planned shutdown...then V$session and V$sesstat is get reset... so at that time what to do ? I can plot the data by using below query but it will not give us the correct values , right ?
<<<select to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi') end_time,
(s1.value/1024/1024) session_pga_memory,
(s2.value/1024/1024)/3600 session_uga_memory
from
dba_hist_sysstat s1,
dba_hist_sysstat s2,
dba_hist_snapshot sn
where
s1.snap_id = sn.snap_id
and s2.snap_id = sn.snap_id
and s1.stat_name = 'session pga memory'
and s2.stat_name = 'session uga memory'
order by to_char(sn.end_interval_time,'yyyy-mm-dd hh24:mi')
/>>
Please suggest ... and thanks again.
from INDRANIL

http://ejemplosprogramacionc.blogspot.com/2013/06/memoria-de-procesos-oracle-sobre.html