By Mike Dietrich-Oracle on Nov 07, 2014
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 22.214.171.124 to Oracle 126.96.36.199 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
. VALID 188.8.131.52.0 02:46:19
JServer JAVA Virtual Machine
. VALID 184.108.40.206.0 00:08:34
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 #';
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 220.127.116.11 to 18.104.22.168 is slow
PS: Credits go to Tan for bringing this to my attention - and sorry for the inconvenience!