Sleeping Beauties – Upgrade to 11.2.0.4 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 11.2.0.3 to Oracle 11.2.0.4 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 11.2.0.4.0 02:46:19

JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 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 #’;
199
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):

bug19855835:
Upgrade from 11.2.0.2 to 11.2.0.4 is slow 

-Mike

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