Request that loses indexes

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, Received on Fri Nov 14 2008 - 08:06:08 CST