Re: Request that loses indexes

On Nov 14, 9:06 am, Che Averell <aver..._at_dalton-brothers.org> wrote:
> Hello,
>
> We have a problem with an Oracle 10g database.
>
> Whe have a fairly big schema, with several tables of more a million
> records, and about 300 tables.
>
> We made a stored procedure that makes several requests. That request
> works perfectly well and it uses the indexes on the several tables
> used. But once per day, that request unexpectedly forget to use the
> index, and the perfomance severely drops. The only way we found to
> force the stored proc to use the index again, is to recompile the proc
> again.
>
> We tried to add a hint into the request, but inside a stored proc
> that hint seems not be followed.
>
> This proc is called 2 or 3 times per second.
>
> Compiling statistics manually once per day or automatically more often
> does not solve the problem.
>
> Do you have any clue to solve this issue ?
>
> Thanks a lot,

So once per day the SQL plans behind the stored procedure go to lunch by all of a sudden not using the indexes.

The following would be helpful
The full Oracle version.
The good and bad explain plans for the statement in question.

When the query goes bad is the input to the procedure the same set of values?

Do you have any daily jobs/tasks that perform DDL operations on any of the referenced objects?

Do any of the tables in question suffer from skewed data?

HTH -- Mark D Powell -- Received on Fri Nov 14 2008 - 08:46:32 CST