Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Monitoring Dedicated Connections
to Oracle

Oracle Tips by Burleson

Oracle provides the v$process and v$session views within Oracle to get detailed information about Oracle sessions. Most importantly, you can see the UNIX process IDs for all executing processes on your database server. Remember, this technique only works when you are not using the Oracle dedicated listener, because invoking the Oracle multi-threaded server (MTS) causes Oracle connections to be funneled into pre-spawned shadow processes. In Oracle, dynamic connections are still permitted, but the PGA memory can be directly allocated and managed within Oracle.

The following script is extremely useful for showing all dedicated connections to Oracle. Please note that this script displays the UNIX process ID (PID) and also gives information about the executing program. It is also possible to enhance this script to show the actual SQL statement by joining into the v$sql view.

--   session.sql
--   © 2002 by Donald K. Burleson
--   No part of this SQL script may be copied, sold or distributed
--   without the express consent of Donald K. Burleson
rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

   rpad(||':',11)||rpad(' current logons='||
   (to_number(b.sessions_current)),20)||'cumulative logons='||
   rpad(substr(a.value,1,10),10)||'highwater mark='||
   b.sessions_highwater Information
   v$sysstat a,
   v$license b,
   v$database c
where = 'logons cumulative'

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
       substr(a.spid,1,9)      pid,
       substr(b.sid,1,5)       sid,
       substr(b.serial#,1,5)   ser#,
       substr(b.machine,1,6)   box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8)    os_user,
       substr(b.program,1,30)  program
   v$session b,
   v$process a
   b.paddr = a.addr
order by

ttitle off;
set heading off;
select 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;

Here is a sample listing from running this script. Please note that it begins by displaying summaries of all current, cumulative, and the high-water mark for logons before displaying the details for each session:

SQL> @session

PRODLIVE:  current logons=14  cumulative logons=166       highwater mark=14

                              UNIX/Oracle Sessions
                          Sessions on database PEMINE
Sat Oct 13                                                          page   1
                                dbname Database
                              UNIX/Oracle Sessions

--------- ----- ----- ------ ---------- -------- ---------------------------
1005      14    124   hawk   CASH       rhayes   runmenu50@hawk
1139      13    39    hawk   STAFF      clarson  runmenu50@hawk
1526      11    1550  hawk   BURLESON   burleson sqlplus@hawk
1690      15    47    hawk   CASH       kjoslin  runmenu50@hawk
2482      16    263   hawk   STAFF      brobinso runmenu50@hawk
2568      17    26    BELLEV SCHED      Bellmont F45RUN32.EXE
27180     9     228   hawk   PATIENT    daemon   sqlplus@hawk
29316     8     3238  hawk   CASH       jdutcher runmenu50@hawk
29440     12    137   hawk   CASH       lchapman runmenu50@hawk
3231      18    173   hawk   STAFF      jhahn    runmenu50@hawk
3241      19    39    BELLEV SCHED      dplueger F45RUN32.EXE
 273      20    11    BELLEV SCHED      dplueger R25SRV32.EXE


Now, the first column of this output represents the UNIX process ID (PID) for each of the Oracle processes. The SID and SER in the second and third columns are also important because you need these values if you want to kill any Oracle session. The fourth column, labeled BOX, shows you the originating server for distributed requests. The fifth column is the Oracle user ID associated with the session, and the sixth column is the UNIX user who initiated the session.

If you want to move outside of Oracle and see details for the session at the UNIX level, you must correlate the Oracle PIS with the UNIX PID. To see details of these processes, you can write an Oracle script to filter the UNIX ps output to only include these processes:


sqlplus cpi/oracle@prodlive<<!
set pages 999
set feedback off
set echo off
set heading off

spool /tmp/run_pid.ksh

   'ps -ef|grep '||spid||’grep –v grep’
   spid is not NULL

spool off;

# Execute the UNIX commands . . . .
chmod +x /tmp/*.ksh


Here is the output from this script. As you see, the SQL*Plus script builds the UNIX ps command for the Oracle PIDs and then executes the command:

root> /tmp/run_pid.ksh
   jjahn  3231  3129  0 08:12:17 ?         0:00 oraclePA
bbeckero  2482  2377  0 07:59:26 ?         0:00 oraclePA
  scarf   2376   785  0 07:59:03 ttyp9     0:00 telnetd
brobins   2377  2376  0 07:59:04 ttyp9     0:01 runmenu50 pamenu
  monte   1372     1  0  Sep 21  ?         5:58 /opt/hpnp/bin/hpnpd
  jmels   1886  1878  0  Sep 21  ttyp1     0:00 tee -a

This script allows you to see the start time for the UNIX connection and also see the cumulative CPU consumption for each task.

Now let’s move on and take a look at how UNIX interacts with the Oracle multi-threaded server.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter