Maximizing Your Investment in PeopleSoft

Melanie Kacerek, Quest Software, Inc.

The Challenges

So, you’re not doing cartwheels yet? You have spent more time and more money deploying an ERP solution than you ever expected. The process of retooling your business practices to fit the software’s expectations has been a trying experiment, and you are still not sure all of your employees have embraced the new methodology. On top of these pressures, you have some outstanding issues, and a few new concerns.

Performance First, your company seems to have apparently mismatched the number of concurrent users to the number of CPUs. Either that, or the performance statistics you anticipated were not representative of the modules your company has deployed. Whatever the theory, the first problem is performance. Online users are not able to enter transactions as quickly as they are capable of doing. To reduce the I/O contention, you have begun to enforce a report generation window, limiting which reports are run, and at what time they may be run. This makes the A/R department nuts, because when they are making collection calls, they hate finding out that the data reflected in the reports is stale, and that the person they have just contacted really did send in the check already.

Management Next, you are faced with the challenge of educating your MIS department about what should and should not be happening with the new software, what should and should not be running, et cetera. Without having established baseline expectations, how can your staff be expected to identify exceptional issues. When is the system really running too slowly? When is a process really taking too long, or consuming too many resources? For that matter, which processes need to be monitored anyway?

Month-end If the day-to-day issues were not enough, you have already learned to dread the monthend process when it seems that your system is going to crumple with exhaustion before you are able to close the books. During this period, you cannot enforce the reporting window rules, so you have large summary queries running preliminary reports while your users struggle to complete the “last few transactions.” You are barely meeting your deadlines, or you may have slightly missed a few, and you know that the tightrope act is guaranteed to eventually fail.

Customization Although you are currently succeeding in resisting the temptation to customize the software, your excuse of “let’s get the routine established first” is losing ground. 4 Experienced users can imagine countless ways to “tweak” the system to do everything the previous solution did, plus take advantage of all the features that were why you purchased this software originally. In truth, you have already made a few customizations, but those you had to do. Nagging you is the persistent worry that once you customize, that your options to upgrade will be seriously jeopardized, or at least the prospect of a relatively simple, smooth, even seamless upgrade is reduced to a myth. How can you guarantee that when you upgrade, your customizations will not be lost? That days of productivity will not be compromised? Yet, if you do not make a few more concessions, how many man hours will be spent, attempting to recreate the solutions you had previously?

Availability All this, and you are also concerned that your business is more dependent on its data than ever before. Now that you have calculated the costs involved in deploying this system, you are painfully aware of the costs associated with downtime.

Yet, you can easily foresee some planned downtime, for database maintenance, hardware maintenance, and upgrades. Plus, you can anticipate unplanned outages resulting from the stress you are placing on the system during monthend. If the system fails during month-end, you will definitely miss your deadline. Now that you have spent so much money on the software and its implementation, how do you maximize that investment? By solving this short list of issues! The Solutions The place to start is the problem that if solved, will return the greatest benefitperformance. If your implementation can run faster and smarter, you can resume using your employees’ capabilities to their fullest, rather than reducing their productivity because the system is slow. To improve performance, it is important to start with the basics. First, look at the database. Does it have any bottlenecks? Is it switching redo logs too frequently? Does it have large enough rollback segments to handle the requirements of the Edit/Post process? Are the components of the database distributed across multiple disk drives to optimize their access? What about static issues – are there any objects that were poorly deployed, already consuming lots of tiny extents? And dynamic issues – can you tell when a group of objects is getting too much activity, so that the I/O maximum is reached, and the need out distances the system’s ability to service the data requests?

Performance Can you effectively separate online transaction processing (OLTP) from the frequent ad-hoc queries and the requested reports, without substituting the overhead associated with queries, with overhead incurred in creating and maintaining a reporting instance? Once you create that separate reporting instance, can you further maximize its utility by creating an efficient report repository for popular reports, so that each employee in the A/R department does not run a separate aging report? 5 The answer to each of these questions on performance is yes. With the right tools, you can easily identify the trouble spots within your database and begin taking corrective action. Simple to use diagnostic tools can help your MIS staff quickly focus on the various types of database issues. They can focus on the few SQL statements that are consuming the majority of the processing power and tune them to run more efficiently.

