Find SQL Statements behind Oracle Error "ORA-00060 Deadlock Detected"

===========================================
General
===========================================
Application is reporting Deadlock.
How to find the offending SQL Statements?

===========================================
Steps
===========================================

A. Check alert.log

B. Check trace file

C. Get the full SQL Text from DB

===========================================

A. Check alert.log

===========================================

cd $ORACLE_BASE/diag/rdbms/orainst/orainst/trace/

less alert_orainst.log

Tue Feb 09 20:58:42 2016

ORA-00060: Deadlock detected. More info in file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

===========================================

B. Check the trace file

===========================================

less /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

Trace file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_ora_1666.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      hostname

Release:        2.6.18-274.el5

Version:        #1 SMP Fri Jul 8 17:36:59 EDT 2011

Machine:        x86_64

Instance name: orainst

Redo thread mounted by this instance: 1

Oracle process number: 146

Unix process pid: 1666, image: oracle@hostname

*** 2016-02-09 20:58:42.290

*** SESSION ID:(503.44168) 2016-02-09 20:58:42.290

*** CLIENT ID:() 2016-02-09 20:58:42.290

*** SERVICE NAME:(SYS$USERS) 2016-02-09 20:58:42.290

*** MODULE NAME:(JDBC Thin Client) 2016-02-09 20:58:42.290

*** ACTION NAME:() 2016-02-09 20:58:42.290

*** TRACE FILE RECREATED AFTER BEING REMOVED ***

*** 2016-02-09 20:58:42.290

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00030012-0006f5ba       146     503     X             91     572           X

TX-00070003-00070332        91     572     X            146     503           X

session 503: DID 0001-0092-000019CC     session 572: DID 0001-005B-0000010B 

session 572: DID 0001-005B-0000010B     session 503: DID 0001-0092-000019CC 

rows waited on:

  Session 503: obj - rowid = 00003940 - AAAViGAALAADe0xAAf

  (dictionary objn - 14656, file - 11, block - 912689, slot - 31)

  Session 572: obj - rowid = 00003942 - AAAViIAAEAAFGiJAAb

  (dictionary objn - 14658, file - 4, block - 1337481, slot - 27)

----- Information for the OTHER waiting sessions -----

Session 572:

  sid: 572 ser: 48 audsid: 33640125 user: 44/MY_SCHEMA flags: 0x41

  pid: 91 O/S info: user: oracle, term: UNKNOWN, ospid: 28338

    image: oracle@hostname

  client details:

    O/S info: user: iu, term: unknown, ospid: 1234

    machine: hostname program: JDBC Thin Client

    application name: JDBC Thin Client, hash value=2546894660

  current SQL:

  

MERGE INTO SFI_CUSTOMER_PROFILE TGT USING (SELECT :B45 AS KEY1, :B44 AS KEY2, :B43 AS KEY3, :B42 AS ATTR1, :B41 AS ATTR2, :B40 AS ATTR3, :B39 AS ATTR4, :B38 AS ATTR5, :B37 AS ATTR6, :B36 AS ATTR7, :B35 AS ATTR8, :B34 AS ATTR9, :B33 AS ATTR10, :B32 AS ATTR11, :B31 AS ATTR12, :B30 AS ATTR13, :B29 AS ATTR14, :B28 AS ATTR15, :B27 AS ATTR16, :B26 AS ATTR17, :B25 AS ATTR18, :B24 AS ATTR19, :B23 AS ATTR20, :B22 AS ATTR21, :B21 AS ATTR22, :B20 AS ATTR23, :B19 AS ATTR24, :B18 AS ATTR25, :B17 AS ATTR26, :B16 AS ATTR27, :B15 AS ATTR28, :B14 AS ATTR29, :B13 AS ATTR30, :B12 AS ATTR31, :B11 AS ATTR32, :B10 AS ATTR33, :B9 AS ATTR34, :B8 AS ATTR35, :B7 AS ATTR36, :B6 AS ATTR37, :B5 AS ATTR38, :B4 AS ATTR39, :B3 AS ATTR40, :B2 AS TS_LAST_MODIFIED, :B1 AS MSISDN FROM DUAL) SRC ON (SRC.KEY1 = TGT.KEY1) WHEN

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=cyn6n1hvduxmh) -----

