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!
How were you able to detect the slow sql?
SET TIMING ON
SET ECHO ON
when you run the upgrade – not the default in 11.2 but is the default beginning with 12.1’s catctl.pl
Cheers
Mike
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.
Mike, In following up on my previous question, I can’t find catctl.pl in the 11204 binaries, this maybe only a 12c thing or platform specific.
Regards,
catctl.pl is a pure 12c tool – we had just given it to a number of selected high profile customers for 11.2.0.3 evalutation – but it is not available for 11g databases to anybody anymore.
Cheers
Mike
OK, so what’s the point of the post if it’s not available to the majority of the customers.
Regards,
Not sure what you mean with your post???
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.
Regards
Sorry … my fault – now I got your point and you are correct.
Please exchange "catctl.pl" with "catupgrd.sql". For catctl.pl the ECHO ON won’t help you as it is a SQL Plus setting but catctl.pl runs outside of sql*plus.
So appologize 🙂
I’ve meant catupgrd.sql
Cheers
Mike