(not sure if this is the right forum or category but was not able to find one)
We started to see few strange behavior after 19c upgrade:
1-sql's keep flipping plans.
2-sql's are stubborn on picking the right plan with sql porfiles and sql patches
--19c upgraded from 12c
--Adaptive statistics are false (default)
--Adaptive plans are true (default)
--Dynamic sampling is 0
With that we expect no sql plan directives being cratered, yet we have many and they are in state= usable and also
dba_stat_extensions is populated!
1-Is that expected?
2-If not why it happened?
3-If spd is off and adaptive stats are false would these spd used Without going thru each sql via dbms_xplan cursor can we tell which sql’s have been using spd from DBA_hist_xxxx views?