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