Re: Request that loses indexes

On Nov 14, 8: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,

You have a stored procedure which is called 3 times every second:

86400 seconds/day * 3 runs/second == 259200 executions per day

and only ONE of those runs 'forgets' to use an index?

I, for one, question why this procedure runs 3 times per second to begin with. And a 'failure rate' of 1/259200 is 0.0004%. If you run this once per second instead of 3 times per second does this 'problem' disappear?

David Fitzjarrell Received on Fri Nov 14 2008 - 11:00:24 CST