Re: Analyze /LMT Question .....

Hi!

Yes, we were talking about different issues here.

Tanel.

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:pan.2003.02.24.02.46.39.392723_at_yahoo.com.au...
> On Mon, 24 Feb 2003 03:17:47 +0200, Tanel Poder wrote:
>
> > Hi,
> >
> >> You do not *need* to recompile everything, but procedures etc.
referencing
> >> tables for which you've re-collected statistics or rebuilt indexes will
be
> >> invalidated, automatically.
> >
> > Erm.. what is this? I can't agree with that.
>
> Depends what you think you are agreeing or disagreeing with, really. Your
> examples are of procedures etc. and their state of validity as procedures.
> Which is fair enough, and if that's what you thought I meant, then I'm at
> fault for writing it open to that interpretation. There was, however, an
> 'etc' there, meaning that I was actually referring to anything already
> loaded in thelibrary cache, for which an execution plan has already been
> compiled.
>
> Those most definitely will be invalidated by DDL, create index, or
> fresh analysis of statistics (and a good thing too, of course, since new
> indexes or new statistics could well mean that a better plan could be
> derived given half a chance).
>
> Those execution plans are invalidated, and will be recompiled the first
> time they are referenced following the invalidation (second time if they
> involve remote calls).
>
> > It would be quite nonsense to recompile hundreds or thousands objects
after
> > every analyze in some apps.
>
> It might be nonsense, but that's exactly what happens in the library
cache.
>
> > DDL on an object invalidates it's dependent objects. I don't think any
> > object can be dependent on an index for example. And analyze isn't DDL.
>
> It's in the same class of statements as DDL, namely: it invalidates any
> execution plans in the library cache referencing the object for which new
> statistics have just been computed/estimated.
>
> HJR
Received on Mon Feb 24 2003 - 09:35:10 CST