Why did the plan change
I know I am going to be hounded for this, but my question is truly a generic one. But if you want specifics, then consider it to be a 10.2.0.4 database on linux machine.
My question is how do I find out why a plan changed for a query. I know the probable causes can be data changes (which is not the case I am looking into) or stats change or index rebuild. But my question is how do I pin point the cause of a plan change (what views/reports can help). I know the plan has changed, now what caused it keeping in mind its not a data change and how do I find it out with the help of DBA_HIST or V$ views.
When in doubt ...go to the basics!!
Execution plan over time changes can be found by querying AWR's dba_hist_sql_plan view.
Originally Posted by bhatia333
Once you get the two execution plans you have to infer what happened from what you see e.g. an UNIQUE INDEX SCAN going FULL TABLE SCAN, or whatever differences you see.
If data really didn't change at all and no configuration changes to the database happened my prime suspect would be performance stats.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width