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

• Gather table stats (DBMS_STATS.GATHER_TABLE_STATS) on SYS.USER$ first
• Exchange catuposb.sql with the version distributed via  MOS Note:2147232.1 – Upgrade to 12c : CATUPOSB.SQL SLOW DURING UPGRADE

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