Re: Adaptive plans bypassed - but why ?

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Mon, 18 May 2020 23:41:22 +0100
Message-ID: <CAGtsp8==VJvJSBviy-kQwZ3-ULrUF9ZomgAyEVfXd4xnU2f58Q_at_mail.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

It's using "ANSI" syntax

the view is defined with a "with CTE as subquery" starting section

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)

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
>

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

Received on Tue May 19 2020 - 00:41:22 CEST

This message: [ Message body ]
Next message: William Beldman: "RE: How to run commands as Oracle linux user in Jenkins"
Previous message: Mladen Gogala: "Re: Adaptive plans bypassed - but why ?"
In reply to: Martin Berger: "Re: Adaptive plans bypassed - but why ?"
In reply to Martin Berger: "Re: Adaptive plans bypassed - but why ?"
Next in thread: Martin Berger: "Re: Adaptive plans bypassed - but why ?"
Reply: Martin Berger: "Re: Adaptive plans bypassed - but why ?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message