Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

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

Answered by: Tom Kyte - Last updated: June 01, 2009 - 1:50 pm UTC

Category: Database - 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 we 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


and you rated our response

  (8 ratings)

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

Reviews

Better Question

May 13, 2009 - 5:07 pm UTC

Reviewer: David from Alpharetta, Ga

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

Followup  

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

May 13, 2009 - 5:15 pm UTC

Reviewer: Nags from Boston, USA

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

Followup  

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

May 13, 2009 - 5:24 pm UTC

Reviewer: Nags from Boston, USA

Thanks..

Thank : Q

May 13, 2009 - 8:06 pm UTC

Reviewer: A reader

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

Followup  

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

May 13, 2009 - 8:23 pm UTC

Reviewer: A reader


May 13, 2009 - 10:09 pm UTC

Reviewer: Dmitry from Russia

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

Followup  

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?

May 29, 2009 - 9:11 am UTC

Reviewer: Sandro from Italy

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

Followup  

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

May 21, 2014 - 1:37 pm UTC

Reviewer: Laurentiu O. from Romania

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.