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.
@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@xxxxxxxxx>:
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@xxxxxxxxx>
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@SEL$1
2 - SEL$6
3 - *SEL$86DC3C1D *
7 - SEL$86DC3C1D / V2XXXXXXXXXXXX@SEL$4
8 - SEL$86DC3C1D / V2XXXXXXXXXXXX@SEL$4
15 - SEL$86DC3C1D / T1XXXXXXX@SEL$2
17 - SEL$86DC3C1D / T5XXXXXXXXXXXX@SEL$2
18 - SEL$86DC3C1D / T2XXX@SEL$2
19 - SEL$86DC3C1D / T2XXX@SEL$2
21 - SEL$86DC3C1D / T3XXX@SEL$2
22 - *SEL$3* / L1XXXXX@SEL$2
23 - *SEL$3*
27 - *SEL$3* / V3XXXXXX@SEL$3
29 - *SEL$3* / T4XXXXXXXXXXXXXX@SEL$3
30 - SEL$041DE2A2 / T3XXX@SEL$3
31 - SEL$041DE2A2
32 - SEL$041DE2A2 / indexjoin$_alias$_001@SEL$041DE2A2
34 - SEL$041DE2A2 / indexjoin$_alias$_002@SEL$041DE2A2
38 - SEL$5 / S1XXXXXXXXXXXXXXXXXXX@SEL$6
39 - SEL$5
40 - SEL$DF9BAC90 / L2XXXXXX@SEL$5
41 - SEL$DF9BAC90 / T1@SEL$DF9BAC90
42 - SEL$DF9BAC8F / L2XXXXXX@SEL$6
43 - SEL$DF9BAC8F / T1@SEL$DF9BAC8F
44 - SEL$6 / L3XXXXXXXXXXXXX@SEL$6
45 - SEL$6 / L3XXXXXXXXXXXXX@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@xxxxxxxxx>:
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@xxxxxxxxx>
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@xxxxxxxxx @martinberx <https://twitter.com/martinberx>
^∆x http://berxblog.blogspot.com
--
Martin Berger Oracle ♠
martin.a.berger@xxxxxxxxx @martinberx <https://twitter.com/martinberx>
^∆x http://berxblog.blogspot.com