MERGE into SFI_CUSTOMER_PROFILE o using (select :1  KEY1 from dual) o1 on (o.KEY1 = o1.KEY1) when matched then update  SET IMSI=:2 , ATTR1=:3 , ATTR2=:4 ,ATTR3=:5 , ATTR4=:6 , ATTR5=:7 , ATTR6=:8 , ATTR7=:9 , ATTR8=:10 ,  ATTR9=:11 , ATTR10=:12 , ATTR11=:13 , ATTR12=:14 , ATTR13=:15 , ATTR14=:16 , ATTR15=:17 , ATTR16=:18 , ATTR17=:19 , ATTR18=:20 ,  ATTR19=:21 , ATTR20=:22 , ATTR21=:23 , ATTR22=:24 , ATTR23=:25 ,  ATTR24=:26 , ATTR25=:27 , ATTR26=:28 , ATTR27=:29 , ATTR28=:30 ,  ATTR29=:31 , ATTR30=:32 , ATTR31=:33 , ATTR32=:34 , ATTR33=:35 ,  ATTR34=:36 , ATTR35=:37 , ATTR36=:38 , ATTR37=:39 , ATTR38=:40 ,  ATTR39=:41 , ATTR40=:42 ,TS_LAST_MODIFIED=sysdate, KEY2=:43 , KEY3=:44 , MSISDN=:45 , ATTR41=:46 , ATTR42=:47 , ATTR43=:48 , ATTR44=:49 , ATTR45=:50 ,  ATTR46=:51 , ATTR47=:52 , ATTR48=:53 , ATTR49=:54 , ATTR50=:55 ,  ATTR51=:56 , ATTR52=:57 , ATTR53=:58 , ATTR54=:59 , ATTR55=:60 ,  ATTR56=:61 , ATTR57=:62 , ATTR58=:63 , ATTR59=:64 , ATTR60=:65 ,  ATTR61=:66 , ATTR62=:67 , ATTR63=:68 , ATTR64=:69 , ATTR65=:70 ,  ATTR66=:71 , ATTR67=:72 , ATTR68=:73 , ATTR69=:74 , ATTR70=:75 ,  ATTR71=:76 , ATTR72=:77 , ATTR73=:78 , ATTR74=:79 , ATTR75=:80 ,  ATTR76=:81 , ATTR77=:82 , ATTR78=:83 , ATTR79=:84 , ATTR80=:85 ,  TS_LAST_TRY_FETCH=:86 , EI_STATUS_CODE_ID=:87   when not matched then  Insert (o.KEY1, o.IMSI, o.ATTR1, o.ATTR2, o.ATTR3, o.ATTR4,o.ATTR5,o.ATTR6,o.ATTR7,o.ATTR8,  o.ATTR9,o.ATTR10,o.ATTR11,o.ATTR12,o.ATTR13, o.ATTR14,o.ATTR15,o.ATTR16,o.ATTR17,o.ATTR18, o.ATTR19,o.ATTR20,o.ATTR21,o.ATTR22,o.ATTR23, o.ATTR24,o.ATTR25,o.ATTR26,o.ATTR27,o.ATTR28, o.ATTR29,o.ATTR30,o.ATTR31,o.ATTR32,o.ATTR33, o.ATTR34,o.ATTR35,o.ATTR36,o.ATTR37,o.ATTR38,  o.ATTR39,o.ATTR40,o.TS_LAST_MODIFIED,o.KEY2,o.KEY3, o.MSISDN, o.ATTR41,o.ATTR42,o.ATTR43,o.ATTR44,o.ATTR45,  o.ATTR46,o.ATTR47,o.ATTR48,o.ATTR49,o.ATTR50, o.ATTR51,o.ATTR52,o.ATTR53,o.ATTR54,o.ATTR55, o.ATTR56,o.ATTR57,o.ATTR58,o.ATTR59,o.ATTR60, o.ATTR61,o.ATTR62,o.ATTR63,o.ATTR64,o.ATTR65, o.ATTR66,o.ATTR67,o.ATTR68,o.ATTR69,o.ATTR70, o.ATTR71,o.ATTR72,o.ATTR73,o.ATTR74,o.ATTR75,  o.ATTR76,o.ATTR77,o.ATTR78,o.ATTR79,o.ATTR80, o.TS_LAST_TRY_FETCH,o.EI_STATUS_CODE_ID) Values (:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 ,:95 ,:96 ,:97 ,:98 ,:99 ,:100 ,:101 ,:102 ,:103 ,:104 ,:105 ,:106 ,:107 ,:108 ,:109 ,:110 ,:111 ,:112 ,:113 ,:114 ,:115 ,:116 ,:117 ,:118 ,:119 ,:120 ,:121 ,:122 ,:123 ,:124 ,:125 ,:126 ,:127 ,:128 ,:129 ,sysdate,:130 ,:131 ,:132 ,:133 ,:134 ,:135 ,:136 ,:137 ,:138 ,:139 ,:140 ,:141 ,:142 ,:143 ,:144 ,:145 ,:146 ,:147 ,:148 ,:149 ,:150 ,:151 ,:152 ,:153 ,:154 ,:155 ,:156 ,:157 ,:158 ,:159 ,:160 ,:161 ,:162 ,:163 ,:164 ,:165 ,:166 ,:167 ,:168 ,:169 ,:170 ,:171 ,:172 ,:173 ,:174 )

