Re: Oracle session hangs

"Justin Ziegler" <ziegler_w_justin_at_yahoo.fr> a écrit dans le message de news: 37feadaa.0401040259.623b0cc5_at_posting.google.com...
> 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 client
> 176879
>
>
> 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

Hi,

First, the advice about hotsos.com and the 10046 event is really a good one :-) Go on http://asktom.oracle.com too, it's perhaps the best ressource on the web about Oracle.

OK, so the problem is : you have a big Linux box, a 200GB database, lots of things (coming from differents applications) are running fine and some sessions are hanging.

We will consider that the problem is NOT due to cartesian product due to false statistics in 8i.
We will consider that when you session are hanging, the other are running fine.
We will consider that it's not an archivelog problem.

So you should be able to know which application launch thoses hanging sessions... ? is it the same aplpication ? which one is it ? what is the programmation language (VB ???) ?

What is interesting in your session_event is that the main wait event for your session is "SQLNet message from client" 176879/6126 makes db_file_sequantial_read anonying... "SQLNet message from client"is usually considered as an "idle" event but in your case it represents more than 99% of your wait events... so you can't ignore it. That "smells" that the application which is lauching thoses hanging sessions is the problem. We often meet thoses problems with VB applications cause the code is made like that (the SQL is in the VB code and NOT in the database !) :

for each line of a particular condition

1. send a request to the datanase
2. wait for the answer
3. made something with the line receive from the database ...

The result is a loooooooooots of waits from an answer from the database or the client cause of too many database calls because the code is bad written (writtent by VB developer, not by SQL developers). Imagine a thing like i wrote with a billion of line table (like you have). You will send a billion of request to the database which will read a billion of time one line (which will increase the db_file_sequential_read wait event), will send back a billion of "one line each time" through the network increasing the "SQL Net message from client wait event...)
The problem is not your database here but the network traffic due to a bad writtent applcation...

Good luck,

Fred Received on Tue Jan 06 2004 - 12:50:12 CST