Stan Kharab, February 19, 2009 - 11:52 pm UTC
Hi Tom
Can you please elaborate more regarding algorithm behind AUTO_INVALIDATE. You mentioned that "over the course of a SHORT period of time, all dependent cursors will invalidate themselves but not all at once".
Is there any parameters that influence when/what will be invalidated? Do size of shared_pool , number of objects/cursors, type of stats gathered, or anything else play any role in calculation to what/when will be invalidated?
Thanks
Stan
February 21, 2009 - 8:44 pm UTC
it is undocumented, it can and will and probably already has changed over time, it is automated, it is self tuning based on how the Oracle kernel developers feel it should be.
Basically you can either
a) invalidate everything right here, right now, right away and suffer the resulting hard parse storm
b) invalidate things casually, more gently, over time...
It is important to know that (b) is the default in current releases however, because it does change what you should expect to see after a dbms_stats run - you won't see the hard parse storm you used to see, and that might be confusing as we are "used" to seeing dependent sql going invalid and re-hard parsing right away. Some people *rely* on that (eg: they gathered stats in order to flush that sql from the shared pool and now it doesn't..)
A small correction
volkerk, October 01, 2009 - 8:56 pm UTC
Thanks for stumping me at this useful parameter, Tom.
Although the parameter is actually called "no_invalidate", and to force immediate re-parsing due to the changed stats when queries on the object are next executed you have to set it to FALSE (in other words, invalidate=TRUE, but that parameter does not exist).
great help
A reader, December 17, 2010 - 11:59 am UTC
thanks so much for this post, i thought i was going crazy after we upgraded to 11g, now i understand!