Re: Why my query plan changed ?

From: Dennis Williams <>
Date: Tue, 16 Sep 2008 18:47:20 -0500
Message-ID: <>


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 <> 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
> --

Received on Tue Sep 16 2008 - 18:47:20 CDT

Original text of this message