Several vendors provide products that perform these functions. In the interest in brevity, this paper will use the Quest Software products as examples of the available solutions that can assist in optimizing your PeopleSoft environment. For example, Quest’s Spotlight on Oracle is an excellent tool to help DBAs quickly analyze the database’s performance. Easy to install and intuitive to use, this product enables the DBA to spend more time correcting an issue, and less time analyzing the situation. As you can see in Figure 1 below, the product uses the traffic light coloring scheme, equating good situations in green, warning situations in yellow, and critical issues in red.

At a glance, the DBA is drawn to the issue in red (the hit ratio for the SGA is only 71%), and when the DBA clicks on the aspect of the display in red, helpful information is relayed which can be used to correct the problem. Once the issues with the underlying database have been resolved, your team can begin looking at tuning opportunities. Using the 20/80 rule, where 20 percent of the SQL statements represent 80 percent of the work being done, you can easily see the benefit of tuning those top few SQL statements. Identifying them is a snap. So is tuning them, if you can get expert advice or if you can use a realistic sandbox to test out a few options. With SQLab Vision, you can do all three. Identifying static space related issues is a task that can be done manually, or with a tool, it can be done quickly.

If that tool provides diagnostics, plus capacity planning and problem prevention, so much the better. If in addition to identifying the issues, the tool enables you to correct the issues without interrupting production activity, fantastic. Again, there are several tools on the market that can provide some of this functionality. 6 Quest Software’s LiveReorg product is the only solution in the market today to provide this functionality, including online reorganizations of key tables and their related indices with virtually no downtime. What takes LiveReorg to a new level of sophistication is its ability to diagnose dynamic I/O bottlenecks. Previously, these bottlenecks required the combined skills of a senior DBA and a senior system administrator, plus a week’s worth of effort, to diagnose the source of an historical bottleneck.

Prior to the release of the optional StorageXpert feature, it was virtually impossible to identify and diagnose these I/O bottlenecks as they occurred. With StorageXpert, you can easily see the correlation of physical files to database objects. Now, rather than pointing fingers at each other, the DBAs and System Administrators can use StorageXpert to bridge their communication gap, to identify the root cause of the bottlenecks. And again, once the issues have been identified, LiveReorg enables the separation of heavily accessed objects to take place without interfering with business activity. Once you have optimized performance of your production instance, you can further improve its performance by separating all query activity from OLTP activities. PeopleSoft makes extensive use of long columns and sequences. In order to fully support all query and reporting needs, your reporting instance must maintain these data items. Otherwise, you will only be able to relocate some of your query activity. And, as experience has taught you, it only takes one poorly constructed query to bring the system to its knees.

To create a reporting instance, you have a number of options. The top issues to consider when selecting a solution in this area are: Can the solution maintain long columns and sequences? 7Can it maintain the reporting instance in real-time, so that the data reflected in the reports is as accurate as it would be if they had been run on the primary system? Can the reporting instance be maintained without crippling the production instance’s performance as a result of increased overhead? Can the reporting instance be maintained without saturating the network? Can the solution be implemented and maintained without increasing planned downtime for setup and if necessary for resynchronizing? Many PeopleSoft sites, including PeopleSoft itself, have selected SharePlex for Oracle to create and maintain a live reporting instance. SharePlex uses special log-based replication technology to minimize its impact to the primary system, to minimize its impact to the network, and to minimize the latency between the time a transaction is entered on the production system and when it is reflected in the reporting instance.

