Finally it got published on MyOracle Support (MOS) portal – one of the most helpful notes when we prep for an upgrade or a migration – and potentially the one with the longest title in MOS:
MOS Note: 2034610.1
Things to consider BEFORE upgrading to Oracle Database 12.1.0.2 to AVOID poor performance and wrong results
One important note regarding these “Things to Consider” notes:
They will only list issues where either a fix or a proven workaround is available. They don’t list all open issues nor do they recommend every given underscore which may cure one or the other misbehavior.
Just in case you are NOT upgrading/migrating to Oracle Database 12.1.0.2 there are three other helpful notes for earlier releases available as well:
Document 1320966.1
Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
Document 1392633.1
Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
Document 1645862.1
Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results
–Mike
Thank you for sharing the info. During the upgrade test I performed today, from 11.2.0.3 to 12.1.0.2 , major time was spent at
"alter table histgrm$ add ep_repeat_count number default 0 not null;" –> Approx 40mins. The table has 33391756 rows.
Any recommendations to reduce the timing.
Thank you for sharing the info.
Thanks – this is very interesting – would you please mind to send me:
catupgrd0-3.logs,
preupgrade.log
upg_summary.log
Then I will investigate this for you.
Email is: firstname.lastname@oracle.com
Thanks in advance!
Mike
Hi,
Thank you for the Tip.
I also have the same problem as Anand.
Upgrade SI from 11.2.0.3 => 12.1.0.2.4
The upgrade takes long time on:
15:02:22 SQL> Rem Add columns to accommodate endpoint repeat count information
15:02:22 SQL> alter table histgrm$ add ep_repeat_count number default 0 not null;
Table histgrm$ has 40184419 rows
And also on other AWR Tables as shown below:
13:06:13 SQL> create index i_wri$_optstat_h_obj#_icol#_st on
13:06:13 2 wri$_optstat_histgrm_history(obj#, intcol#, savtime, colname)
13:06:13 3 tablespace sysaux
13:06:13 4 /
I guess the second problem should be fixed by patch 19855835.
Regards,
Pascal
Pascal,
sorry for my late reply – vacation 😉
Yes, the 2nd thing for sure will be fixed – and if that doesn’t clean the histogram table please do the following:
1.) Run the below script to check the current space usage of Optimizer Statistics Histogram & AWR tables, its relevant
indexes in SYSAUX tablespace.
SQL> conn / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql
Output is written to awrinfo.lst in the current working directory
2.) Check the statistics availablility from Optimizer Statistics Histogram table by number of days.
SQL> select systimestamp – min(savtime) from sys.wri$_optstat_histgrm_history;
3.) Purge the Statistics by altering the number of days from the above query.
SQL> exec dbms_stats.purge_stats(sysdate – <no of days>);
That should clean out the histograms as well.
Mike
Hi Pascal,
You can take a look at
https://aprakash.wordpress.com/2015/08/23/12c-upgrade-taking-time/
Hi,
We are doing POC on Oracle 12c. Currently we have Oracle 11g. During month end we use to run a batch job which gets executed by 4Hrs and 30Mins. The same hardware, the same data oracle 12c is taking more than 8 Hours. Can you help me on this.
regards,
Senthil Kumaran. S.
Senthil,
that is very hard to answer out of the box.
First I need to know how the plans differ. Or IF they differ at all. It sounds a bit odd and marketing like but I really mean it: if you’d had Real Application Testing’s Capture/Replay and SPA in place it would be very easy to compare the differences.
If you don’t have them in place I’d usually do the following approach:
(1) Capture the current plans in 11.2 from
– AWR and
– Cursor Cache
into a SQL Tuning set. Make sure this STS is present
on the target machine.
(2) Run your tests and compare plans
– if plans are different and worse take them from the STS
and nail them into the plan baseline.
– if plans are the same and worse try to analyze them with
the SQL Tuning Advisor. Please note: AWR requires DIAG
Pack, SQL Tuning Advisor requires TUNING Pack licenses.
(3) After you’ve gone through this exercise I’d usually REPLAY
to see the overall performance to verify if my tasks were
successful
If you have neither DIAG or TUNING Pack it will be a bit tough. You can collect Stored Outlines on 11.2 for your statements getting worse in 12.1 – after upgrade/migration move them with DBMS_SPM into the Plan Baseline (free EE feature). Drop the Outlines afterwards.
Furthermore apply the recommended patches from the note in this blog post – and you may alter a few optimizer settings and verify their influence.
And PLEASE THIS IS NOT A GENERAL RECOMMENDATION – this is just a hint to EVALUATE THE INFLUENCE:
optimizer_features_enable=12.1.0.1
Test to switch off the adaptive plans feature and maybe the cardinality feedback.
Mike
_