Re: Why my query plan changed ?

My Database version is 10.2.0.3 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 <oracledba.williams_at_gmail.com> wrote: From: Dennis Williams <oracledba.williams_at_gmail.com> Subject: Re: Why my query plan changed ? To: jaromir_at_db-nemec.com Cc: ajayoraclel_at_yahoo.com, oracle-l_at_freelists.org Date: Tuesday, September 16, 2008, 11:47 PM

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