===========================================

C. Get the full SQL Text from DB

===========================================

Get the sql_id

SELECT SID, SQL_ID FROM V$SESSION WHERE SID IN (503, 572);

or

SELECT sql_id, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'MERGE into SFI_CUSTOMER_PROFILE o using (select :1  KEY1 from dual) o1 on (o.KEY1 = o1.KEY1) when matched then update%';

Using sql_id, get the full text

SELECT TO_CHAR(SQL_FULLTEXT) FROM V$SQL WHERE SQL_ID = 'cyn6n1hvduxmh';

Linux Memory - By Example

========================================
General
========================================

Customer complain that the server is slow.
How to check if the memory on the server is sufficient?

========================================
Commands to run
========================================
free -m
top
sar
ps - sorted by memory size.
/proc/$PID/map - Each process memory consumption
Optionally collect this data in a script over time

========================================
Analyze data
========================================

=====================
free -m

=====================

Thu Jan 28 12:56:15 ICT 2016

             total       used       free     shared    buffers     cached

Mem:         40200      40010        172          0        502      24503

-/+ buffers/cache:      15004      25178

Swap:        10240       2502       7738

Linux does pre-allocation/caching of the host RAM memory, so when a process needs a chunk of a memory, the allocation to the process would be faster.

So, it appears that 99.5% of the server RAM memory is used.

But on Linux that would be normal situation.

The actual memory usage, is displayed in the -/+ buffers/cache used and free values.
RAM Memory Allocated: 40010/40200*100=99.5% 
Meaning 99.5% of RAM memory is "used" - actually it is allocated/cached.

Out of total 40010 "used", 502Mb are allocated for file system metadata, and 24503 for I/O buffer.

RAM Memory Usage: 15004/40200*100 =37% of RAM memory is used.

SWAP usage: 2502/10240*100 = 25% of SWAP memory is used.

=====================

top

=====================

By default top would sort the output by CPU usage.
To change this behavior:

top -> Shift+f -> get sort options list -> chose memory (n)

top - 12:29:15 up 153 days, 12:34,  7 users,  load average: 11.03, 11.88, 11.50
Tasks: 2206 total,   1 running, 2202 sleeping,   0 stopped,   3 zombie
Cpu(s): 12.2%us,  5.9%sy,  0.0%ni, 69.1%id, 12.2%wa,  0.1%hi,  0.4%si,  0.0%st
Mem:  65968640k total, 65669104k used,   299536k free,    33612k buffers
Swap: 10485752k total,  8537300k used,  1948452k free, 11815512k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND               
 1957 iu        15   0 29.4g  23g  13m S  0.0 37.7  26:40.09 notification
11410 oracle    15   0 8449m 4.6g 4.6g S  0.0  7.4  70:39.95 oracle                 11390 oracle    15   0 8442m 4.6g 4.6g S  0.0  7.2  13:07.52 oracle 
11394 oracle    16   0 8456m 4.3g 4.3g D  0.6  6.8   7796:57 oracle                 

CPU

Cpu(s):  5.4%us,  1.4%sy,  0.0%ni, 92.8%id,  0.2%wa,  0.0%hi,  0.1%si,  0.0%st

92.8%id - 92% of the time it is in idle state.

Memory

Mem:  41146860k total, 40936740k used,   210120k free,   603392k buffers

Swap: 10485752k total,  2561952k used,  7923800k free, 24739192k cached

or in Mb, to be in sync with free -m output

Mem:  40182Mb total, 40000Mb used,   205Mb free,   590Mb buffers

Swap: 10240Mb total,  2502Mb used,  7738Mb free, 24160Mb cached

RAM Memory Allocated: 40000/40182*100 = 99.5% of RAM memory is "used" - In Linux that is normal.

SWAP usage: 2502/10240*100 = 25% of SWAP memory is used.

The output of the memory in top is not informative.

Because by default Linux would allocate most of host RAM memory, and distribute to applications as needed.

Processes using most memory:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            

 6069 iu        15   0  504m  64m  21m S  7.5  0.2   3533:35 mpi                

 6281 iu        15   0  504m  64m  21m S  5.6  0.2   3450:47 mpi                

  874 iu        15   0  410m 130m  14m S  3.7  0.3   6:05.08 notification       

  531 iu        15   0  623m 372m  14m S  1.9  0.9  23:38.78 notification       

12565 oracle    18   0 8447m  32m  28m S  1.9  0.1 107:55.56 oracle             

20396 oracle    15   0 8447m 301m 298m S  1.9  0.8   3:49.01 oracle             

