Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: May 13, 2009 - 4:21 pm UTC

Last updated: June 01, 2009 - 1:50 pm UTC

Version: 9208 ,11107

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have 5 databases running on HPUX box having 16GB Ram

How to find the total memory consumed by each of these Databases
From the
1) OS ( may be some ps -ef dont know )
2) Database ( I did show sga not sure if that is the correct way)


Thanx

and Tom said...

1) for that - ask your HP/UX admin. It is OS specific. But, it should vary too much from....

2)

  1  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(value)
  8    from v$sysstat
  9   where name = 'session pga memory'
 10  )
 11*  group by rollup(nm)
ops$tkyte%ORA10GR2> /

NM            MB
----- ----------
pga           27
sga          512
total        539


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?"
Tom Kyte
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.
Tom Kyte
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

Tom Kyte
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?

Tom Kyte
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)

Tom Kyte
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.