Re: Slow updates for one table with bind variables

Wolfram Roesler wrote:
>
> 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.
>

Are the statitics for table and its columns up to date (gather_schema_stats)?
What about the data distribution in this two key columns? Is there an even value distribution especially for the first key column?

If you use bind variables oracle determines the access plan or the table using the values of the bind variables of the first statement execute. All further executes use the same plan.
So if you have an uneven data distribution and the first value used is very selective, the index access will be used for any of the following input values, although other plans might be better now. Received on Mon Aug 11 2008 - 14:03:20 CDT