25046 oracle    15   0 8448m 3.8g 3.8g S  1.9  9.7   8:19.16 oracle    

PR - Priority.

The lower this value, the higher the priority.
The highest priority is -20 and the lowest is 20.

NI - Nice.

RES stands for the Resident Size, which is an accurate representation of how much actual physical memory,RAM memory, a process is consuming. This also corresponds to the %MEM column.

VIRT stands for the Virtual Size of a process, which is the sum of memory it is actually using, shared libraries, and memory shared with other processes. VIRT represents how much memory the program is able to access at the present moment.

SHR indicates how much of the VIRT size is actually Sharable (memory or libraries). In the case of libraries, it does not necessarily mean that the entire library is resident. For example, if a program only uses a few functions in a library, the whole library is mapped and will be counted in VIRT and SHR, but only the parts of the library file containing the functions being used will actually be loaded in and be counted under RES.

S is the Process Status : 
The status of the task which can be one of:
'D' = Uninterruptible sleep
'R' = Running
'S' = Sleeping
'T' = Traced or stopped
'Z' = Zombie

%MEM
Based upon Memory Usage (RES) value.
A task's currently used share of available physical memory (RAM).

TIME+
Total CPU time the task has used since it started.

=====================

sar

=====================

Total CPU time the task has used since it started.
sar -S => Get SWAP usage
sar -r => Get Memory free and used 

=====================
ps
=====================

ps -eo pid,ppid,rss,size,vsize,pcpu,pmem,cmd -ww --sort=vsize

Sample output:
  PID  PPID     RSS   SZ    VSZ %CPU %MEM CMD
28756     1 3442212 3008 8659216 0.0  8.3 oracleigt (LOCAL=NO)
27088     1 4048484 3016 8659224 0.0  9.8 oracleigt (LOCAL=NO)
14187     1 4334060 3024 8659232 0.0 10.5 oracleigt (LOCAL=NO)
28230     1 827388  2968 8666344 0.0  2.0 oracleigt (LOCAL=NO)
28200     1 739872  2972 8666348 0.0  1.7 oracleigt (LOCAL=NO)
28222     1 824800  2972 8666348 0.0  2.0 oracleigt (LOCAL=NO)
11412     1 2554320 2996 8666372 1.0  6.2 oracleigt (LOCAL=NO)
11404     1 2463452 3000 8666376 1.2  5.9 oracleigt (LOCAL=NO)
13056     1 204308  2980 8678196 0.0  0.4 oracleigt (LOCAL=NO)
11421     1 2653716 3000 8687944 1.1  6.4 oracleigt (LOCAL=NO)



ps -eo pmem,pcpu,pid,rss,vsize,args | sort -k 1 -n -r | head -20 

pmem pcpu  pid  rss     vsize   args

8.1  1.4   3242 2665160 6230672 /software/java/x64/jdk1.8.0_77/bin/java -Dcom.sun.management.jmxremote.port=1108 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -XX:NewSize=200m -XX:SurvivorRatio=6 -XX:MaxTenuringThreshold=4 -XX:CMSInitiatingOccupancyFraction=60 -XX:+UseCMSInitiatingOccupancyOnly -XX:+DisableExplicitGC -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -DserviceName=sparx -DclusterStartDelay=120000 -Djava.net.preferIPv4Stack=true -DisCluster=true -Xms4096m -Xmx4096m -Djava.library.path=/starhome/igate/pru-ent-sx2/bin -classpath /starhome/igate/pru-ent-sx2/lib/wrapper-3.5.17.jar:/starhome/igate/pru-ent-sx2/lib/log4j-1.2.17.jar:/starhome/igate/pru-ent-sx2/lib/ojdbc8-12.2.0.1.jar:/starhome/igate/pru-ent-sx2/lib/javolution-1.0.99.jar:/starhome/igate/pru-ent-sx2/lib/commons-pool-1.6.jar:/starhome/igate/pru-ent-sx2/lib/ig2-common.jar:/starhome/igate/pru-ent-sx2/lib/sparx.jar:/starhome/igate/pru-ent-sx2/lib/jaxb-api-

6.4  0.0   124235 2123600 4496808 ora_dbw0_igt

5.9  0.1   123633 1955316 4446704 oracleigt (LOCAL=NO)

5.7  0.0   125467 1898732 4446708 oracleigt (LOCAL=NO)

5.7  0.0   126547 1872568 4446672 oracleigt (LOCAL=NO)

5.1  0.0   125675 1692532 4446636 oracleigt (LOCAL=NO)

4.3  0.0   123456 1413616 4445596 oracleigt (LOCAL=NO)

4.2  0.0   124580 1385260 4446656 oracleigt (LOCAL=NO)

