Home » RDBMS Server » Server Administration » Help: DB session and OS process
| Help: DB session and OS process [message #61337] |
Thu, 15 April 2004 06:51  |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Hi all, I have a question and can not find the answer from the articles.
We have Oracle 9i running on Solaris system. The application servers connect to database by using JDBC. I want to monitor the cpu/memory usage for the DB sessions. But how can I match the DB sessions with the OS processes? Since when I "select process from V$session", it is all blank!
What I really want to monitor is the cpu/memory usages for the sessions from the applications that call different stored procedures).
Please give me some advice! Thanks.
Calla
|
|
|
|
| Re: Help: DB session and OS process [message #61339 is a reply to message #61337] |
Thu, 15 April 2004 07:17  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Hi,
you can use 'top' to monitor the cpu usage of the oracle server process.
for eg)
last pid: 28704; load averages: 0.96, 1.14, 1.39 11:18:40
212 processes: 209 sleeping, 3 on cpu
CPU states: 78.6% idle, 13.2% user, 4.8% kernel, 3.4% iowait, 0.0% swap
Memory: 12G real, 7396M free, 3555M swap in use, 14G swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
24947 oracle 1 40 0 1744M 1716M cpu/2 4:56 8.54% oracle
28651 oracle 1 51 0 1744M 1715M sleep 0:05 1.11% oracle
10428 oracle 1 46 4 8384K 7240K sleep 0:17 0.87% xterm
28364 oracle 1 1 0 1744M 1714M sleep 0:05 0.46% oracle
18158 oracle 1 3 0 1744M 1716M sleep 174:02 0.44% oracle
27571 oracle 1 59 0 1744M 1716M sleep 0:24 0.41% oracle
1240 root 48 101 -20 55M 4464K sleep 40.6H 0.33% rgmd
27334 oracle 1 58 0 1744M 1716M sleep 0:19 0.32% oracle
1255 root 14 54 0 12M 3896K sleep 22.5H 0.26% scsymon_srv
26022 oracle 1 58 0 1744M 1716M sleep 1:01 0.18% oracle
28414 oracle 1 48 0 1745M 1717M cpu/3 0:08 0.18% oracle
Once you get OS PID, you could find out the details of that process, by joining v$session and v$process , as below
SQL> select substr(b.username,1,15) "Username",substr(b.osuser,1,10) "OSUSER",substr(b.machine,1,10) "host",substr(b.program,1,15) "Program",b.status,b.lockwait,b.sid, b.serial#,to_char(logon_time,'DD-MON-YY HH:MI AM') "Login_time",b.client_info
from v$session b, v$process c where
c.spid = '&pid' and b.paddr = c.addr;
2 3 Enter value for pid: 24947
old 3: c.spid = '&pid' and b.paddr = c.addr
new 3: c.spid = '24947' and b.paddr = c.addr
Username OSUSER host Program STATUS LOCKWAIT
--------------- ---------- ---------- --------------- -------- --------
SID SERIAL# Login_time
---------- ---------- ------------------
CLIENT_INFO
----------------------------------------------------------------
SYSADM fprd apricot PSAPPSRV@aprico INACTIVE
29 30686 15-APR-04 10:03 AM
CTAKAKJI,fprd,apricot,,PSAPPSRV,
-Thiru
|
|
|
|
| Re: Help: DB session and OS process [message #61358 is a reply to message #61339] |
Fri, 16 April 2004 06:02 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Thanks Thiru, I got it...
Another question, what exactly is the meaning of status in V$session. Since everytime I do
"select username, status from V$session where username is not null", the status for the JDBC sessions shows up 'INACTIVE', but actually some sessions are active!
Is this a right way to check the active session in database?
Thank you very much for your reply! Really appreciate it!
Calla
|
|
|
|
| Re: Help: DB session and OS process [message #61381 is a reply to message #61358] |
Mon, 19 April 2004 13:13 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Calla,
STATUS : Status of the session
ACTIVE (currently executing SQL),
INACTIVE(currently not executing),
KILLED (marked to be killed),
CACHED (temporarily cached for use by Oracle*XA),
SNIPED (session inactive, waiting on the client)
A session that is connected may not be 'ACTIVE' all the time.
-Thiru
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 14:18:41 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
