sql plan directives, dynamic sampling, 19c, adaptive plans and statistics — oracle-tech

    Forum Stats

  • 3,708,983 Users
  • 2,241,169 Discussions
  • 7,840,727 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

User433031User433031 Posts: 1 Red Ribbon

(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

Sign In or Register to comment.