Re: Adaptive plans bypassed - but why ?
Date: Mon, 18 May 2020 23:01:29 +0200
Message-ID: <CALH8A93gbERAuT5vCcZVvqD87XO51_LLJHVdbLzChL2DvAW9iA_at_mail.gmail.com>
Hi Jonathan,
this query seems to have something of all, but I can't bring it into right
order:
The Plan (obfuscated):
-----------------------------------------------------------------------------------+-----------------------------------+
| 10 | JOIN FILTER CREATE | :BF0001
| 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 |
| 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-lReceived on Mon May 18 2020 - 23:01:29 CEST