pmem - Percent memory of the process out of host resources.
pcpu - Percent CPU of the process out of host resources.

The size for RSS, SZ, VSZ is in Kb

* To see the command line parameters, use: xargs -0 < /proc/<pid>/cmdline

This command, is displaying selected fields, and sorts the output by vsize.
Similar to top, ps also displays three memory types:

RSS - Resident Set Size.
RSS Is a memory currently used by a process. 
How much RAM the process is actually using.
It include stack and heap process memory.

VSIZE - Virtual Set Size
VSZ is how much memory a process can access for its execution.
It include memory that is from shared libraries.

SIZE - Virtual Size of the data section of the process. 
            The data segment contains any global or static variables.


top vs ps
in top => in ps
res    => rss
virt   => vsize 


batch mode
top -b - The default. Output is sorted by CPU.
top -ba - The output is sorted by %MEM.

=====================

/proc/$PID/map and pmap

=====================

To see each process memory consumption, check out /proc/$PID/map file.

RSS is how much memory this process currently has in main memory (RAM). 
VSZ is how much virtual memory the process has in total. 
This includes all types of memory, both in RAM and swapped out. 
These numbers can get skewed because they also include shared libraries and other types of memory. 
To have a  more detailed idea about the memory footprint of a process, need to look at  /proc/$PID/maps.
If lists shared libraries and additional memory usage per process.
To see the data listed in  /proc/$PID/maps in a more human form, use pmap $PID command.
ps -ef | grep ora

oracle   24812     1  0  2015 ?        00:01:23 ora_pmon_igt
oracle   24814     1  0  2015 ?        00:00:00 ora_mman_igt
oracle   24816     1  0  2015 ?        07:08:47 ora_dbw0_igt
oracle   24818     1  0  2015 ?        03:51:26 ora_lgwr_igt
oracle   24820     1  0  2015 ?        01:42:20 ora_ckpt_igt
oracle   24822     1  0  2015 ?        00:21:07 ora_smon_igt
oracle   24824     1  0  2015 ?        00:00:09 ora_reco_igt
oracle   24826     1  0  2015 ?        00:12:52 ora_cjq0_igt
oracle   25112     1  0  2015 ?        00:00:44 ora_arc0_igt
oracle   25114     1  0  2015 ?        04:33:05 ora_arc1_igt
oracle   25121     1  0  2015 ?        00:00:01 ora_qmnc_igt
oracle   25123     1  0  2015 ?        00:09:23 ora_mmon_igt
oracle   25125     1  0  2015 ?        00:01:48 ora_mmnl_igt
oracle   25401     1  0  2015 ?        00:00:00 ora_q000_igt
oracle   18812     1  0 Apr16 ?        00:00:00 ora_q001_igt
oracle   18680    1  0 06:38 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   18710     1  0 06:38 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   19557     1  0 06:39 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   22466     1  0 06:44 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   27122     1  0 06:52 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   29435     1  0 06:56 ?        00:00:00 oracleigt (LOCAL=NO)
oracle   28329     1  0 13:28 ?        00:00:01 oracleigt (LOCAL=NO)
oracle   28334     1  0 13:28 ?        00:00:01 oracleigt (LOCAL=NO)
oracle   28336     1  0 13:28 ?        00:00:00 oracleigt (LOCAL=NO)
oracle    2773     1  4 13:40 ?        00:02:48 oracleigt (LOCAL=NO)
oracle   31244     1  0 14:29 ?        00:00:00 oracleigt (LOCAL=NO)

less /proc/18680/maps

