Re: Optimizer issue in 11g

From: Gerard H. Pille <ghp_at_skynet.be>
Date: Wed, 03 Feb 2010 20:20:38 +0100
Message-ID: <4b69cc85$0$2865$ba620e4c_at_news.skynet.be>

Jonathan Lewis wrote:
> The obvious guess is that there is some variation in bind
> variables used to run the query, and someone uses a value
> that produces a plan that's good for them but bad for everyone
> else.
>
> Since you have execution plans that take hours to complete
> you should have time to query v$sql_plan to find the bind
> variables used to generate the plan. If you can do the same
> when a good plan is running, this may give you a clue about
> the problem.
>
> You'll need to find the SQL_ID of the statement then make a
> call the appropriate call to dbms_xplan
>

...
>

Indeed, and if you're sure to have a plan that should fit all queries, you could freeze it with an outline. Received on Wed Feb 03 2010 - 13:20:38 CST