Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!


Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ?

You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:

dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated);
END; -- end of update for system internally generated objs
/

The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade as recommended.

Offered Workarounds

Important things to know

  • Bug 21744290 – catuposb.sql can be slow during upgrade depending on no. of objects in the DB
    is supposed to be fixed in the April 2016 BPs and PSU – but in Chris’ case Oracle Support confirmed that even though Chris had the BP the issue still happens. And this observation is correct. The fix from December 2015 is missing in the catuposb.sql distributed with the April 2016 PSU and BP.When you compare it now with the version Oracle Support is distributing you’ll find the fix being included – but the two more recent fixes in the current catuposb.sql (see above) are missing.
    We’ll sort this out and I will update you via this blog post.
  • These are the lines missing in the version of catuposb.sql distributed with the April 2016 PSU/BP:
    -- bug 22178855: gather table stats on user$ after update to avoid slow
    -- select in update 4
    dbms_stats.gather_table_stats('SYS', 'USER$');
    

What should you do?

Actually it is not supported to edit “our” scripts in ?/rdbms/admin so please wait for a clean version of catuposb.sql being distributed. I will update the blog asap. In between I think it should be ok to gather stats on USER$ beforehand in order to prevent this from happening:

SQL> exec dbms_stats.gather_table_stats('SYS', 'USER$');

Or you’ll wait for the July 2016 Proactive BP and PSU which should be available on July 19, 2016. It will contain the correct version of the script including all fixes.

–Mike

6 thoughts on “Speed up Upgrade Phase 65 with a new catuposb.sql

  1. I gathered stats for SYS.USERS$ before the upgrade, still ran into this issue.
    I had to start over but replaced catuposb.sql according to 2147232.1 and it ran fine.
    The difference must be that during the upgrade the script gathers statistics with the 12c software.

    Cheers

  2. Rodolfo,

    this is expected as it gets changed during the upgrade and therefore the stats need to be gathered later on – that is the fix we implemented.

    Cheers and sorry for the inconvenience
    Mike

  3. Hi Mike,

    We have the latest catuposb.sql file but still we are seeing the issue. update #4 step took more than 6 hours to finish.

    Rem MODIFIED (MM/DD/YY)
    Rem apfwkr 06/24/16 – Backport apfwkr_blr_backport_22178855_12.1.0.2.0
    Rem from st_rdbms_12.1
    Rem apfwkr 01/14/16 – Backport cmlim_bug-21744290 from main
    Rem cmlim 10/08/15 – bug 21744290: rewrite sql optimally
    Rem apfwkr 05/11/16 – Backport cmlim_bug-22178855 from main
    Rem cmlim 12/03/15 – bug 22178855: gather table stats on user$ to
    Rem speed up select in update #4

  4. Hi there,

    thanks for the update and sorry to hear this. You are actually the 3rd case who reported such an issue. At the moment my colleagues are investigating this (it seems to have to do with stale histograms).

    Would you please mind to open an SR to have this tracked? Then will give you the chance to get an immediate update once the issue is diagnosed and solved.

    Thanks in advance!
    Mike

Leave a Reply

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