Environment Details: Oracle 12 (12.1.0.2)
OS: Linux
Execution plan has been changed for a gather stats job which was working well and approx execution time was 10-15 min. Suddenly, one fine day it got stuck and took more than half an hour to complete even 50%. After investigation we found that plan has changed. So on pinning the old plan it went well. Reason was it has started doing the Full table scan on the WRI$_OPTSTAT_SYNOPSIS_HEAD$ table rather than using the index I_WRI$_OPTSTAT_SYNOPHEAD which was happening in the good plan.
Now, my question is how can i find out that what makes optimizer to change the plan suddenly. I know there could be couple of reasons like-
1. Probably huge data load in the partition tables or more number of partitions has been added which actually increase the size enormously for tables like WRI$_OPTSTAT_SYNOPSIS$.
2. Change in the gather stats job proc itself.
3. Or there could be so many unknown reason which i might not aware of, so asking for experts opinion.
Please let me know how can i find out to make sure that this X or Y or Z whatever is the reason that the plan got changed.
Regards,