0089a000-008af000 r-xp 00000000 fd:01 213125     /lib/ld-2.3.4.so
008af000-008b0000 r-xp 00015000 fd:01 213125     /lib/ld-2.3.4.so
008b0000-008b1000 rwxp 00016000 fd:01 213125     /lib/ld-2.3.4.so
008b3000-009d7000 r-xp 00000000 fd:01 213127     /lib/tls/libc-2.3.4.so
009d7000-009d8000 r-xp 00124000 fd:01 213127     /lib/tls/libc-2.3.4.so
009d8000-009db000 rwxp 00125000 fd:01 213127     /lib/tls/libc-2.3.4.so
009db000-009dd000 rwxp 009db000 00:00 0
009df000-009e1000 r-xp 00000000 fd:01 213139     /lib/libdl-2.3.4.so
009e1000-009e3000 rwxp 00001000 fd:01 213139     /lib/libdl-2.3.4.so
009e5000-00a06000 r-xp 00000000 fd:01 213136     /lib/tls/libm-2.3.4.so
00a06000-00a08000 rwxp 00020000 fd:01 213136     /lib/tls/libm-2.3.4.so
00afb000-00b09000 r-xp 00000000 fd:01 213128     /lib/tls/libpthread-2.3.4.so
00b09000-00b0b000 rwxp 0000d000 fd:01 213128     /lib/tls/libpthread-2.3.4.so
00b0b000-00b0d000 rwxp 00b0b000 00:00 0
00bdd000-00bef000 r-xp 00000000 fd:01 213159     /lib/libnsl-2.3.4.so
00bef000-00bf1000 rwxp 00011000 fd:01 213159     /lib/libnsl-2.3.4.so
00bf1000-00bf3000 rwxp 00bf1000 00:00 0
08048000-0bfeb000 r-xp 00000000 fd:08 300759     /software/oracle/101/bin/oracle
0bfeb000-0c191000 rwxp 03fa2000 fd:08 300759     /software/oracle/101/bin/oracle
0c191000-0c20e000 rwxp 0c191000 00:00 0
50000000-a2200000 rwxs 00000000 00:06 8192002    /SYSV16e137cc (deleted)
b73d7000-b73e7000 rwxp 00000000 00:0d 2047       /dev/zero
b73e7000-b73f7000 rwxp 00000000 00:0d 2047       /dev/zero
b73f7000-b7407000 rwxp 00000000 00:0d 2047       /dev/zero
b7407000-b7417000 rwxp 00000000 00:0d 2047       /dev/zero
b7417000-b7427000 rwxp 00000000 00:0d 2047       /dev/zero
b7427000-b7437000 rwxp 00000000 00:0d 2047       /dev/zero
b7437000-b7447000 rwxp 00000000 00:0d 2047       /dev/zero
b7447000-b7457000 rwxp 00000000 00:0d 2047       /dev/zero
b7457000-b74d7000 rwxp 0012f000 00:0d 2047       /dev/zero
b74d7000-b74f7000 rwxp 00000000 00:0d 2047       /dev/zero
b74f7000-b7507000 rwxp 00000000 00:0d 2047       /dev/zero
b7507000-b7517000 rwxp 00000000 00:0d 2047       /dev/zero
b7517000-b7527000 rwxp 00000000 00:0d 2047       /dev/zero
b7527000-b7537000 rwxp 00000000 00:0d 2047       /dev/zero
b7537000-b7547000 rwxp 00000000 00:0d 2047       /dev/zero
b7547000-b7557000 rwxp 00000000 00:0d 2047       /dev/zero
b7557000-b7560000 r-xp 00000000 fd:01 213042     /lib/libnss_files-2.3.4.so
b7560000-b7562000 rwxp 00008000 fd:01 213042     /lib/libnss_files-2.3.4.so
b756f000-b75c0000 rwxp 00000000 00:0d 2047       /dev/zero
b75c0000-b75d0000 rwxp 00000000 00:0d 2047       /dev/zero
b75d0000-b75e0000 rwxp 00000000 00:0d 2047       /dev/zero
b75e0000-b75ef000 rwxp 00071000 00:0d 2047       /dev/zero
b75ef000-b75f9000 rwxp b75ef000 00:00 0
b75f9000-b75fa000 --xp b75f9000 00:00 0
b75fa000-b76fe000 rwxp b75fa000 00:00 0
b76fe000-b76ff000 r-xp 00000000 fd:02 70039      /usr/lib/libaio.so.1.0.1
b76ff000-b7700000 rwxp 00000000 fd:02 70039      /usr/lib/libaio.so.1.0.1
b770d000-b7886000 r-xp 00000000 fd:08 250228     /software/oracle/101/lib/libnnz10.so
b7886000-b78a0000 rwxp 00178000 fd:08 250228     /software/oracle/101/lib/libnnz10.so
b78a0000-b78a1000 rwxp b78a0000 00:00 0
b78a1000-b78ac000 r-xp 00000000 fd:08 250414     /software/oracle/101/lib/libdbcfg10.so
b78ac000-b78ad000 rwxp 0000a000 fd:08 250414     /software/oracle/101/lib/libdbcfg10.so
b78ad000-b7e8d000 r-xp 00000000 fd:08 250588     /software/oracle/101/lib/libjox10.so
b7e8d000-b7ecf000 rwxp 005e0000 fd:08 250588     /software/oracle/101/lib/libjox10.so
b7ecf000-b7ed0000 rwxp b7ecf000 00:00 0
b7ed0000-b7ed4000 r-xp 00000000 fd:08 250415     /software/oracle/101/lib/libocrutl10.so
b7ed4000-b7ed5000 rwxp 00003000 fd:08 250415     /software/oracle/101/lib/libocrutl10.so
b7ed5000-b7ed6000 rwxp b7ed5000 00:00 0
b7ed6000-b7efb000 r-xp 00000000 fd:08 250409     /software/oracle/101/lib/libocrb10.so
b7efb000-b7efc000 rwxp 00024000 fd:08 250409     /software/oracle/101/lib/libocrb10.so
b7efc000-b7f2c000 r-xp 00000000 fd:08 250407     /software/oracle/101/lib/libocr10.so
b7f2c000-b7f2d000 rwxp 00030000 fd:08 250407     /software/oracle/101/lib/libocr10.so
b7f2d000-b7f2e000 r-xp 00000000 fd:08 250412     /software/oracle/101/lib/libskgxn2.so
b7f2e000-b7f2f000 rwxp 00001000 fd:08 250412     /software/oracle/101/lib/libskgxn2.so
b7f2f000-b7fec000 r-xp 00000000 fd:08 250419     /software/oracle/101/lib/libhasgen10.so
b7fec000-b7ff1000 rwxp 000bc000 fd:08 250419     /software/oracle/101/lib/libhasgen10.so
b7ff1000-b7ff5000 rwxp b7ff1000 00:00 0
b7ff5000-b7ff7000 r-xp 00000000 fd:08 250298     /software/oracle/101/lib/libskgxp10.so
b7ff7000-b7ff8000 rwxp 00001000 fd:08 250298     /software/oracle/101/lib/libskgxp10.so
b7ff8000-b7ff9000 r-xp 00000000 fd:08 250672     /software/oracle/101/lib/libodmd10.so
b7ff9000-b7ffa000 rwxp 00000000 fd:08 250672     /software/oracle/101/lib/libodmd10.so
b7ffa000-b7ffb000 rwxp b7ffa000 00:00 0
b7ffb000-b7fff000 r-xp 00000000 fd:08 250395     /software/oracle/101/lib/libunwind.so.3
b7fff000-b8000000 rwxp 00003000 fd:08 250395     /software/oracle/101/lib/libunwind.so.3
bffec000-c0000000 rwxp bffec000 00:00 0
ffffe000-fffff000 --xp 00000000 00:00 0

