Alert If Explain Plan Changes?
591186Oct 2 2008 — edited Oct 3 2008All,
Oracle Version:10.2.0.3
Os: SunOS x86-64
We had some issues with a specific SQL's which were running fine in the past and suddenly started running poorly,created a drastic performance to the application. This happened during our stats collection process. CBO started picking up a wrong index,instead of the right index. we estimate sample percent on all of our schema objects and after seeing this, we computed statistics on that particular table which resolved the issue and sql was running fine now. There was a debate going that whether stats was the problem or why CBO picked up a wrong index suddenly?
Coming to my clarification, Is there a way to create an alert if the explain changes to a specific SQL? we have some third party tools available to monitor our servers,application,databases and have explored the options.they didnt this feature.
Let me know your thoughts.
Anantha.