Skip to Main Content
  • Questions

  • Performance issue after database upgrade from 11.2.0.4 to 12.1.0.2

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Thoufeeq.

Asked: July 26, 2018 - 9:16 am UTC

Last updated: August 18, 2018 - 12:36 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi Team,

We have recently upgraded a SAP database from 11.2.0.4 to 12.1.0.2, After encountered performance issues that did not exist on the previous version(11.2.0.4).

Proper keeping sga_target/sga_maxsize did not help
Proper keeping db_file_multiblock_read_count did not help
Proper collection of statistics did not help(taken system/fixed objects and applciation schema stats with sample auto size)
Keeping optimizer_features_enable to older version did not help
Keeping below optimizer parameter did not help

optimizer_adaptive_features FALSE
optimizer_adaptive_plans FALSE
optimizer_adaptive_reporting_only FALSE
optimizer_adaptive_statistics FALSE
optimizer_capture_sql_plan_baselines FALSE


After database upgrade we have seen database wait time is increased for all the sql statements, and there is a differences in sql execution, most of tables are going FULL Table scan.

An average before upgrade FULL table scan count is 35 tables, and after upgrade the count increased to 2000 - 20000 also increased physical reads

I am taking a one example. I am having a table (ESLL) ,after upgrade ESLL table record processing is almost same but it increased physical reads, I/O load and CPU load.

After Upgrade (12.1.0.2)

Load Statistics Total for ESLL table


Analysis Date Total Executions Total Physical Reads Elapsed Time (ms) Total Buffer Gets Records Processed
23.07.2018 255.65 64.096.456.685 747.280.416 4.480.613.775 2.593.564.848

Before Upgrade(11.2.0.4)

Load Statistics Total for ESLL table



Analysis Date Total Executions Total Physical Reads Elapsed Time (ms) Total Buffer Gets Records Processed
28.05.2018 471.704 17.862 342.969.570 3.229.153.429 2.428.629.378


Can you please help me how can i deal with this issue.

Thanks in advance.

Regards,
Thoufeeq

and Connor said...

I hate to be critical in your time of need, but really - these sorts of issues should have been discovered (and resolved) in testing, not in your production upgrade. Then again, maybe this is still just your test system - I hope so.

Even with all of those parameters set, this will get you *close* to your pre-existing state, but the moment you have collected some statistics, then you have changed the inputs to the optimizer, so the potential for changed execution plans is there.

You mentioned "Proper collection of statistics" - for consistency across upgrades, you probably did *not* want to do this. You should have kept the previous statistics to ensure a like-for-like comparison. In a perfect world, you would have used sql plan baselines to lock in the 11g plans and guarantee you would get the same plans in 12c.

Sorry, but I don't have a lot of good news for you here. I think you'll need to pick a candidate SQL statements and run a 10053 trace on them to see why they have started to lean toward full scans, and without the same numbers from your 11g database - comparisons will be tough to make. But hopefully you can find some patterns which will let you make some global adjustments which will let your performance get back on track.

Rating

  (2 ratings)

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

Comments

Two Thoughts

Patrick Jolliffe, August 18, 2018 - 6:42 am UTC

Reading this response, I had two thoughts.

Firstly, I think 10053 trace might be a bit drastic at this stage, sure it contains lots of interesting information, but the volume of information can be a bit overwhelming especially if one is not reasonably knowledgeable about the Cost-Based Optimizer. I'd start by picking the most simple problem query and (assuming appropriately licensed) using Real-Time SQL Monitor, comparing estimated with actual cardinalities to try to find out where things are going wrong. If you don't have license for that, hinting with GATHER_PLAN_STATISTICS and using dbms_xplan.display_cursor with ALLSTATS LAST can be used similarly (google it). You may have to drill into 10053 trace eventually, but keep it simple first.
Secondly, I guess you are where you are, but in 2018 I can't understand why anyone would be going from 11.2 to *only* 12.1. 12.1 is already out of Premier Support (Extended Support fee waived until July 2019). If you're going to have to deal with the pain of the upgrade at least go to 12.2 (if you think 18c is a bit too new for you). Not only do you get huge amount of issues with 12.1 resolved properly (adaptive statistics spring to mind), you are then within Premier Support until 2022.
Connor McDonald
August 18, 2018 - 12:36 pm UTC

Nice input.

I'm leaning toward 10053 as a first step, not to tune the SQL but to see if a pattern can be found, which can then perhaps lead to a global solution, eg, "Ah... the system stats are making multiblock reads too cheap"

Because if a cause cannot be found, then we're into bad territory...because this is a SAP system - you can't go tinkering with the SQL's.

What About upgrade itself

Gh, August 18, 2018 - 6:55 am UTC

You didn't talk about upgrade itself. How it was completed and what about os characteristics?
For instance did you gathered system stats?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database