Why did the plan change

Hi,
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.

Thanks

Quote:

---

Originally Posted by bhatia333

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.

---

Execution plan over time changes can be found by querying AWR's dba_hist_sql_plan view.

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.