- 3,715,507 Users
- 2,242,778 Discussions
- 7,845,371 Comments
Forum Stats
Discussions
Categories
- 15 Data
- 362.2K Big Data Appliance
- 6 Data Science
- 1.5K Databases
- 461 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 3 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 416 SQLcl
- 42 SQL Developer Data Modeler
- 184.8K SQL & PL/SQL
- 21K SQL Developer
- 1.8K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 8 DevOps
- 3K QA/Testing
- 246 Java
- 5 Java Learning Subscription
- 10 Database Connectivity
- 66 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 124 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 10 Español
- 1.9K Japanese
- 2 Portuguese
sql plan directives, dynamic sampling, 19c, adaptive plans and statistics

(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?
Answers
-
The documentation for the
OPTIMIZER_ADAPTIVE_STATISTICS
parameter (remark: modifiable in a PDB) states:OPTIMIZER_ADAPTIVE_STATISTICS
does not control the creation of SQL plan directives. SQL plan directives will be created even if this parameter is false, but they will not be used to refine SQL execution plans with dynamic sampling.Therefore, if the
AUTO_STAT_EXTENSIONS
statistics preference is set to ON, it would be normal that statistics extensions be created.Related blog articles by Frank Pachot and Christian Antognini:
- https://blog.dbi-services.com/matching-sql-plan-directives-and-queries-using-it/
- https://blog.dbi-services.com/dba_sql_plan_directive-last_used/
- https://antognini.ch/2017/11/spd-state-does-not-change-if-adaptive-statistics-are-disabled/
Remarks:
- Sadly, the
+metrics
format option (so far undocumented) seems to work only indbms_xplan.display
; it does nothing apparently in calls todbms_xplan.display_cursor
—I didn't try, but I wouldn't expect it to do anything in calls todbms_xplan.display_awr
either. - Christian Antognini's post includes a test script; I ran it on a 19.5 test DB, which behaved exactly as described.