pmap 18680
18680:   oracleigt (LOCAL=NO)
0089a000     84K r-x--  /lib/ld-2.3.4.so
008af000      4K r-x--  /lib/ld-2.3.4.so
008b0000      4K rwx--  /lib/ld-2.3.4.so
008b3000   1168K r-x--  /lib/tls/libc-2.3.4.so
009d7000      4K r-x--  /lib/tls/libc-2.3.4.so
009d8000     12K rwx--  /lib/tls/libc-2.3.4.so
009db000      8K rwx--    [ anon ]
009df000      8K r-x--  /lib/libdl-2.3.4.so
009e1000      8K rwx--  /lib/libdl-2.3.4.so
009e5000    132K r-x--  /lib/tls/libm-2.3.4.so
00a06000      8K rwx--  /lib/tls/libm-2.3.4.so
00afb000     56K r-x--  /lib/tls/libpthread-2.3.4.so
00b09000      8K rwx--  /lib/tls/libpthread-2.3.4.so
00b0b000      8K rwx--    [ anon ]
00bdd000     72K r-x--  /lib/libnsl-2.3.4.so
00bef000      8K rwx--  /lib/libnsl-2.3.4.so
00bf1000      8K rwx--    [ anon ]
08048000  65164K r-x--  /software/oracle/101/bin/oracle
0bfeb000   1688K rwx--  /software/oracle/101/bin/oracle
0c191000    500K rwx--    [ anon ]
50000000 1345536K rwxs-    [ shmid=0x7d0002 ]
b73d7000     64K rwx--  /dev/zero
b73e7000     64K rwx--  /dev/zero
b73f7000     64K rwx--  /dev/zero
b7407000     64K rwx--  /dev/zero
b7417000     64K rwx--  /dev/zero
b7427000     64K rwx--  /dev/zero
b7437000     64K rwx--  /dev/zero
b7447000     64K rwx--  /dev/zero
b7457000    512K rwx--  /dev/zero
b74d7000    128K rwx--  /dev/zero
b74f7000     64K rwx--  /dev/zero
b7507000     64K rwx--  /dev/zero
b7517000     64K rwx--  /dev/zero
b7527000     64K rwx--  /dev/zero
b7537000     64K rwx--  /dev/zero
b7547000     64K rwx--  /dev/zero
b7557000     36K r-x--  /lib/libnss_files-2.3.4.so
b7560000      8K rwx--  /lib/libnss_files-2.3.4.so
b756f000    324K rwx--  /dev/zero
b75c0000     64K rwx--  /dev/zero
b75d0000     64K rwx--  /dev/zero
b75e0000     60K rwx--  /dev/zero
b75ef000     40K rwx--    [ anon ]
b75f9000      4K --x--    [ anon ]
b75fa000   1040K rwx--    [ anon ]
b76fe000      4K r-x--  /usr/lib/libaio.so.1.0.1
b76ff000      4K rwx--  /usr/lib/libaio.so.1.0.1
b770d000   1508K r-x--  /software/oracle/101/lib/libnnz10.so
b7886000    104K rwx--  /software/oracle/101/lib/libnnz10.so
b78a0000      4K rwx--    [ anon ]
b78a1000     44K r-x--  /software/oracle/101/lib/libdbcfg10.so
b78ac000      4K rwx--  /software/oracle/101/lib/libdbcfg10.so
b78ad000   6016K r-x--  /software/oracle/101/lib/libjox10.so
b7e8d000    264K rwx--  /software/oracle/101/lib/libjox10.so
b7ecf000      4K rwx--    [ anon ]
b7ed0000     16K r-x--  /software/oracle/101/lib/libocrutl10.so
b7ed4000      4K rwx--  /software/oracle/101/lib/libocrutl10.so
b7ed5000      4K rwx--    [ anon ]
b7ed6000    148K r-x--  /software/oracle/101/lib/libocrb10.so
b7efb000      4K rwx--  /software/oracle/101/lib/libocrb10.so
b7efc000    192K r-x--  /software/oracle/101/lib/libocr10.so
b7f2c000      4K rwx--  /software/oracle/101/lib/libocr10.so
b7f2d000      4K r-x--  /software/oracle/101/lib/libskgxn2.so
b7f2e000      4K rwx--  /software/oracle/101/lib/libskgxn2.so
b7f2f000    756K r-x--  /software/oracle/101/lib/libhasgen10.so
b7fec000     20K rwx--  /software/oracle/101/lib/libhasgen10.so
b7ff1000     16K rwx--    [ anon ]
b7ff5000      8K r-x--  /software/oracle/101/lib/libskgxp10.so
b7ff7000      4K rwx--  /software/oracle/101/lib/libskgxp10.so
b7ff8000      4K r-x--  /software/oracle/101/lib/libodmd10.so
b7ff9000      4K rwx--  /software/oracle/101/lib/libodmd10.so
b7ffa000      4K rwx--    [ anon ]
b7ffb000     16K r-x--  /software/oracle/101/lib/libunwind.so.3
b7fff000      4K rwx--  /software/oracle/101/lib/libunwind.so.3
bffec000     80K rwx--    [ stack ]
ffffe000      4K --x--    [ anon ]
 total  1426920K

