Re: Spd 19c adaptive statistics

Hello Ahmed,

> But will they be ever used with adaptive stats false?

Yes, in some cases. For example as soon as the new dynamic sampling (DS) code is used (optimizer_dynamic_sampling=11). This can happen in case of PX, e.g. as mentioned on slide 9 here: http://www.soocs.de/public/talk/171121_DOAG2017_Under_The_Hood_Dynamic_Adaptive_Features_PPT.pdf

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Moustafa Ahmed <moustafa_dba_at_hotmail.com> hat am 06.01.2021 00:32 geschrieben:
>
>
> Hello
>
> we have been seeing many issues since our upgrade to 19c from 12c
> Exadata DW system with massive and complex sql’s
> Such a as:
> 1-plans keeps flipping.
> 2-many cases it is impossible to freeze older plans using profiles or even sql baselines (as recommended)
> 3-insanely off cardinality estimates mainly showing (1) as cardinality estimate !!! (For sure sticking to 1 means something)
>
> Now it made me take a deep dive and found I have these question for you
>
> We have
> 1-adaptive plans true (default)
> 2-adaptive statistics false (default)
> 3-Dynamic_sampling 0 (non default)
>
> Now checking dba_sql_plan_directives we see many created spd in state usable?
>
>
> As I see from blog post
> https://blogs.oracle.com/optimizer/optimizer-adaptive-features-in-oracle-database-12c-release-2
>
>
> They are being generated automatically regardless of adaptive statistics false
> But will they be ever used with adaptive stats false?
>
> I know I can tell from dbms_xplan cutsor note section but is there a bulk way to check history on dba_hist_xxx views to expose the note section and see if it was ever used?
>

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