This tip is brought to you by the International Oracle Users Group (IOUG), a user-driven organization that empowers Oracle database and development professionals by delivering the highest quality information, education, networking and advocacy. To become a member of the IOUG and gain access to thousands more tips and techniques created for Oracle users by Oracle users, visit http://w3.ioug.org/searchoracle2. Part 1 of this series explained the process behind a PeopleSoft upgrade and the steps to prepare for it.
Discovering the truth
Armed with your plan, and once you've confirmed your upgrade path is certified, your first step will be to install the new software. The upgrade from version 7.x to 8.x is more along the lines of a new install and then a conversion. You will first have to install the new application software and PeopleTools and then create the DEMO database for the new application. This will give you your first look at the new system, acquaint you with differences in the toolset and set the stage for your first conversion.
With the DEMO database installed, you'll install the Upgrade Assistant, a tool new to PeopleSoft 8.x, that when loaded with upgrade templates specific to your upgrade path contains a run-time mechanism (including documentation) to execute all technical steps required to complete the upgrade process. The templates come in two flavors: the 'initial upgrade' and the 'move to production.' The main difference between the two is that the move to production builds upon the initial upgrade by extracting converted PeopleTools information (configuration information) from the 'initial upgrade' environment in order to convert a (copy of) production data.
Initial upgrade and the first move to production
The initial upgrade walks you through the steps required to convert/upgrade your PeopleSoft 7.x system to version 8.x. This upgrade will likely take the longest, as it is the first you will have performed. The upgrade assistant and the PeopleSoft message log tables will record the run-times of jobs executed by the Upgrade Assistant during the conversion. It is important to save these for later analysis to determine the 'pain points' or longest running portions of the upgrade process.
Many of the steps used in the initial upgrade are repeated in subsequent practice move to production upgrades. As mentioned previously, the former serves as the building block for the latter. The move to production upgrade is the one that you must practice until the results are predictable (no failures) and the elapsed time is acceptable (tuned).
This first move to production conversion will provide the foundation for future tuning efforts. At my company, the initial upgrade took place over approximately three weeks elapsed time with a cumulative conversion time of approximately seven days! Needless to say this wasn't going to be acceptable for the final run as the plan going into the project was to complete the conversion over a long weekend.
Uncovering which areas you will need to concentrate on in order to improve the overall elapsed time is a fairly simple exercise. Your first guideline may be that you'd hoped to complete the overall conversion or upgrade in a long-weekend (three days). If your initial upgrade or first practice move-to-production takes significantly longer -- you know you have some work to do. Using the upgrade assistant, which contains run timings for each of the prescribed upgrade steps, and the PeopleTools message log, which contains detailed task timings for the conversion Application Engine programs (UPG_xx), you can quickly pinpoint which steps took the longest and therefore which steps you'll need to tackle in order to improve the overall upgrade execution time. Making improvements to delivered PeopleSoft upgrade steps requires any or all of the following:
- Strong technical skills including SQL and PeopleSoft-specific application engine, process scheduler and database administration in order to combine steps which 'touch' or perform updates to the same table multiple times.
- A close working relationship with functional team personnel to understand what exactly is being modified and to confirm that the results of a tuning attempt are correct.
This may seem burdensome and it is, since you will unfortunately have to become intimate with how a particular PeopleSoft application function (for example Journal Ledger) works. This is truly where the 'black box' concept of performing the upgrade disappears. You'll need to employ everything in your full bag of tricks in order to make the upgrade happen in a reasonable (e.g. long weekend) amount of time.
The sections that follow, detail what was done to mitigate or tune several of my company's longest running processes uncovered in the initial move-to-production and improved upon in successive practice moves.
Condensing the upgrade
One of the first tuning improvements made was to archive as much data from the production system as possible. Having less data to convert means a faster conversion run.
The second general tuning improvement was to run as many of the prescribed upgrade steps concurrently. This simply entailed splitting up long running jobs into many smaller ones, usually by moving the jobs out of the Upgrade Assistant and into jobs that can be scheduled through the Process Scheduler. A manual step was left in the Upgrade Assistant conversion stream in order to allow for the switch over to Process Scheduler.
Oracle database ('init.ora') changes were made dynamically -- i.e., within each of the specific conversion steps -- and executed as separate SQL steps or executed system wide. The system wide settings were reset at the end of the conversion routines. Some example of the 'init.ora' / Oracle database changes:
- Increased the 'db_file_multiblock_read_count' as high as possible (mine was set to 512, beyond what the hardware supports) to allow 'read ahead' and large block reads per I/O.
- Redo logs were also greatly increased (to 200 MB from 20 MB) so that the Oracle database writer did not have to sync each database file very often.
- Disabled archivelogging to reduce I/O waits to the disk subsystem and unneeded logging.
- Indexes were created with the nologging option (an unrecoverable action but totally acceptable)
One of the most important tuning improvements was to practice, practice, practice the move-to-production process. This allowed for tinkering with the upgrade process and also allowed for incremental improvements in order try out many ideas. With each iteration of the move-to-production process, the next longest running process could be analyzed to determine where next to focus the tuning effort.
The initial move-to-production process ran for approximately two days. Following the general approach of splitting the single 'alter table' application engine (AE) Upgrade Assistant job into multiple AE jobs, the longest running SQLs were identified and placed into separate AE jobs and then scheduled to run concurrently through the Process Scheduler.
The method by which a table is altered (to make a temporary copy; to drop the original and rename the temporary table to the actual and then rebuild the indexes) was also changed for several tables. These included tables that were partitioned (PeopleSoft does not take this into consideration when performing its 'alter') or for tables which had multiple conversion steps. To get around this, an 'alter in place' was performed where possible. For tables that had columns modified with an increase in precision or extra columns added the Oracle SQL 'alter table... modify column list'; followed by an 'alter table ... add column list' was issued to complete the change to the table definition. This had three advantages over the delivered PeopleSoft method: 1) the changes occur almost instantly (no data copy), 2) did not require twice the table space required to perform the PeopleSoft copy, and 3) preserved the existing table indexes (no need to rebuild).
For tables that were taking a long time and required to use the PeopleSoft copy/rename method of performing a 'table alter,' Oracle parallel was used (e.g. alter table … parallel 4).
The result was that this step of the conversion ran in six hours.
Part 3 of this tip will discuss application specific conversions.