Compare the total from pmap to data from top:
top -b | grep 18680
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
18680 oracle    16   0 1393m  45m  43m S  0.0  1.5   0:00.23 oracle

The total number reported by pmap is close to virtual memory reported by top.

============================
Sum of all processes, collected by ps
============================
ps -eo pid,ppid,rss,size,vsize,pcpu,pmem,cmd -ww --sort=vsize" 

----------- ----------- ----------- ----------- ------- ----
Processes   RSS        SZ          VSZ        %CPU %MEM
----------- ----------- ----------- ----------- ------- ----
Oracle    13585300 422944    1522421180 0.6 29.2
Java    4421972 27383820    28059964 0.9 10.5
Starhome    3841628     7190628    10834928 14.7 8   

Total    21848900 34997392    1561316072 16.2 47.7

RSS = 21848900Kb = 21,336Mb

per ps output, the actual used memory is 21.336Mb

========================================
Comparing the results
========================================
The important data is the 
Each command provide slightly different numbers:

RAM Memory Allocated.
free: 15,004Mb
top : not possible to tell
ps  :  21,336Mb

Free Memory
free: 25,178Mb
top : not possible to tell
ps  : not possible to tell

========================================
script to collect data from top, free, ps commands into a log

========================================
top -b -n 1 
-b => batch
-n 1 => execute 1 time.


#!/bin/bash

touch top_trace.log
touch free.log
touch sorted_processes.log

while [ 1 -eq 1 ] ; do
  date >> top_trace.log
  top -b -n 1 | head -30 >> top_trace.log

  date >> free.log
  free >> free.log  

  date >> sorted_processes.log

  ps -eo pid,ppid,rss,size,vsize,pcpu,pmem,cmd -ww --sort=vsize > sorted_processes.log

  sleep 10

  #------------------------------------------
  #prevent the log files from growing forever
  #------------------------------------------
  mv -f `find . -name "sorted_processes.log" -size +1000000k` sorted_processes.log_bak
  mv -f `find . -name "top_trace.log" -size +1000000k` top_trace.log_bak
  mv -f `find . -name "free.log" -size +1000000k` free.log_bak

done

========================================
Reference
========================================
Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10
top command - Linux.about.com


Linux Memory Explained