database hangs

oracle 8.1.6
solaris 2.6
4cpus at 400mhz
4gig of memory

running in archivelog mode
MTS config =

		mts_dispatchers      = "tcp,4"
		mts_max_dispatchers  = 10
		mts_servers          = 2
		mts_max_servers      = 10

total of 3 instances on one box.

intermittently over the past 3 days, one particular instance hangs. no sqlplus connections are allowed, but no error message is generated. it just hangs. i've logged onto svrmgrl to investigate, and i think it may be locking issues (tho, not sure). i've search metalink for locking docs, and i've ran query after query to see what/which query is causing the database to hang. i'm guessing there is a deadlock, but wouldn't that generate a trace file and you would get an ORA-60 error? i've received no errors anywhere.

as soon as the database hangs, the alert.log is not written to anymore... it just freezes. only when i issue a shutdown immediate, does the alert log kick in again.

Thu Oct 19 11:17:34 2000
ARC0: Beginning to archive log# 1 seq# 7267 ARC0: Completed archiving log# 1 seq# 7267 Thu Oct 19 12:05:20 2000
Shutting down instance (immediate)
License high water mark = 230

i've searched metalink under transactions, mts, hung database, locked objects... still no luck in determining the problem. i've submitted a TAR through metalink, but still no word.

this is a production database, which effectively freezes all of our other sites when this instance goes down. so if this one db crashes, our 5 sites (condenet.com) hang as well.

the programmers are using KIVA, which is netscape application server, to communicate with the database. so, KIVA connects to the db, not the web machines.

every time the db hangs, i query v$locked_object and i come up with the same object. an application is opening multiple sessions executing the same exact delete. while the delete executes, a select is run against the table.

could a locked object bring a database to its knees and not allow any more connections? everyone points their finger at the db, while i think it's the code that is causing the hanging. but if it is the db, i haven't found a solution to this very difficult problem.

any thoughts on this would be most appreciated! Received on Tue Oct 24 2000 - 10:45:33 CDT