Re: Why my query plan changed ?
Date: Tue, 16 Sep 2008 17:01:36 -0700 (PDT)
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
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
You didn't mention your policy of gathering object statistics. Change in statistics can lead to switch of execution plan as well.
JaromirReceived on Tue Sep 16 2008 - 19:01:36 CDT