Skip to Main Content
  • Questions
  • SQL SELECT query slowness after database upgrade from 11.2.0.4 to 12c

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ramesh Kumar.

Asked: September 28, 2016 - 1:28 pm UTC

Last updated: November 27, 2018 - 4:58 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

What would be the cause sudden SELECT query slowness in a Production Environment after database gets upgraded from Oracle 11.2.0.4 to Oracle 12c? Earlier these 2 SQL queries were ran less than 1 sec. Not its taking 30 to 40 sec's to execute that SELECT query. Could you please provide us the better solutions?

and Connor said...

Most likely cause is a different execution plan in the new version.

If you still have the old 11 version, you can use sql plan management to lock down the plan across versions.

Or if you have access to change the sql, you can temporarily use a hint to revert back to the old optimizer code until you come up with a more permanent solution, ie

select /*+ opt_param('optimizer_features_enable' '11.2.0.4') */ ...


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?

Connor McDonald
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
Chris Saxon
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.


Connor McDonald
November 27, 2018 - 4:58 am UTC

nice input

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.