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


 

HTML Text

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;

select
   rpad(c.name||':',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
from
   v$sysstat a,
   v$license b,
   v$database c
where
   a.name = '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;
select
       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
from
   v$session b,
   v$process a
where
   b.paddr = a.addr
and
   type='USER'
order by
   spid
;


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

INFORMATION
----------------------------------------------------------------------------
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

PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM
--------- ----- ----- ------ ---------- -------- ---------------------------
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

To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION 'SID, SER#';
SQL>   
                   

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:

ps_pid.ksh

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

spool /tmp/run_pid.ksh

select
   'ps -ef|grep '||spid||’grep –v grep’
from
   v\$process
where
   spid is not NULL
;

spool off;
!

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

/tmp/run_pid.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