Re: Force specific plan to be used

Can you please confirm the difference in the execution plan you’re looking to replicate? Remember that anything in the notes is usually an explanation of how it got that plan, it’s not necessarily about what it’s going to do with it.

If this is about predicate placement then a SQL patch could do the trick. I had a similar symptom where a large in list went from being an access predicate to a filter. The proper fix would have been to fix the statistics on the underlying objects, but who has time for testing that ;)

Regards,
Andy

On Fri, 1 Nov 2019 at 17:41, Henry Poras <henry.poras_at_gmail.com> wrote:

> Also, nearly all of the time difference is in cpu_sec_per_exec. The
> elapsed time difference posted above is ~2.5 seconds, 2 seconds of which is
> CPU.
>
> Henry
>
> On Fri, Nov 1, 2019 at 11:41 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> Excellent suggestion.
>>
>>
>>
>> Now I haven’t read the full thread yet, but I immediately have 3
>> questions (that may be in there when I dig):
>>
>>
>>
>> 1) Parallel query?
>>
>> 2) RAC?
>>
>> 3) Force parallel query local?
>>
>>
>>
>> A wide variety of hilarity can be seen in RAC parallel query NOT forced
>> local on most of 12.1 (and some of 12.2). Hmm actually it can happen forced
>> local on 12.1, but I think that at least was patched for 12.2. A symptom of
>> the BUG I reference would be either parallel forced to serial (having given
>> up trying to get identical FULL HASH on child plans) or a long parse time
>> finally getting the matching children to feed to the coordinator.
>>
>>
>>
>> That may be a zebra whilst you’re looking for a sick horse, and there are
>> a lot of fine responses from the few I’ve read in the thread, so take my
>> comment with a salt shaker.
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sayan Malakshinov
>> *Sent:* Friday, November 01, 2019 1:23 AM
>> *To:* breitliw_at_centrexcc.com
>> *Cc:* ORACLE-L; JBECKSTROM_at_gcrta.org; tanel_at_tanelpoder.com
>> *Subject:* Re: Force specific plan to be used
>>
>>
>>
>> Hi all,
>>
>>
>>
>> Since OP is talking about adaptive plans with multiple child cursors, I
>> would check *FULL_PLAN_HASH_VALUE *and* PLAN_HASH_2. *
>>
>>
>>
>>

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