Re: Cause behind execution plan change
Date: Wed, 21 Jul 2021 01:29:06 +0530
Message-ID: <CAKna9VY9SWvGL1c_J41dZyuFfJyS4BC=WMxLvTYwhJfqgqTZbA_at_mail.gmail.com>
*"The note part will not show you if a baseline was used" *
On Wed, Jul 21, 2021 at 12:51 AM Laurentiu Oprea < laurentiu.oprea06_at_gmail.com> wrote:
> The note part will not show you if a baseline was used, will show you only
> if a SQL patch was used ( and I think a SQL profile).
>
> Based on your comments most probably you had a baseline in place.
>
> Some solutions can be:
>
> 1. Review the hints inside your code, consider Jonathan's comments, also
> consider to hint the index usage as well.
>
> 2. You can use a SQL patch to fix current problem to attach the index hint
>
> Thanks.
>
> On Tue, Jul 20, 2021, 14:37 Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank You Jonathan and Lok. Attaching again the query along with the
>> outline and note section.
>>
>> I am seeing one usage of INTERNAL_FUNCTION around FFT.STCD but in the new
>> plan(post function change), I am seeing two more usage of INTERNAL_FUNCTION
>> around the ND.NE column. These columns are the same with respect to the
>> data type in both sides of the predicate, why are these appearing and if
>> anyway these are responsible for some wrong estimation?
>>
>> I had checked the dba_hist_sqlstat but didn't see any profiles attached
>> for the old sql and als checked the plan from display_awr and the note
>> section was only showing below i.e. usage of dynamic sampling only and
>> nothing regarding sql profile or plan baselines either. But then when I
>> query dba_sql_plan_baselines manually with the sql_text like '%...sample
>> query text...%', I saw one entry there with ACCEPTED and ENABLED both
>> columns set as 'YES'. And also the signature is matching with the query
>> force_matching_signature. And I can see the last_executed column was also
>> showing the date close to when we introduced the new modified sql into
>> prod. So it seems this was the one getting used for old sql/query but the
>> note section does not state that.
>>
>> So is it true that it may be possible that the note section of the
>> display_awr function won't show the usage of profile/baseline but still it
>> may be used by that query internally?
>>
>> Note
>> -----
>> - dynamic statistics used: dynamic sampling (level=2)
>>
>> On Mon, Jul 19, 2021 at 12:47 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> You shouldn't be using the ORDERED hint, by the way, you should learn
>>> how to use the LEADING() hint.
>>> And since you've dictated the join order for this query FT does not
>>> need to be in the USE_NL() hint because it's the first table in the join
>>> order so it's not going to appear as the second table in any of the joins.
>>> (See: https://jonathanlewis.wordpress.com/2017/01/13/use_nl-hint/ , and
>>> for the equivalent comment on the use_hash() hint see:
>>> https://jonathanlewis.wordpress.com/2013/09/07/hash-joins/ )
>>>
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Sun, 18 Jul 2021 at 20:35, Pap <oracle.developer35_at_gmail.com> wrote:
>>>
>>>> Hello listers, It's version 12.1.0.2.0 of oracle. We have done a
>>>> change to the code inside the function which gets called from the SELECT
>>>> query. But as its just been used in the SELECT part of the query ideally it
>>>> should not change sql_id of the query and also the plan, but we also add
>>>> one new additional input parameter(i.e. :B3 below) to the function call and
>>>> thus sql_id got changed which is understood. But something which we are not
>>>> able to understand is , why did the plan change occurred after this change?
>>>>
>>>> Attached is both the plans i.e the one it used to take in the past vs
>>>> the current one which it's now taking. From the plan it does look like ,
>>>> its cardinality estimation of global temporary table FT which causes the
>>>> difference, as it puts table RTNID in index access vs FTS access in a
>>>> nested loop. But the old query(before function change) was not taking the
>>>> bad plan ever, but it started taking after function change. So wondering
>>>> how a new input parameter addition to a function which is not part of the
>>>> WHERE clause, can cause this sort of impact and how to fix it?
>>>>
>>>> In this query, all the tables are global temporary tables except FFT,
>>>> which is a list partition table with partition key as CKEY.
>>>>
>>>> INSERT INTO RTF(...)
>>>> SELECT /*+ ordered use_nl(ft FFT nd curr)*/ ND.NE, ND.NID, CUR.SCD,
>>>> FT.FXID, FT.TFXID,
>>>> fun1 (FFT.AMT, FT.STS, FT.PDT, :B3, TRUNC ( :B2), 'S'),
>>>> fun1 (FFT.AMT, FT.STS, FT.PDT, :B3, TRUNC ( :B2), 'F'),
>>>> TRUNC ( :B1),
>>>> ND.MCID
>>>> FROM FT , FFT , RTNID ND, RDCUR CUR
>>>> WHERE FT.FFXID = FFT.FXID
>>>> AND FT.ACK = FFT.CK
>>>> AND FFT.CKEY = ND.NKEY
>>>> AND ND.NE IN ('XX', 'YY', 'ZZ')
>>>> AND FFT.STCD IN ('X', 'Y')
>>>> AND FFT.CKEY = CUR.CKEY
>>>>
>>>>
>>>>
>>>>
>>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 20 2021 - 21:59:06 CEST