Re: Slow updates for one table with bind variables

"Wolfram Roesler" <wr_at_spam.la> 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: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832

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