Re: Oracle 8 HANGING! Anyone else?

I have experienced a similar problem in 8.04 on NT 4.0. The (dual) CPUs utilization is 100%, but there are no locks waiting or active, and it looks like no I/O. Nothing can run because we are CPU bound. We are finding 97% of our rows in the buffer. We should be screaming but instead are crawling or dieing.

We have found that the cause of this seems to be runaway threads in NT. I have reproduced this problem and solved it this way:

An user running any client tool such as sqlplus/sqlworksheet/toad/pb60 executes a sql instruction.

sql instruction runs until system error (In our case they were filling rollback segments that could not extend).

Oracle now flags the NT thread as top priority to execute the rollback.

Rollback completes, but thread priority is never reset, and thread never terminates.

pmon fails to cleanup bad thread.

Get a couple of these running and things go down hill very fast. We have learned to identify the explicit NT thread ID, and then orakill the thread.

It has now been two weeks since we found that fix and started using it and performance has been excellent. However, I sit and monitor the server all day, trying to get other work done around it.

We are going to try to fix the problem by re-installing service pack 3 on NT (again) on reports that similar problems have been solved this way. Received on Tue Sep 29 1998 - 20:30:14 CDT