Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql plan directives, dynamic sampling, 19c, adaptive plans and statistics

User433031Jan 5 2021 — edited Jan 6 2021

(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?

Comments

user9540031

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 in dbms_xplan.display; it does nothing apparently in calls to dbms_xplan.display_cursor—I didn't try, but I wouldn't expect it to do anything in calls to dbms_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.

1 - 1

Post Details

Added on Jan 5 2021
1 comment
769 views