Re: Request that loses indexes

On Nov 14, 12:11 pm, Che Averell <aver..._at_dalton-brothers.org> wrote:
> ddf <orat..._at_msn.com> écrit :
>
> > 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?
>
> No, not just only one. When one invocation forgets to use an index,
> all following calls will fails also, until we recompile the stored
> proc.
>
> > 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?
>
> It seems not. We have deployed this application on two different
> environments, one for the developement, and one for integration and
> tests, and we have the problems on both plateforms. The first one is
> not as stressed as the second one. On dev, we call that proc about
> once per second, and on the test platform about 2 or 3 calls per
> seconds.

If something is causing one or more of the SQL statements in the procedure to reparse then bind variable peeking can cause behavior like this if the variable values peeked at are not good for the majority of queries.

Execution of DDL against any of the tables referenced by procedure would cause SQL to go invalid and have to be reparsed.

The information I asked for in my first response will probably be necessary for anyone to identify the problem.

HTH -- Mark D Powell -- Received on Fri Nov 14 2008 - 11:48:06 CST