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 18.104.22.168 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 22.214.171.124 there are three other helpful notes for earlier releases available as well:
Things to Consider Before Upgrading to 126.96.36.199 to Avoid Poor Performance or Wrong Results
Things to Consider Before Upgrading to 188.8.131.52 to Avoid Poor Performance or Wrong Results
Things to Consider Before Upgrading to 184.108.40.206 to Avoid Poor Performance or Wrong Results
Thank you for sharing the info. During the upgrade test I performed today, from 220.127.116.11 to 18.104.22.168 , 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:
Then I will investigate this for you.
Email is: firstname.lastname@example.org
Thanks in advance!
Thank you for the Tip.
I also have the same problem as Anand.
Upgrade SI from 22.214.171.124 => 126.96.36.199.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.
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
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.
You can take a look at
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.
Senthil Kumaran. S.
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
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:
Test to switch off the adaptive plans feature and maybe the cardinality feedback.