Re: Slow updates for one table with bind variables

On Aug 11, 5:59 am, Wolfram Roesler <w..._at_spam.la> wrote:
> 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

This kinda "sounds like" the situation of the updates backing up and slowing each other down, thrashing the heck out of the SGA (and maybe undo) while attempting to keep up consistency. Run statspack several times an hour including the times it slows down, I'll bet you'll see some very interesting wait states. Also you might try the tools under OEM that show the executing plan and database wait states.

jg

--
@home.com is bogus.
For a good laugh, see metalink bug 5001575.  See how long before the
end you guess what is wrong.