Re: Priority of profile baseline patch

Thank You Jonathan.
My apology for the confusion regarding profile hints. Actually I have fetched the hints of the profile from comp_data column of the DBMSHSXP_SQL_PROFILE_ATTR. And because the table aliases were revealing the exact objects names, I have manually replaced those with different aliases/names. And replaced the other characters at the starting and ending of each hint , which was something as below. <outline_data><hint><![CDATA[
BEGIN_OUTLINE_DATA ]]></hint><hint><![CDATA[ IGNORE_OPTIM_EMBEDDED_HINTS ]]></hint><hint><![CDATA[ OPTIMIZER_FEATURES_ENABLE('19.1.0') ]]></hint><hint> ]]>

Now as you suggested, I tried comparing the sorted outline/hints section of the sql profile(which i had fetched from DBMSHSXP_SQL_PROFILE_ATTR) with the outline section of the good plan baseline collected from the AWR(i.e from dbms_xplan.dispay_awr). And I see no other difference than a hint ''OUTLINE(_at_"SEL$3")".
It's there in the good plan but not in sql profile hints. But I do see this block 'SEL$3' usage in the profile hints section , so hopefully that won't matter then.

Attached is the sheet holding sorted hints/outline sections of the profile , the good plan baseline and the bad sql plan baseline. And the Bad plan hint report showing ~44 unresolved hints. I am not able to figure out anything specific from this report though.

However, I am still wondering if the unusable index partitions(~95 unusable out of total ~440 partitions) of the table mdd can be the cause of this issue of non reproducible baseline , even though that table is going for full scan in the plan?

On Thu, Dec 23, 2021 at 2:30 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> That "profile" looks suspect. I wouldn't expect any of the hints with
> lower case contents (whether query block names or table aliases) to have
> any effects unless the referenced query blocks / aliases in the query were
> also lower case and double-quoted. To me it looks as if someone edited the
> text before trying to save it (and the double brackets look suspect, and
> the closing HTML tags shouldn't be there either).
>
> Does the query show anything in the hint-report for the plan (and you'll
> probably have to pull a live one from memory to be sure), and what do the
> 44 hints that you mentioed in the "unused hints" section of the report look
> like when you get a bad plan.
>
> As an investigative exercise you could also extract the profile hints, and
> then the outline information from the plan when it's good and compare the
> two sets (it's easiest if you do an alphabetical sort first) to see whether
> there are any point where they disagree.
>
>
> Regards
> Jonathan Lewis
>
>
>
> On Wed, 22 Dec 2021 at 11:58, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Thank You So much Jonathan.
>>
>> Attached is the sql hints/outline of the profile which is pushed through
>> dbms_sqltune.import_sql_profile procedure.
>>
>> And yes this query is having two of the tables MTD and MDD both are
>> partitioned. And there are ~97 index partitions for table MDD for one of
>> the indexes which is in UNUSABLE state. However that query is going for a
>> full table scan on that table MDD in the plan.So can that still cause such
>> a plan issue because of those unusable index partitions? But yes that
>> column on which index is created is part of the join condition in this
>> query, is it because that optimizer is going for that table expansion Or
>> dividing the query evaluation into multiple pieces, even though showing
>> full scan in the plan in each of the expanded sections?
>>
>> expand_table hint is used for MDD table in the outline section even for
>> the sql profile. So wondering , as Lok pointed, how this OR_EXPAND of two
>> predicates now becomes non reproducible and it's going for three
>> predicates/sets plan?
>>
>> On Wed, Dec 22, 2021 at 3:42 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> You've got Table expansion, OR-expansion, and Join factorization going
>>> on in the old plan, but the join factorization does not occur in the new
>>> plan.
>>>
>>> The significant factor is probably the Table expansion - this suggests
>>> you have some partial indexing in place, or some partitions of local
>>> indexes that are currently unusable. If there was something about the
>>> pattern of "missing" index data that FORCED oracle to use a 3-way
>>> OR-expansion then everything else follows from there.
>>>
>>> Have you posted the content of the profile yet ? If so I missed it.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Mon, 20 Dec 2021 at 20:19, Pap <oracle.developer35_at_gmail.com> wrote:
>>>
>>>> Thank You So much Jonathan.
>>>>
>>>> I think you are spot on wrt the OR expansion point. The plan which I am
>>>> seeing now is not getting reproduced and thus causing a new
>>>> baseline generation is having OR expansion. And thus the total number of
>>>> lines in this new plan is ~113 as compared to ~87 in the earlier plan. I
>>>> have attached the sample query with the old plan and the new one with their
>>>> respective outlines.
>>>>
>>>> But yes the question would be why optimizer is now not able to produce
>>>> the old plan(i.e mostly without OR expansion). No such object
>>>> definition change or parameter setup changes have been done. Statistics are
>>>> getting gathered on a daily basis on the underlying objects but that should
>>>> not cause such issues. And this plan suddenly appeared after a
>>>> particular date and is continuing now. And the note section in the new plan
>>>> is showing the section below (i.e. it failed to reproduce the baseline).
>>>> And used the sql profile, but the sql profile plan/old plan is not exactly
>>>> the same as this one.
>>>>
>>>> We have not had any sql patch created here but yes the sql profile was
>>>> not created using the traditional method(which would have a bunch of
>>>> opt_estimate hints) rather its created by forcing the exact outline hints
>>>> of an awr plan i.e the old plan in the attached doc. So do you think that
>>>> forced outline hints through sql profile can cause such an issue?
>>>>
>>>
>>>

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





text/plain attachment: Bad_plan_Hint_report.txt



application/vnd.openxmlformats-officedocument.spreadsheetml.sheet attachment: Hints_Section_Compare.xlsx