Re: Why my query plan changed ?

Ajay,

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.

Dennis

On Tue, Sep 16, 2008 at 3:38 AM, jaromir nemec <jaromir_at_db-nemec.com> wrote:

> Hi,
> > 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.
>
> regrads,
>
> Jaromir
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l