Re: Adaptive plans bypassed - but why ?

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
>

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