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).
