Small confusion for point a
Alok Kumar, July 07, 2020 - 7:09 am UTC
Thanks Tom for quick reply. Appreciate your help.
———————————
I have a small double for point a.
When you say Auto_Invalidate will spread out the hard parsing for all the 1000 queries , how? Default value of auto invalidate is 5 hours. So my understanding is after 5 hours of running the stats gather job, hardparsing will start immediately for all these queries. So, for any reason if my stats gather job has produced some bad plans it’s effect and system slowness will be visible after 5 hours. Please correct me if i am wrong.
Thanks
July 08, 2020 - 6:40 am UTC
Think of it like a "raffle" or "lottery".
- We have an SQL with an existing plan.
- We gather stats which mean (theoretically) that plan should ditched and the query re-parsed
- We take that SQL and "roll the dice" that comes up between 1 and 18000. If the number of seconds elapsed since gather stats is more than our number, then we parse, otherwise we keep using the old plan.
Thus over the course of 5 hours, we'll slowly parse everything again (assuming its requested to run again).
NO_INVALIDATE in Oracle19
Andy, November 11, 2021 - 2:40 pm UTC
Hi Tom,
Above question was related to parameters in oracle11g, I am upgrading database to 19.
Could you please let me know if NO_INVALIDATE parameter is still valid for Oracle19? Any documentation related to this will help.
Thanks
November 12, 2021 - 4:54 am UTC