Home » RDBMS Server » Server Administration » weird runaway connections/processes on the Oracle server, urgent, please help.
| weird runaway connections/processes on the Oracle server, urgent, please help. [message #51406] |
Tue, 21 May 2002 21:11  |
Bob
Messages: 49
Registered: March 1999
|
Member |
|
|
Oracle 8.1.6 Server on Solaris 8
the application cnnects to db server from the application server. for certain activities (queries) against the db, which i don't know exactly yet, the app'll create a connection which stays 'ACTIVE' (from v$session) forever. The server has 4 CPUs, for the first connection, it takes 25% of CPU time, or one whole CPU, away. The 2nd connection will take another 25%,.. when you have four connections, CPU utilization became 100%. when more than 4 connections, then each connection will take 1/n CPU time. The weird part is those connections always shows 'ACTIVE' in v$session and even the application guys say they are not running anything or what they've been run was already finished. There is no transactions in v$transaction and no query against the database (from v$sql) either. Those connections never disappear unless you kill them one by one. We have the same application in the QA evironment and seems running fine. I can't figure this out, and simply feel it's sort of the network/connection issue between the application server and the database server. any idea? pls help
|
|
|
|
|
|
| Re: weird runaway connections/processes on the Oracle server, urgent, please help. [message #51446 is a reply to message #51406] |
Thu, 23 May 2002 04:30  |
Alex Shenfield
Messages: 15
Registered: May 2002
|
Junior Member |
|
|
From you email it would seam likely that it's related to the install. The Unix calls you noted would cause me to look at the latches. While you have the problem perform the following sql:-
SPOOL v_views.log;
SELECT *
FROM v$parameter;
SELECT class, value, name
FROM v$sysstat;
SELECT sid, id1, id2, type, lmode, request
FROM v$lock;
SELECT l.latch#, n.name, h.pid, l.gets, l.misses,
l.immediate_gets, l.immediate_misses, l.sleeps
FROM v$latchname n, v$latchholder h, v$latch l
WHERE l.latch# = n.latch#
AND l.addr = h.laddr(+);
SELECT *
FROM v$session_wait
ORDER BY sid;
Repeat last query 3 times - we want to see who's repeatedly waiting!
I would expect this to point you in the correct direction.
|
|
|
|
| Re: weird runaway connections/processes on the Oracle server, urgent, please help. [message #51466 is a reply to message #51446] |
Thu, 23 May 2002 22:17 |
Bob
Messages: 49
Registered: March 1999
|
Member |
|
|
thanks a lot Alex, i think i'm one step forward by now even still don't know why and what adction should be taken.
by query v$session_wait, i found the following about this weird connection/session:
event: latch free, i missed the values for p1,p2,p3
event: db file sequential read, p1: 7, p2: 48283, p3 1
event: db file sequential read, p1: 8, p2: 58820, p3 1
....
the event from v$session_wait, then, always is 'db file sequential read' and the file# (p1) switch from among 7,8,9 which are the 2 datafiles and one index file. i'm not sure what's wrong with those files and further more somehow i feel, per the developer, they already got their query response back, how ever they also having a query 'timeout issue' which may be related to this as well.
another thing is our production dataabse and Oracle binarys as well are built on HDS (Hitachi Data Ssystem) which is different from the the dev database/ can this be the cause? i thought to include the other spool files here but changed my mind since they can not easily edited here for ease the reading. thanks for all your help and also thanks in advance for your further advice.
|
|
|
|
Goto Forum:
Current Time: Mon Sep 16 18:19:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
