| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Oracle session hangs
Hi,
I have spent a while searching the web and news groups to find relevant information concerning this problem, but with no success so far. Here it is :
I am using
- Oracle 8.1.7.4 + recommended patches at time of installation which
was last jully.
- Linux RedHat Enterprise 2.1 with kernel 2.4.9-e.27enterprise #1 SMP
+ recommended patches at time of installation last jully
- The machine is a big and powerfull dual Xeon, losts of RAM, lost of
disk
- Oracle is dealing with many requests coming in through 8 application
servers. DB is big : approx 200GB.
For some time now, it seems that about zero to 5 sessions hang every day, but seem to be doing NOTHING ! Or in other words seem to be waiting for something that never comes. During this wait, the corresponding oracle shadow processes are not using any cpu. However, the client is waiting for some extra response from oracle.
"Alter system kill session" does not have any effect on these sessions. After a short period of time, it returns indicating that the session has been marked for kill. After the "kill session" the unix session process is still around, and not using any cpu. The killed session shadow process stays around many hours, until the client disconnects. Also the current transaction is very small. Rollback would be unnoticed.
It seems the only way to get rid of this session is to perform a unix "kill -9" on the oracle shadow process. In that case, the result seems relatively clean, and oracle seems to manage to clean up instantly.
I looked into v$session / v$session_wait / v$session_event / v$lock and v$locked_object and v$transaction to try and understand what these sessions are doing. It seems that they are not waiting for a lock, and thus are not in any sort of deadlock. It seems that a SQL DML statement is active : either an insert, an update, or even sometimes a select statement. The only strange info I found in there is the wait_time equal to -1 :
START_TIME SEQ# WAIT_TIME SECONDS_IN_WAIT STATE
EVENT
----------------- ---------- ---------- ---------------
------------------- ------------------------------
01/03/04 22:48:41 58542 -1 4192 WAITED SHORT
TIME db file sequential read
The "db file sequential read" always seems to be what we are waiting for, but is not relevant for inserts for instance... Also looking in the session_event view one can see that the time waiting for "db file sequential read" does not increase after repeated queries :
START_TIME SID EVENT TIME_WAITED
----------------- ---------- ------------------------------ ----------- 01/03/04 22:48:41 172 db file scattered read 0 01/03/04 22:48:41 172 file open 0 01/03/04 22:48:41 172 buffer busy waits 2 01/03/04 22:48:41 172 SQL*Net more data from client 12 01/03/04 22:48:41 172 SQL*Net message to client 13 01/03/04 22:48:41 172 latch free 21 01/03/04 22:48:41 172 SQL*Net more data to client 41 01/03/04 22:48:41 172 log file sync 49 01/03/04 22:48:41 172 single-task message 547 01/03/04 22:48:41 172 db file sequential read 6126 01/03/04 22:48:41 172 SQL*Net message from client176879
I have not found any relevant messages in the alert.log file.
It seems that the sql statement that is blocked concerns a large table (more than 1 million lines). Also it seems that triggers are involved (except when it is a select statement). The blocked sql statement is either executed by a trigger or would itself launch a trigger. Sometimes, the blocked sql statement is inserting in a materialised view log. However, I am not sure this large table & trigger issue is relevant because most of our business activity takes place on these large tables, two of which have triggers...
I would be very greatfull for any indications or ideas... I myself do not have access to metalink. Would be great if somebody could give it a try.
Thanks,
Justin
Received on Sun Jan 04 2004 - 04:59:07 CST
 |
 |