Slow updates for one table with bind variables

Hello,

I have an application that queries and updates several table in a transaction every few seconds. I recently modified the application to use bind variables instead of ad-hoc SQL. With that modification, updates to one particular table have become slow: They used to be in the millisecond range but now sometimes take several seconds to complete (most of the time, however, they are as fast as ever). During the nightly online backup, sometimes they even take several minutes, locking the application during that time.

Updates to other tables are still as fast as they have ever been.

The update in question is for a table that has about 3000 rows. The WHERE clause is of the form "WHERE key1=value1 AND key2=value2", where key1 and key2 are the two primary key columns of the table (one is VARCHAR2 and the other is DATE). An index exists for these columns. This update is executed every few minutes (with different values for the updated columns and for the primary key, and possibly with a different set of updated columns) and normally finishes within a couple of milliseconds.

The problem only occurs with that particular database. Deleting and recreating  the table didn't help.

We are using Oracle 9.2 on a Windows server. The application is running on the database server, connected via ODBC.

Does anyone know a possible reason for the problem, or how to trace it down further?

Thanks for any help
Wolfram Rösler Received on Mon Aug 11 2008 - 07:59:46 CDT