ITL Contention on Index?

Environment: Oracle 8.1.7.3 Enterprise Edition, AIX 4.3. Third party application running on Windows 2000 Server application servers, clients run on Windows 2000 Workstation. Client applications communicate with application servers, which sends the SQL to the database. The app servers have persistent sessions to the database and multiplex the client transaction to the database.

Problem: I have a high-volume OLTP database. Some afternoons, between 3:30 and 4:30 we start getting blocking locks on the database. This is when the offices that are coming online the next day start entering seed data for inventory. The locks cause other processes to wait, which causes the application on the client workstation to hang. It's a TX lock and mode held is 6. All lock request modes are 4. My first thought is ITL contention on the table. Upon further investigation, I found that session holding the lock is doing massive inserts. At least one of the waiting sessions is doing massive inserts also. Since inserts shouldn't block other sessions, my attention turned to the indexes. The table has only one index, the primary key. It is not a bitmapped index. The current solution is to stop the process on the application server that is holding the lock.

Question: How can I verify that this is an ITL problem, and if it is on the index? We are currently in the middle of a massive roll-out of the application. The third-party vendor wants to change the inserts to commit more frequently, like every fifty rows. I would like to avoid the performance hit of the extra commits if the problem is a configurable storage problem. But I need to know how to verify the actual problem.

Thanks in advance for any help you can give.

Later,
Patrick

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG