Better Question
David, May 13, 2009 - 5:07 pm UTC
A more interesting question to ask would have been "How much less memory would I need if I consolidated my 5 databases into 1?"
May 13, 2009 - 5:15 pm UTC
very very very good point.
followed up by "and now you can actually try to tune and give SLA's - with 5 instances on a single host, you can do *neither*"
Can you please explain this Result
Nags, May 13, 2009 - 5:15 pm UTC
I ran the query on my database and got this result
NM MB
----- ----------
pga 452909
sga 8192
total 461101
But, the total memory on my box is only 16Gig. Even if I include paged memory, this is a lot more. What could be the issue?
I am using 10.2.0.4.0 on sun solaris box.
May 13, 2009 - 5:22 pm UTC
my fault, used the wrong v$ view...
ops$tkyte%ORA11GR1> select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
2 from
3 (
4 select 'sga' nm, sum(value) val
5 from v$sga
6 union all
7 select 'pga', sum(a.value)
8 from v$sesstat a, v$statname b
9 where b.name = 'session pga memory'
10 and a.statistic# = b.statistic#
11 )
12 group by rollup(nm)
13 /
NM MB
----- ----------
pga 30
sga 511
total 541
Now it makes sense
Nags, May 13, 2009 - 5:24 pm UTC
Thanks..
Thank : Q
A reader, May 13, 2009 - 8:06 pm UTC
Thank you so much.
0)So the Host OS total for that DB should be same as SGA + PGA from your query
1)Does the SGA and PGA also include Memory used by Client Processes which show as LOCAL = No on the Host OS.
2)How do I find memory consumed by each Entry in V$session
May 13, 2009 - 8:09 pm UTC
0) it should be close, there is stack memory and other bits of process memory we don't count as pga ourselves, the true "OS" number should be a little higher.
2) yes
3) the SGA is a shared resource, you would not count it against a session. And the inline view I have for pga memory shows you how to get pga memory by session - it is from v$sesstat (session statistic), look at that view.
memory
A reader, May 13, 2009 - 8:23 pm UTC
Dmitry, May 13, 2009 - 10:09 pm UTC
Tom, in my systems it`s not so close to OS stats.
In solaris 9 I used shell script which count memory usage by "pmap -x" calls. I see:
$ ksh oramem.sh primdev
Total RAM = 2560Mb, Swap = 3514.54Mb used, 1420.16Mb free
Total memory consumption by Oracle instance "primdev":
PID Command Shm Priv
=== ======= === ====
25910 ora_mman_primdev 338848 1416
25908 ora_pmon_primdev 338880 2736
25922 ora_cjq0_primdev 340888 1768
25918 ora_smon_primdev 340568 1488
25914 ora_lgwr_primdev 349936 3240
25916 ora_ckpt_primdev 339464 3304
25912 ora_dbw0_primdev 341544 5624
25941 ora_mmon_primdev 340440 1520
25958 oracleprimdev 341200 1808
25931 ora_qmnc_primdev 338888 1384
22826 oracleprimdev 339192 1624
28722 ora_q000_primdev 338944 1448
25920 ora_reco_primdev 339296 1480
25943 ora_mmnl_primdev 338976 1616
26201 oracleprimdev 340480 1712
# Procs # Procs Max Sum
Foregrnd Backgrnd Shm Kb Priv Kb Total Kb
======== ======== ====== ======= ========
3 12 349936 32168 382104
from inside DB:
system@primdev> /
NM MB
----- ----------
pga 27
sga 240
total 267
I think that OS method more precise. It`s accordance with sar, top... In linux system I see huge difference: sar say 3,9Gb used but you script say only 1,7Gb total. It`s interest for me what part is absent in sql report?
May 14, 2009 - 10:24 am UTC
you are almost certainly double counting shared and shareable memory. I don't know what your OS tool is reporting as "private". Define what priv memory is (by quoting documentation, not your understanding of what it *should probably* be)
Do you see how it says "total ram = 2560mb", and 1420mb free? That means 1.1gb used doesn't it?
As a point of reference see
http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html determining the actual memory used by a process is (for whatever reason) exceedingly difficult and error prone. Unless you can reconcile the total ram and free numbers, I'll tend to not trust the other numbers either - what exactly is that "priv kb"
I ran that script on linux. My database says "21mb" of pga allocated. oramem.sh said "872kb". Now what? I *know* 872kb is wrong.
Why not uga?
Sandro, May 29, 2009 - 9:11 am UTC
Hi Tom, in your statement you do not consider UGA, why?
This is my review:
select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
from
(
select 'sga' nm, sum(value) val
from v$sga
union all
select 'uga', sum(a.value)
from v$sesstat a, v$statname b
where b.name = 'session uga memory'
and a.statistic# = b.statistic#
union all
select 'pga', sum(a.value)
from v$sesstat a, v$statname b
where b.name = 'session pga memory'
and a.statistic# = b.statistic#
)
group by rollup(nm)
June 01, 2009 - 1:50 pm UTC
because the UGA is either
a) already included in the PGA memory, the UGA is a subset of the PGA when you connect via dedicated server
b) part of the SGA, not in the process at all, when you connect with shared server
so you are either
a) double counting the memory if you are dedicated server
b) counting memory that is already counted in the SGA if you are shared server.
either way - you would be double counting.
delta between os memory and sql resulted value
Laurentiu O., May 21, 2014 - 1:37 pm UTC
Hello Tom,
Is there any method to determin the value between the sql resulted total memory and os physical memory for oracle.exe process by using sql scripts? If not, how can i compute this delta and determin who is consuming it?
For example, for my database:
Result from your script:
NM MB
----- ----------
pga 394
sga 5097
total 5491
Result from all OS monitoring tools:
Physical memory working set 5700.7 MB (for the oracle.exe process).
Delta = 5700.7 - 5491 ?
Thank you in advance.