SQL Performance (MOSC)

MOSC Banner

Odd spd behavior on 19c

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?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center