Catharine,
You've hit exactly on the main issues that our other clients are experiencing while preparing to migration from older OTM versions (4.0,4.5). There's a multi-pronged approach that we normally take to bring these migration windows down into something more acceptable:
- Purge as much data as possible. Many clients don't have a data retention policy and the OTM databases get out of control. This improves all-around performance as well as migration times.
- Ensure that your DB is running as efficiently as possible, both from a hardware perspective and an Oracle DB tuning perspective. For instance, our new Linux servers with fast disk arrays run many of these intensive scripts over twice as fast as HP-UX, Solaris and AIX boxes from only a year ago. We've also removed indexes in the past and re-created them post-migration in order to speed up the process.
- Tune the migration scripts, just as you've suggested.
So - directly to your point, great work in tracking this down to the pop procedure! I definitely agree that increasing the commit count is a great way to decrease the processing time. While I'm not familiar with this procedure directly, I can't see any reason that altering the procedure, if only for the duration of the migration, wouldn't help significantly. I can't think of anything in the migration process that would be adversely affected by this - as long as you have enough rollback and temp space available.
Are you at the point where you can give this a try and report back your results?
Thanks!
--Chris