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?
