Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ITL Contention on Index?
On Wed, 28 Aug 2002 17:31:08 +0000 (UTC), "Patrick Meyer"
<buckeye234_at_excite.com> wrote:
>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
Most likely your locking issues are accompanied by waits for data
blocks (the term 'data' in this case doesn't discriminate between
table and indexes)
You can check them out using v$waitstat and v$session_event.
Who is that 3rd party vendor? Yet another one that knows zilch about
Oracle?
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address Received on Wed Aug 28 2002 - 15:18:09 CDT