Re: Slow updates for one table with bind variables

From: Matthias Hoys <>
Date: Mon, 11 Aug 2008 19:46:16 +0200
Message-ID: <48a07aee$0$2851$>

"Wolfram Roesler" <> wrote in message news:Xns9AF7988C71010wrgrpde_at_78.46.73.112...
> 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 re-
> creating 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

You might check out this thread on the asktom site:

Matthias Received on Mon Aug 11 2008 - 12:46:16 CDT

Original text of this message