Home -> Mailing Lists -> Oracle-L -> Spd 19c adaptive statistics

Spd 19c adaptive statistics

From: Moustafa Ahmed <moustafa_dba_at_hotmail.com>
Date: Tue, 5 Jan 2021 23:32:23 +0000
Message-ID: <BN6PR06MB2785DA0E8C03EAA163105195E1D10_at_BN6PR06MB2785.namprd06.prod.outlook.com> 





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


Received on Wed Jan 06 2021 - 00:32:23 CET












Original text of this message