Re: Adaptive plans bypassed - but why ?

Hi Jonathan,

I managed to get access to the view definition, so I could crosscheck your points:

I did not find any ANSI join in the text, but all the others are right!
b) it IS a "with CTE as subquery"
c) [ in a 2nd subquery CTE2 ] there is an aggregate (count( SOMECOL) as COUNTSOMECOL ) on CTE, and in main query, there is a comparison as CTE.SOMECOL = CTE2.COUNTSOMECOL
d) the MView is directly joined. No MView rewrite magic is done.

Thank you for all the insights. I learned a lot about subtile details in SQL Plans and 10053 traces.

_at_Mladen : you are right, this view is obviously hand-written. It also contains some comments (why this filter, why the join that way) to even gie someone without knowledge about the business logic (like me) at least a chance to understand the authors intention.

If anyone wants some more details (regarding the query, objects, trace-details) I'm willing to share ;-)

best regards,
 Martin

Am Di., 19. Mai 2020 um 00:43 Uhr schrieb Jonathan Lewis < jlewisoracle_at_gmail.com>:

>
> Martin,
>
> Notionally every single join could be adaptive, so I let myself be misled
> by the comment about materialized query block.
>
> I can't work out quite what your query looksl like, but I think
>
> a) It's using "ANSI" syntax
> b) the view is defined with a "with CTE as subquery" starting section
> c) the view includes a construct like "select columns from CTE where colx
> = (select max(colx) from CTE)" (which means the materialization would have
> been automatic and didn't need hinting)
> d) the query involves explicit reference to the materialized view name
> (since the operations are: MAT_VIEW ACCESS rather than MAT_VIEW REWRITE
> ACCESS).
>
> You may well be right that 12.2 introduces some extra (possibly not yet
> documented) restriction on when AP will apply. Alternatively it's just a
> case were 12.2 is using a different algorithm for some transformation which
> means it doesn't consider its estimated cardinality to be a guess (or
> otherwise subject to unknowable variation), so it doesn't use AP.
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
>
>
> On Mon, May 18, 2020 at 10:01 PM Martin Berger <martin.a.berger_at_gmail.com>
> wrote:
>
>> Hi Jonathan,
>>
>> this query seems to have something of all, but I can't bring it into
>> right order:
>> The Plan (obfuscated):
>>
>> -----------------------------------------------------------------------------------+-----------------------------------+
>>
>> | Id | Operation | Name
>> | Rows | Bytes | Cost | Time |
>> -----------------------------------------------------------------------------------+-----------------------------------+
>>
>> | 0 | SELECT STATEMENT |
>> | | | 1223 | |
>> | 1 | VIEW | Vxxxxxxx
>> | 1 | 4267 | 1223 | 00:00:15 |
>> | 2 | *TEMP TABLE TRANSFORMATION * |
>> | | | | |
>> | 3 | LOAD AS SELECT |
>> *SYS_TEMP_2FD9E18DE_43EF313F*| | | | |
>> | 4 | HASH JOIN OUTER |
>> | 247 | 38K | 1215 | 00:00:15 |
>> | 5 | JOIN FILTER CREATE | :BF0000
>> | 241 | 29K | 682 | 00:00:09 |
>> | 6 | HASH JOIN |
>> | 241 | 29K | 682 | 00:00:09 |
>> | 7 | MAT_VIEW ACCESS BY INDEX ROWID BATCHED | V2XXXXXXXXXXXX
>> | 210 | 8610 | 18 | 00:00:01 |
>> | 8 | INDEX RANGE SCAN |
>> IDX_VXXXXXXXXXXXXXX | 214 | | 2 | 00:00:01 |
>> | 9 | HASH JOIN |
>> | 242 | 20K | 664 | 00:00:08 |
>> | 10 | JOIN FILTER CREATE | :BF0001
>> | 141 | 7332 | 490 | 00:00:06 |
>> | 11 | NESTED LOOPS |
>> | 141 | 7332 | 490 | 00:00:06 |
>> | 12 | NESTED LOOPS |
>> | 141 | 7332 | 490 | 00:00:06 |
>> | 13 | HASH JOIN |
>> | 141 | 6204 | 349 | 00:00:05 |
>> | 14 | JOIN FILTER CREATE | :BF0002
>> | 141 | 3948 | 176 | 00:00:03 |
>> | 15 | TABLE ACCESS STORAGE FULL | T1XXXXXXX
>> | 141 | 3948 | 176 | 00:00:03 |
>> | 16 | JOIN FILTER USE | :BF0002
>> | 28K | 441K | 173 | 00:00:03 |
>> | 17 | TABLE ACCESS STORAGE FULL | T1XXXXXXX
>> | 28K | 441K | 173 | 00:00:03 |
>> | 18 | INDEX UNIQUE SCAN | PK_XXXX
>> | 1 | | 0 | |
>> | 19 | TABLE ACCESS BY INDEX ROWID | T2XXX
>> | 1 | 8 | 1 | 00:00:01 |
>> | 20 | JOIN FILTER USE | :BF0001
>> | 37K | 1159K | 173 | 00:00:03 |
>> | 21 | TABLE ACCESS STORAGE FULL | T3XXX
>> | 37K | 1159K | 173 | 00:00:03 |
>> | *22* | VIEW |
>> | 8117 | 277K | 533 | 00:00:07 |
>> | *23* | HASH GROUP BY |
>> | 8117 | 428K | 533 | 00:00:07 |
>> | 24 | HASH JOIN |
>> | 8117 | 428K | 532 | 00:00:07 |
>> | 25 | HASH JOIN |
>> | 8117 | 349K | 362 | 00:00:05 |
>> | 26 | JOIN FILTER CREATE | :BF0003
>> | 28 | 336 | 4 | 00:00:01 |
>> | *27* | *MAT_VIEW ACCESS STORAGE FULL* | V3XXXXXX
>> | 28 | 336 | 4 | 00:00:01 |
>> | 28 | JOIN FILTER USE | :BF0003
>> | 54K | 1742K | 358 | 00:00:05 |
>> | *29 * | TABLE ACCESS STORAGE FULL |
>> T4XXXXXXXXXXXXXX | 54K | 1742K | 358 | 00:00:05 |
>> | 30 | VIEW | index$_join$_009
>> | 37K | 374K | 170 | 00:00:03 |
>> | 31 | HASH JOIN |
>> | | | | |
>> | 32 | INDEX STORAGE FAST FULL SCAN | IDX_LXXXXX
>> | 37K | 374K | 104 | 00:00:02 |
>> | 33 | JOIN FILTER USE | :BF0000
>> | 37K | 374K | 108 | 00:00:02 |
>> | 34 | INDEX STORAGE FAST FULL SCAN | PK_LXXX
>> | 37K | 374K | 108 | 00:00:02 |
>> | 35 | NESTED LOOPS |
>> | 1 | 226 | 8 | 00:00:01 |
>> | 36 | NESTED LOOPS |
>> | 1 | 226 | 8 | 00:00:01 |
>> | 37 | HASH JOIN |
>> | 1 | 217 | 7 | 00:00:01 |
>> | 38 | VIEW |
>> | 1 | 39 | 4 | 00:00:01 |
>> | 39 | HASH GROUP BY |
>> | 1 | 48 | 4 | 00:00:01 |
>> | 40 | VIEW |
>> | 247 | 12K | 3 | 00:00:01 |
>> | 41 | TABLE ACCESS STORAGE FULL |
>> SYS_TEMP_2FD9E18DE_43EF313F| 247 | 23K | 3 | 00:00:01 |
>> | 42 | VIEW |
>> | 247 | 43K | 3 | 00:00:01 |
>> | 43 | TABLE ACCESS STORAGE FULL |
>> SYS_TEMP_2FD9E18DE_43EF313F| 247 | 23K | 3 | 00:00:01 |
>> | 44 | INDEX UNIQUE SCAN | PK_CXXXXXXX
>> | 1 | | 0 | |
>> | 45 | TABLE ACCESS BY INDEX ROWID | T1XXXXXXX
>> | 1 | 9 | 1 | 00:00:01 |
>> -----------------------------------------------------------------------------------+-----------------------------------+
>>
>>
>> Query Block Name / Object Alias(identified by operation id):
>> ------------------------------------------------------------
>> 1 - SEL$6 / PRICING_at_SEL$1
>> 2 - SEL$6
>> 3 - *SEL$86DC3C1D *
>> 7 - SEL$86DC3C1D / V2XXXXXXXXXXXX_at_SEL$4
>> 8 - SEL$86DC3C1D / V2XXXXXXXXXXXX_at_SEL$4
>> 15 - SEL$86DC3C1D / T1XXXXXXX_at_SEL$2
>> 17 - SEL$86DC3C1D / T5XXXXXXXXXXXX_at_SEL$2
>> 18 - SEL$86DC3C1D / T2XXX_at_SEL$2
>> 19 - SEL$86DC3C1D / T2XXX_at_SEL$2
>> 21 - SEL$86DC3C1D / T3XXX_at_SEL$2
>> 22 - *SEL$3* / L1XXXXX_at_SEL$2
>> 23 - *SEL$3*
>> 27 - *SEL$3* / V3XXXXXX_at_SEL$3
>> 29 - *SEL$3* / T4XXXXXXXXXXXXXX_at_SEL$3
>> 30 - SEL$041DE2A2 / T3XXX_at_SEL$3
>> 31 - SEL$041DE2A2
>> 32 - SEL$041DE2A2 / indexjoin$_alias$_001_at_SEL$041DE2A2
>> 34 - SEL$041DE2A2 / indexjoin$_alias$_002_at_SEL$041DE2A2
>> 38 - SEL$5 / S1XXXXXXXXXXXXXXXXXXX_at_SEL$6
>> 39 - SEL$5
>> 40 - SEL$DF9BAC90 / L2XXXXXX_at_SEL$5
>> 41 - SEL$DF9BAC90 / T1_at_SEL$DF9BAC90
>> 42 - SEL$DF9BAC8F / L2XXXXXX_at_SEL$6
>> 43 - SEL$DF9BAC8F / T1_at_SEL$DF9BAC8F
>> 44 - SEL$6 / L3XXXXXXXXXXXXX_at_SEL$6
>> 45 - SEL$6 / L3XXXXXXXXXXXXX_at_SEL$6
>> ------------------------------------------------------------
>>
>>
>> There is a *TEMP TABLE TRANSFORMATION* , but not in *SEL$3* .
>> In *SEL$3*, there is a *MAT_VIEW ACCESS*.
>>
>> And the adaptive plan in 12.1 was in lines 35..45 (some HASH JOIN) - but
>> the NL was considered better.
>>
>> I can not confirm there is a WITH SUBQUERY. The original query is a
>> simple "SELECT ... from VIEW", the view definition is hidden atm (I try
>> to gain access), and the Final query after transformations is capped
>> after 4000 char :-(
>>
>> For me right now it seems, 12.2 is more careful regarding Adaptive Plans.
>> 12.1 accepted AP when a SYS_TEMP_% temp table was involved (lines
>> 35-45).
>>
>> In SEL$6, the optimizer also have some reasons to bypass AP:
>> they are
>> due to View on right side of join
>> due to Index join
>> due to Join method is MJC
>>
>>
>> I assume, it can be somehow seen how "due to materialized query block"
>> refers to the creation of SYS_TEMP_% temp table - it would just not make
>> any sense to have AP at creation of a temp table.
>>
>> Still I struggle to see the background of due to adaptive plans disabled
>> .
>>
>> thank you for helping me sorting all my semi-knowledge,
>> Martin
>>
>> Am Mo., 18. Mai 2020 um 21:24 Uhr schrieb Jonathan Lewis <
>> jlewisoracle_at_gmail.com>:
>>
>>>
>>> The text says: "materialized query block", not "materialized view".
>>> Is sel$3 a "with subquery" that the optimizer has decided to turn into
>>> "temp table" ? Does the plan show Temp Table Transformation" ?
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Mon, May 18, 2020 at 6:51 PM Martin Berger <martin.a.berger_at_gmail.com>
>>> wrote:
>>>
>>>> Dear List,
>>>>
>>>> I have a SQL query - in 12.1 it was using Adaptive Plan.
>>>> In 12.2 it isn't anymore. I don't see this a big problem, but would
>>>> like to be able to explain why.
>>>>
>>>> Unfortunately I can not share the full 10053 trace, but I will be able
>>>> to copy&paste all relevant parts - just tell me what you are looking for.
>>>>
>>>> some probably interesting parameters:
>>>> AP - adaptive plans
>>>> optimizer_adaptive_reporting_only = false
>>>> optimizer_adaptive_plans = true
>>>> optimizer_adaptive_statistics = false
>>>> optimizer_features_enable = 12.2.0.1
>>>>
>>>> I'm curious regarding these lines:
>>>>
>>>> AP: Checking validity for query block SEL$3, sqlid=0zc1a01zycagh
>>>> AP: Adaptive plans bypassed for query block SEL$3 due to materialized
>>>> query block
>>>> AP: Adaptive joins bypassed for query block SEL$3 due to adaptive plans
>>>> disabled
>>>>
>>>> ? why is a MV block adaptive plans?
>>>>
>>>> ? where can I find why adaptive plans are disabled?
>>>>
>>>> Again, this is more for me to understand, right now there is no issue
>>>> ;-)
>>>>
>>>> thanks for all hints,
>>>> berx
>>>>
>>>> --
>>>> Martin Berger Oracle ♠
>>>> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
>>>> ^∆x http://berxblog.blogspot.com
>>>>
>>>
>>
>> --
>> Martin Berger Oracle ♠
>> martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
>> ^∆x http://berxblog.blogspot.com
>>
>

-- 
Martin Berger                Oracle ♠
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com


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