Re: Why my query plan changed ?
Date: Tue, 16 Sep 2008 18:47:20 -0500
You didn't mention your database version. Yes, the first time a query with bind variables is executed, a query plan is created and that plan is used for subsequent executions regardless of the change to bind variables. So if you have a query that is strongly affected by input values, you might get a different query plan each time.
You might take the query and test it with different bind variables. Johathan Lewis discusses this a in his book Cost-Based Oracle Fundamentals.
On Tue, Sep 16, 2008 at 3:38 AM, jaromir nemec <jaromir_at_db-nemec.com> wrote:
> > I am seeing lot of query plans are changing in database.
> > I am not
> > doing any changes in database(No DDL) and periodically taking snapshot
> > of sql(v$sql) queries and their plans(v$sql_plan) from memory. I am
> > comparing with sql_id if any plan changed and I often get plan changed
> > messages.
> You didn't mention your policy of gathering object statistics. Change in
> statistics can lead to switch of execution plan as well.