Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

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 

Avoid Poor Performance and Wrong Results when upgrading to 12.1.0.2

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

8 thoughts on “Things to consider BEFORE upgrading to Oracle 12.1.0.2 to AVOID poor performance and wrong results

  1. 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.

  2. 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

  3. 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

  4. 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.

  5. 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
    _

Leave a Reply

Your email address will not be published. Required fields are marked *