session blocking

[a128]

Platform: Oracle 8.1.7.3 on Solaris 8, no OPS

I have a DB that has session blocking almost every day. There is a front end Java application server that does _not_ use connection pool and the DB is running in dedicated server mode. The problem is, when there is a session blocking all other sessions, I can't even create a new session nor can I use any of existing session to see what's going on with that blocking session. The only thing I can do is to run hang analyze in svrmgrl to get sid, serial# for the blocking session, which does not help much. I was hoping to see which SQL is causing this and to see some wait and event statistics but could not.

I know I can simply kill the process but it does not fix the problem in the long run and it pops up next time around. Because it's from the front end Java app via JDBC, it's hard to identify which session ties to which SQL.

Also, there are no errors in the alert log.

Anyone has any suggestions on how to resolve this?

Please help!

[marist89]

Hmmm. I would probably start a trace on the session that is doing the hanging and another on one of the sessions being blocked.

Connect to the database using something like:

sqlplus "/ as sysdba"

Then, find the sessions and use sys.dbms_system.set_ev to start a level 8 or 12 trace. Then start looking at the trace files to see what each session is waiting on.

Also, I'd probably run statspack and take a snapshot every 5 minutes or so for a little while to see what's going on with my database.

[a128]

marist89,

Thanks for your input! I will try your suggestions.

In the meantime, does it make sense at all an ordinary session can block all other sessions and newly created sessions? I let the blocking session run at least 7 hours before killing it even though our queries by no means are so complicated that it would run that long. The CPU usage for that blocking process flucturated between 40%-50%. All our schema is under single DB user and we don't manipulate data dictionary at all. The server is not lack of memory or HD space and no failed HD detected in the RAID. Though I can connect as sysdba via server manager, the only thing I've tried that is successful is oradebug. Any other sql statements result in session hanging there. Once I killed the blocking session, all my hung sessions come back alive.

Can yuu think of any possible causes something like this may happen?

Appreciated!

[steeve123]

select * from dba_waiters;
this will give you sid of holding_session;

select * from v$locked_object;

where you can see object what that session is locking with SESSION_ID ;


select from dba_objects where OBJECT_ID = object locking;
To see wich object that are lock.