Re: Request that loses indexes
Date: Fri, 14 Nov 2008 09:00:24 -0800 (PST)
On Nov 14, 8:06 am, Che Averell <aver..._at_dalton-brothers.org> wrote:
> 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
> 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