Re: ITL Contention on Index?

"Patrick Meyer" <buckeye234_at_excite.com> wrote in message news:<d22285710d37ec23f320d5028596283f.61632_at_mygate.mailgate.org>...
> 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.

Patrick,

If the statement is an INSERT, and TX mode 6 is held and 4 is waited on, then you're right, it's not a wait due to ITL shortage on the table, since Oracle will never wait on ITL on an INSERT into the table. (It will simply pull another block off the freelist.)

In this case, I'd look at ITL shortage on the index block (though in my experience, this is a rare occurance), or (more likely, in my opinion) overlap on insert of unique values, where a unique index and/or unique constraint exists. If the column 'ID' has a unique key, session 1 inserts ID=1 and does not commit, and then session 2 tries to insert ID=1, then session 2 will wait in mode 4. This wait will be on the index block where session 1 holds a mode 6 lock.

Hope that helps,

-Mark Received on Thu Aug 29 2002 - 11:57:31 CDT