One of the best things with my role in Oracle’s Upgrade Development is:
I work with customers – and I learn a lot from customers.
This is an issue Richie and his team from Accenture in Ireland brought to my attention. I try to support them currently in an EBS database upgrade. And he did highlight a potential issue with the post upgrade time zone change to me:
Bug 10209691 – slow performance on ALL_TSTZ_TAB_COLS
Workaround: alter session set “_with_subquery”=materialize;
All together the upgrade of an Oracle 9.2.0.8 EBS 11i database takes approx 30 minutes. And we still do recommend upgrading the Time Zone Data post upgrade as first of all we use the data type TIMESTAMP WITH TIMEZONE internally in the dictionary and – more important – the scheduler relies on correct settings. Otherwise it could happen that jobs don’t get executed.
Of course Roy and me added this information to the slides already 😉