I posted a similar question recently and whilst I received some answers I
never did discover an exact way of matching oracle use of memory with unix
use of memory.
One issue is that when oracle releases memory the unix process does not
automatically do the same until the memory is required. Therefore it is
likely that there will always be a difference.
Tim Gorman pointed me to a script oramem.sh (www.evdbt.com/tools.htm) which
does what you are doing with a pmap function which is quite useful
The discussion was around 4/10/02 and the thread title was "How much memory
is an oracle shadow process using"
John
-----Original Message-----
Sent: 03 December 2002 11:29
To: Multiple recipients of list ORACLE-L
chao_ping,
It seems i am posting a too long question, so nobody is
interested.
I post the answer to my second question, to check the memory
utilization in linux operation system.
It is in /proc/spid/status file.
Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.cnoug.org(Chinese Oracle User Group)
======= 2002-12-02 08:34:00 ,you wrote£º=======
>Hi,dba friends:
> I am thinking of measure how much memory per connection used, from
the os viewpoint and oracle viewpoint. And this is my result from my
production server, and i have some questions below.
>
>
>23:56:28 SQL> select sum(value),sum(value)/count(distinct sid) average
from v$sesstat where statistic#=15; --uga
>
> SUM(VALUE) AVERAGE
>------------ ------------
> 69,098,528 145,777
>
>23:58:09 SQL>select sum(value),sum(value)/count(distinct sid),max(value)
from v$sesstat where statistic#=20 --pga
>
> SUM(VALUE) SUM(VALUE)/COUNT(DISTINCTSID) MAX(VALUE)
>------------ ----------------------------- ------------
> 265,290,648 559,684.911 7,510,184
>
> There is about my production server(oracle817+solaris7), and I also
used pmap to trace some process and it look like:
>oracle@main-db1$pmap 11443 #some process id i which is choosed randomly
via /usr/ucb/ps -aux.
>11443: oraclebiddb (LOCAL=NO)
>0000000100000000 29440K read/exec
/export/home/oracle/app/product/8.1.7/bin/oracle
>0000000101DBE000 464K read/write/exec
/export/home/oracle/app/product/8.1.7/bin/oracle
>0000000101E32000 1440K read/write/exec [ heap ]
>0000000380000000 5685720K read/write/exec/shared [ shmid=0x65 ]
>FFFFFFFF7D800000 16K read/exec /usr/lib/sparcv9/libmp.so.2
>FFFFFFFF7D902000 8K read/write/exec /usr/lib/sparcv9/libmp.so.2
>FFFFFFFF7DA00000 88K read/exec /usr/lib/sparcv9/libm.so.1
>FFFFFFFF7DB14000 16K read/write/exec /usr/lib/sparcv9/libm.so.1
>FFFFFFFF7DC00000 8K read/exec /usr/lib/sparcv9/libkstat.so.1
>FFFFFFFF7DD00000 8K read/write/exec /usr/lib/sparcv9/libkstat.so.1
>FFFFFFFF7DE00000 32K read/exec /usr/lib/sparcv9/librt.so.1
>FFFFFFFF7DF06000 8K read/write/exec /usr/lib/sparcv9/librt.so.1
>FFFFFFFF7E000000 24K read/exec /usr/lib/sparcv9/libaio.so.1
>FFFFFFFF7E104000 16K read/write/exec /usr/lib/sparcv9/libaio.so.1
>FFFFFFFF7E200000 704K read/exec /usr/lib/sparcv9/libc.so.1
>FFFFFFFF7E3AE000 64K read/write/exec /usr/lib/sparcv9/libc.so.1
>FFFFFFFF7E3BE000 8K read/write/exec [ anon ]
>FFFFFFFF7E400000 8K read/exec /usr/lib/sparcv9/libsched.so.1
>FFFFFFFF7E500000 8K read/write/exec /usr/lib/sparcv9/libsched.so.1
>FFFFFFFF7E600000 32K read/exec /usr/lib/sparcv9/libgen.so.1
>FFFFFFFF7E706000 8K read/write/exec /usr/lib/sparcv9/libgen.so.1
>FFFFFFFF7E800000 40K read/exec /usr/lib/sparcv9/libsocket.so.1
>FFFFFFFF7E908000 16K read/write/exec /usr/lib/sparcv9/libsocket.so.1
>FFFFFFFF7EA00000 624K read/exec /usr/lib/sparcv9/libnsl.so.1
>FFFFFFFF7EB9A000 64K read/write/exec /usr/lib/sparcv9/libnsl.so.1
>FFFFFFFF7EBAA000 32K read/write/exec [ anon ]
>FFFFFFFF7EC00000 3896K read/exec
/export/home/oracle/app/product/8.1.7/lib/libjox8.so
>FFFFFFFF7F0CC000 192K read/write/exec
/export/home/oracle/app/product/8.1.7/lib/libjox8.so
>FFFFFFFF7F0FC000 8K read/write/exec [ anon ]
>FFFFFFFF7F300000 40K read/exec
/export/home/oracle/app/product/8.1.7/lib64/libdsbtsh8.so
>FFFFFFFF7F408000 8K read/write/exec
/export/home/oracle/app/product/8.1.7/lib64/libdsbtsh8.so
>FFFFFFFF7F40A000 8K read/write/exec [ anon ]
>FFFFFFFF7F500000 8K read/exec
/export/home/oracle/app/product/8.1.7/lib64/libskgxp8.so
>FFFFFFFF7F600000 8K read/write/exec
/export/home/oracle/app/product/8.1.7/lib64/libskgxp8.so
>FFFFFFFF7F680000 128K read/exec /usr/lib/sparcv9/ld.so.1
>FFFFFFFF7F790000 16K read/exec
/usr/platform/sun4u/lib/sparcv9/libc_psr.so.1
>FFFFFFFF7F79E000 8K read/write/exec /usr/lib/sparcv9/ld.so.1
>FFFFFFFF7F7A0000 8K read/write/exec [ anon ]
>FFFFFFFF7F7B0000 8K read/write/exec [ anon ]
>FFFFFFFF7F7C0000 8K read/write/exec [ anon ]
>FFFFFFFF7F7D0000 8K read/write/exec/shared [ anon ]
>FFFFFFFF7F7E0000 8K read/write/exec [ anon ]
>FFFFFFFF7F7F0000 8K read/exec /usr/lib/sparcv9/libdl.so.1
>FFFFFFFF7FFEE000 72K read/write [ stack ]
> total 5723336K
>
>and i compared it with oracle statistics:
>00:11:59 SQL> @whoisit
>00:12:02 SQL> col machine format a30
>00:12:02 SQL> col program format a40
>00:12:02 SQL> set line 200
>00:12:02 SQL> select sid,serial#
,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd
hh24:mi:ss')
>00:12:02 2 from v$session where paddr in(
>00:12:02 3 select addr from v$process where spid in(&spid));
>Enter value for spid: 11443
>old 3: select addr from v$process where spid in(&spid))
>new 3: select addr from v$process where spid in(11443))
>
> SID SERIAL# USERNAME OSUSER
MACHINE PROGRAM
PROCESS TO_CHAR(LOGON_TIME,
>---------- ---------- ------------------------------
------------------------------ ------------------------------
> 447 27693 BIDDER domain2
appc ? @appc (TNS V1-V3) 15068 2002/11/20
17:51:38
>
>1 row selected.
>
>Elapsed: 00:00:00.06
>00:12:04 SQL> @sesstat
>00:12:09 SQL> set line 200
>00:12:09 SQL> col name format a50
>00:12:09 SQL> select a.value,b.name
>00:12:09 2 from v$sesstat a,v$statname b
>00:12:09 3 where a.sid=&sid
>00:12:09 4 and a.statistic#=b.statistic#
>00:12:09 5 and b.name like 'memory'
>00:12:09 6 /
>Enter value for sid: 447
>old 3: where a.sid=&sid
>new 3: where a.sid=447
>
> VALUE NAME
>---------- --------------------------------------------------
> 174472 session uga memory
> 965696 session uga memory max
> 1238664 session pga memory
> 1238664 session pga memory max
>
>
>And the following is my question:
>1. what does the annon mean? It seems that there is always 9 anon area and
the sum of it is always 72KB(the most below line), are they the same? (I
just noticed that the max pga connection user ora_snp have 152K stack area,
and 26 anon lines,but the sum of anon lines is 232KB?).
>2. Does it mean that this process used 1440K memory of the unix machine?
All other library is shared among all oracle connections?
>3. I compared it with Oracle statistics
> From pmap: 1440K heap area
> From Oracle v$sesstat: 1238664
> I also checked some other process, these two values are always
near,pmap heap size slightly larger than v$sesstat.
> So, is the pmap result more accurate? How to caculate the accurate
memory usage of this connection? Shall i add the stack area, anon area or
even other library area(though i think we do not)?
> If the v$sesstat result is nearly accurate, can i get the conclusion
that the total connection's memory allocation is just less than
300M?(sum(value) from v$sesstat where statistic#=20),even if i add the stack
area size, it is far below 1GB.
>
>oracle@main-db1$prtmem
>
>Total memory: 11904 Megabytes
>Kernel Memory: 353 Megabytes
>Application: 6552 Megabytes
>Executable & libs: 80 Megabytes
>File Cache: 4550 Megabytes
>Free, file cache: 367 Megabytes
>Free, free: 3 Megabytes
>00:28:04 SQL> show sga
>
>Total System Global Area 5821238972 bytes
>Fixed Size 102076 bytes
>Variable Size 452149248 bytes
>Database Buffers 5368709120 bytes
>Redo Buffers 278528 bytes
> Does the 6552M(from prtmem) = 5550M(SGA)+1GB(connections process
memory, and executable like oracle and tns and other lib.) //this is
dedicated database server.
> Please share your opinions,thanks.
> A last question, if on linux, without pmap, how to measure it?
>
>
>
>Regards
>zhu chao
>Eachnet DBA
>86-21-32174588-667
>[EMAIL PROTECTED]
>www.cnoug.org(Chinese Oracle User Group)
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: chao_ping
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
= = = = = = = = = = = = = = = = = = = =
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: chao_ping
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
