Re: Priority of profile baseline patch
Date: Sat, 25 Dec 2021 19:18:57 +0530
Message-ID: <CAEjw_fjausJvUwXtUF1yUURThBAhpTb6egAbBetqXMjiioZT-A_at_mail.gmail.com>
Thank You So Much.
I just fetched the explain plan by using ' autotrace traceonly explain' command and I see that there exists multiple UNION ALL must be because of the existing unusable index partition(~94 out of total 442). But along with that I also noticed the table partitions(mainly historical partitions) are compressed using 'archive high'. ~388 are compressed and ~54 partitions are non compressed. Will that also play a role in such a table expansion plan?
But I was thinking maybe the outline section of the sql profile might contain any such hard coded information regarding the partitions which might be one of the reasons why the plan is not reproducible with a new set of compressed table partitions or unusable index partitions. But I don't see such information in the hint section of the sql profile. So why would it then cause the old plan non reproducible?
SQL> select * from MDD where trn=':b1' and part_dt
=to_date(':b2','DD-MON-YYYY');
Elapsed: 00:00:00.05
Execution Plan
Plan hash value: 4011258023
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 181 | 43558
(6)| 00:00:02 | | |(6)| 00:00:02 | KEY | KEY |
| 1 | VIEW | VW_TE_2 | 2 | 724 | 43558
(6)| 00:00:02 | | |
| 2 | UNION-ALL | | | |
| | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 181 | 43558
(6)| 00:00:02 | KEY | KEY | |* 4 | TABLE ACCESS STORAGE FULL| MDD | 1 | 181 | 43558 (6)| 00:00:02 | KEY | KEY |
| 5 | PARTITION RANGE SINGLE | | 1 | 181 | 43558
(6)| 00:00:02 | KEY | KEY | |* 6 | TABLE ACCESS STORAGE FULL| MDD | 1 | 181 | 43558
Predicate Information (identified by operation id):
4 - storage("trn"=':b1' AND ("MDD"."part_dt">=TO_DATE(' 2021-04-10 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "MDD"."part_dt"<TO_DATE(' 2022-02-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE(' 2021-04-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "MDD"."part_dt">=TO_DATE(' 2021-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR
"MDD"."part_dt"<TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND
"part_dt"=TO_DATE(':b2','DD-MON-YYYY'))
filter("trn"=':b1' AND ("MDD"."part_dt">=TO_DATE(' 2021-04-10
00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "MDD"."part_dt"<TO_DATE('
2022-02-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE(' 2021-04-06
00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "MDD"."part_dt">=TO_DATE(' 2021-03-06 00:00:00',
'syyyy-mm-dd hh24:mi:ss') OR
"MDD"."part_dt"<TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-02-19 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')) AND
"part_dt"=TO_DATE(':b2','DD-MON-YYYY'))
6 - storage("trn"=':b1' AND ("MDD"."part_dt"<TO_DATE(' 2021-02-19
00:00:00',
'syyyy-mm-dd hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE(' 2021-04-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MDD"."part_dt">=TO_DATE(' 2021-04-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
OR "MDD"."part_dt"<TO_DATE(' 2021-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) AND
"part_dt"=TO_DATE(':b2','DD-MON-YYYY'))
filter("trn"=':b1' AND ("MDD"."part_dt"<TO_DATE(' 2021-02-19
00:00:00',
'syyyy-mm-dd hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE('
2021-04-10 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MDD"."part_dt">=TO_DATE(' 2021-04-06
00:00:00', 'syyyy-mm-dd hh24:mi:ss')
OR "MDD"."part_dt"<TO_DATE(' 2021-03-06 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')) AND
"part_dt"=TO_DATE(':b2','DD-MON-YYYY'))
SQL> select * from MDD where trn=':b1';
Elapsed: 00:00:00.03
Execution Plan
Plan hash value: 3641660968
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | |
1 | 181 | 1996K (6)| 00:01:18 | | |
| 1 | VIEW | VW_TE_2 |
4 | 1448 | 1996K (6)| 00:01:18 | | |
| 2 | UNION-ALL | |
| | | | | |
| 3 | VIEW | VW_ORE_1C1FD070 |
3 | 1086 | 1050 (0)| 00:00:01 | | |
| 4 | UNION-ALL | |
| | | | | |
| 5 | PARTITION RANGE ITERATOR | |
1 | 181 | 41 (0)| 00:00:01 | 89 | 101 |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MDD |
1 | 181 | 41 (0)| 00:00:01 | 89 | 101 |
| 7 | SORT CLUSTER BY ROWID BATCHED | |
1 | | 40 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | MDD_IX2 |
1 | | 40 (0)| 00:00:01 | 89 | 101 |
| 9 | PARTITION RANGE ITERATOR | |
1 | 181 | 95 (0)| 00:00:01 | 104 | 134 |
|* 10 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MDD |
1 | 181 | 95 (0)| 00:00:01 | 104 | 134 |
| 11 | SORT CLUSTER BY ROWID BATCHED | |
1 | | 94 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | MDD_IX2 |
1 | | 94 (0)| 00:00:01 | 104 | 134 |
| 13 | PARTITION RANGE ITERATOR | |
1 | 181 | 914 (0)| 00:00:01 | 139 | 442 |
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| MDD |
1 | 181 | 914 (0)| 00:00:01 | 139 | 442 |
|* 15 | INDEX RANGE SCAN | MDD_IX2 |
1 | | 913 (0)| 00:00:01 | 139 | 442 |
| 16 | PARTITION RANGE OR | |
1 | 181 | 1995K (6)| 00:01:18 |KEY(OR)|KEY(OR)|
|* 17 | TABLE ACCESS STORAGE FULL | MDD |
1 | 181 | 1995K (6)| 00:01:18 |KEY(OR)|KEY(OR)|
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("trn"=':b1')
10 - filter(LNNVL("MDD"."part_dt">=TO_DATE(' 2021-02-19 00:00:00',
'syyyy-mm-dd hh24:mi:ss')) OR
LNNVL("MDD"."part_dt"<TO_DATE(' 2021-03-04 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
12 - access("trn"=':b1')
14 - filter((LNNVL("MDD"."part_dt">=TO_DATE(' 2021-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
LNNVL("MDD"."part_dt"<TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))) AND
(LNNVL("MDD"."part_dt">=TO_DATE(' 2021-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) OR
LNNVL("MDD"."part_dt"<TO_DATE(' 2021-04-06 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))))
15 - access("trn"=':b1')
17 - storage("trn"=':b1' AND ("MDD"."part_dt"<TO_DATE(' 2021-02-19
00:00:00', 'syyyy-mm-dd hh24:mi:ss')
OR "MDD"."part_dt"<TO_DATE(' 2021-04-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-04-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE('
2021-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "MDD"."part_dt">=TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
filter("trn"=':b1' AND ("MDD"."part_dt"<TO_DATE(' 2021-02-19
00:00:00', 'syyyy-mm-dd hh24:mi:ss')
OR "MDD"."part_dt"<TO_DATE(' 2021-04-10 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-04-06 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') OR "MDD"."part_dt"<TO_DATE('
2021-03-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"MDD"."part_dt">=TO_DATE(' 2021-03-04 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')))
SQL> select/*+full(MDD)*/ * from MDD where trn=':b1'; Elapsed: 00:00:00.04
Execution Plan
Plan hash value: 750636482
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 181 | 16M
(4)| 00:11:00 | | |
| 1 | PARTITION RANGE ALL | | 1 | 181 | 16M
(4)| 00:11:00 | 1 | 442 | |* 2 | TABLE ACCESS STORAGE FULL| MDD | 1 | 181 | 16M (4)| 00:11:00 | 1 | 442 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - storage("trn"=':b1')
filter("trn"=':b1')
On Sat, Dec 25, 2021 at 6:51 PM Lok P <loknath.73_at_gmail.com> wrote:
> 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-lReceived on Sat Dec 25 2021 - 14:48:57 CET