Re: Priority of profile baseline patch

I think that hint( OUTLINE(_at_"SEL$3) should not make this issue a non-reproducible old plan. However, to understand whether the presence of unusable index partitions are causing this , can you post the explain plan for a simple query on that table MDD with a filter only on the column on which that index is created (may be along with the partition key also)?

On Fri, Dec 24, 2021 at 8:01 PM Pap <oracle.developer35_at_gmail.com> wrote:

> 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