Re: Force specific plan to be used

So, to be clear, the all the cursors have the same plan ? (Both good and bad) ? If that's true , then a sql plan isn't the problem. Sounds like one of the child cursors is working on a lot less data if its using the same execution plan and performing much better.

However, if the one good cursor is showing a different plan, look up the coe_xfr_sql_profile.sql from Carlos. It takes a SQL_ID and a PLAN_HASH_VALUE and creates an sql script to force a SQL profile for that specific plan. Then you execute the sql script that is generated to create the profile in the db.

Chris

On Thu, Oct 31, 2019 at 2:03 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> I have a couple of SQL statements that have multiple child cursors. Each
> child cursor has the same plan hash value. The plans all show "this is an
> adaptive plan (rows marked '-' are inactive)". The difference is that on
> the "good" child cursor, the plan also shows "statistics feedback used for
> this statement". Since all of the plans have the same plan hash value, I
> can not use baselines (or can I).
>
> Any suggestions on how to force Oracle to always use the "good" child
> cursor.
>
> Jeffrey Beckstrom
> Lead Database Administrator
> Information Technology Department
> Greater Cleveland Regional Transit Authority
> 1240 W. 6th Street
> Cleveland, Ohio 44113
>
>

--
http://www.freelists.org/webpage/oracle-l