Re: Force specific plan to be used

My 2cents..

Check for v$sql_shared_cursor to identify what is the cause from multiple child cursors. If the same table is shared by multiple schema's then possibly we have different records for the tables. Application might be using one common admin user and just switching to respective schema's using "alter session set current_schema..." This can cause multiple child cursors with probably with same plan as all of them have same sql text eventually same sql_id.. however underlying data might be different..

BR,
Rakesh RA

On Fri, Nov 1, 2019, 07:05 Tanel Poder <tanel_at_tanelpoder.com> wrote:

> Even if the plan hash values are the same, go still ahead and compare the
> predicate sections of the good vs bad child cursors.
>
> Predicate existence (or placement) is not part of the plan hash value - so
> how early you're filtering the rows may differ.
>
> Tanel Poder
> https://blog.tanelpoder.com/seminar
>
> 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