As a result of this design, it is able to replicate business volumes of transactions hitting the thousands of PeopleSoft tables without significant overhead. SharePlex Overdrive (an optional feature) enables the instantiation of the replica with virtually no downtime for the production users. And, if re-synchronizing is ever required, it can be performed using a hot backup, so that end users on the primary system experience no interruption. Since the replica database is independent, you can tune it for the query access you anticipate, adding keys and indices that you couldn’t afford to add to the production instance. So, with the replica, you have additional optimization opportunities. The simplest tuning technique for a reporting instance is to reduce the number of reports run against it.

If you find users in a department all running their own copies of the same report, imagine the improvement in performance that you could gain if they “carpooled” their reporting requirements. With Vista Plus, you can create a report repository that stores, archives, and eventually deletes such reports. The reports can be viewed online with extensive query options for efficient reviews; and, they can be secured from unauthorized access at the same time. Using a report repository, you can shorten the access time to critical information, thereby increasing user productivity, and in turn, reducing the total cost of ownership of PeopleSoft applications by lowering the cost per seat to distribute, view, analyze, extract, download and archive reports. The combination of creating a real-time reporting instance and maximizing the utilization of reports without duplicating their processing, significantly leverages your investment in a reporting instance, while maximizing your OLTP performance which improved with the offloading of all query activities.

With these tools in place, you not only solve the day-to-day performance issues, but you have also eliminated the concerns about month-end processing. Now, those final transactions can be quickly input into the production system because the preliminary reports that reduced the production system to a standstill have been relocated to a separate system. 8 In addition, you have deployed a solution that enhances your options for high availability – reducing planned downtime and the impact of unplanned outages. By maintaining independent instances, you have the opportunity to stage all sorts of maintenance. You can perform maintenance on one system while production activity continues on the other.

Once the maintenance, such as an O/S or Oracle DBMS upgrade, is completed, the deferred transactions may be applied, and the instance can catch up with activity it initially missed.
By replicating at the transaction level, the replica is immune to the disastrous effects of accidental table drops and disk level corruptions (ora600 errors), so unplanned outages that would have jeopardized business continuity are minimized. Instead of halting production activity until the database can be recovered or rebuilt, a quick alias and dblink can be implemented to keep business operations on track.
Customization Once you have maximized the performance of your PeopleSoft environment, your next focus is on customization. Clearly you want to maximize your company’s productivity as well as the performance of the software. To do so, rather than making all business practices conform to the software’s expectations, you may choose to make some customizations in order to restore some of the proven solutions from the past. With Stat, you can create and save all versions of PeopleSoft objects indefinitely, with the ability to immediately roll back objects as desired. The upgrade process is no longer under the cloud of uncertainty, because Stat can safeguard your customizations and actually accelerate the upgrade process. Considering that the average cost of an upgrade exceeds $1M, and that most PeopleSoft environments upgrade every 12-24 months, efficiently upgrading is a serious challenge that can now be easily handled.

Monitoring With performance and customization issues out of the way, you are left with the challenge of simplifying the management aspects for your MIS staff. Enterprise monitoring comes in two basic flavors – server based and agent based. Quest’s solution in this space is Foglight, an agent-based enterprise monitoring option that proactively discerns system problems, correcting their causes before end users are affected. The agent-based architecture reduces the monitoring overhead on the systems, eliminating the concern that the cure is worse than the bite! Foglight integrates with the other Quest products so that the process if identifying, diagnosing and resolving issues is easily performed. Although the majority of this paper focused on the solutions available for Oracle-based PeopleSoft deployments, many of the same solutions are available for PeopleSoft environments running DB2.

Conclusions

Although it can be discouraging to have deployed an ERP solution and not be completely thrilled with it, it is possible to achieve a much higher rate of satisfaction by taking a few steps to improve performance, to manage customizations, to increase availability, and to ease the management of the environment. With many products on the market, you can easily find the ones that satisfy your needs once you develop your own list of issues and prioritize them. Don’t give up, when you are so close to the finish line!

Melanie Kacerek is the Director of Product Management at Quest Software.