Sleeping Beauties – Upgrade to can be slow

A customer from the US did contact me past week via LinkedIn and raised a question:

“Is it expected that my patch set upgrade from Oracle to Oracle takes over 3 hours?”

Of course, no – this is not expected.

This is the upgrade stats gathered post upgrade with utlu112s.sql:

SQL> @?/rdbms/admin/utlu112s.sql ; .
Oracle Database 11.2 Post-Upgrade Status Tool
10-31-2014 10:05:29
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server
. VALID 02:46:19

JServer JAVA Virtual Machine
. VALID 00:08:34
Final Actions
. 00:00:00
Total Upgrade Time: 03:06:47

No, this is not really expected. So we tried to nail down the root cause finding out these statements in the upgrade script c1102000.sql are causing the trouble:

194 — wri$_optstat_histhead_history2.
195 execute immediate
196 q’#create unique index i_wri$_optstat_hh_obj_icol_st on
197 wri$_optstat_histhead_history (obj#, intcol#, savtime, colname)
198 tablespace sysaux #’;
200 execute immediate
201 q’#create index i_wri$_optstat_hh_st on
202 wri$_optstat_histhead_history (savtime)
203 tablespace sysaux #’;
204 end;
205 /

It’s index rebuilds on histogram tables. And the customer has a large amount of stats data in his database as the default stats retention is 31 days.

Obviously the index rebuild is not done very efficiently (not done in parallel, no nologging clause). Those things can happen and sometimes this may not cause any issues. But in this case it lead to over 2 hours for just those index rebuilds.

Luckily my colleague Cindy is an excellent resource for such things – after asking our team I’ve got the reply that this is tagged with a bug number and code fix already got checked in (under review right now):

Upgrade from to is slow 


PS: Credits go to Tan for bringing this to my attention – and sorry for the inconvenience! 

Share this:

9 thoughts on “Sleeping Beauties – Upgrade to can be slow

    when you run the upgrade – not the default in 11.2 but is the default beginning with 12.1’s


  2. Thanks Mike, I wish I had checked the blog a few moments earlier as already started the upgrade. I wasn’t however sure if my question got updated as I couldn’t see it immediately and I tried to update the blog a couple of time.

  3. Mike, In following up on my previous question, I can’t find in the 11204 binaries, this maybe only a 12c thing or platform specific.


  4. is a pure 12c tool – we had just given it to a number of selected high profile customers for evalutation – but it is not available for 11g databases to anybody anymore.


  5. The post or article is for 11204 however the script catpl used for determining the perf issue doesn’t exist on 11204 and
    most customers will not have access to it, so it kind of defeats the purpose of the article/post. Hope the explanation is clearer.


  6. Sorry … my fault – now I got your point and you are correct.
    Please exchange "" with "catupgrd.sql". For the ECHO ON won’t help you as it is a SQL Plus setting but runs outside of sql*plus.

    So appologize 🙂
    I’ve meant catupgrd.sql


Leave a Reply

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

* Checkbox to comply with GDPR is required


I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.