Re: Why my query plan changed ?

From: Ajay Thotangare <>
Date: Tue, 16 Sep 2008 17:01:36 -0700 (PDT)
Message-ID: <>

My Database version is on HP-UX Itanium. My cursor_sharing parameter is force so will it still generate new explain plan?

  • On Tue, 9/16/08, Dennis Williams <> wrote: From: Dennis Williams <> Subject: Re: Why my query plan changed ? To: Cc:, Date: Tuesday, September 16, 2008, 11:47 PM

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:


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





Received on Tue Sep 16 2008 - 19:01:36 CDT

Original text of this message