any missing metadata/statistics
Rajeshwaran, Jeyabal, September 29, 2016 - 10:47 am UTC
SELECT query slowness in a Production Environment after database gets upgraded from Oracle 11.2.0.4 to Oracle 12c?So what was the outcome when this was tested during migration in pre-prod environments?
when plan changes between 11g to 12c, are we missing
a) any piece of metadata/statistics ?
b) any histograms ?
c) any sql profiles ?
d) any table/schema/database level preferences?
September 29, 2016 - 11:04 am UTC
"So what was the outcome when this was tested during migration in pre-prod environments?"
:-)
are we missing | few more options
Rajeshwaran, Jeyabal, September 29, 2016 - 1:33 pm UTC
sorry missed out to mention about Extended stats to the above list.
e) Extended stats or Expression stats ?
Before and After
Stephen, September 29, 2016 - 5:20 pm UTC
Posts like these make me a little sad. I have seen these issues pop up every major version upgrade since Oracle 7. And every time I wonder where are you pre execution plans to compare with post execution plans. Back in the day, these things were little harder to get, but Oracle has come a long way in providing this information to help with situations like this. If this is affecting many select statements, you can also put optimizer_features_enable=11.2.0.4 in the spfile or pfile whichever your system uses.
Stephen
September 30, 2016 - 8:51 am UTC
The modern database upgrade seems to be defined as:
"Testing ? Oh....yeah, we skipped that"
on upgrades to 12.2
Rajeshwaran Jeyabal, November 22, 2018 - 1:22 pm UTC
Very recently we had a simillar situation with 10g to 12c upgrade.
all sql's/workloads were tested and working fine from the day#1.
one fine sql from the user's local machine (got missed in testing) got slow post the upgrade. it was due implict conversion and hence cardinality underestimated in 12c.
what we did was
* set up a schema level trigger with OFE to 10.2 for that user.
* then sql ran fine.
* then finally we load that good plan for that sql from AWR ( dbms_spm.load_plans_from_awr - new in 12.2) into SMB and then dropped that tigger.
November 27, 2018 - 4:58 am UTC
nice input