Solving PeopleSoft Performance Issues with Oracle RAC Tuning

While recently working with one of my clients, a large college which has an Oracle RAC 11g on Linux setup, I noticed that certain PeopleSoft batch processes were slow compared to what I have observed at other client sites. I confirmed with the user that the slow performance, in fact, was impacting the end user.

I began to research the issue by taking some database snapshots and I reviewed other statistics that confirmed that there were a large number of enqueue waits for TAO tables. While an enqueue is not RAC specific, it can involves a global lock operation when RAC is enabled. Most of the global requests for enqueues are synchronous, and foreground processes wait for them. Therefore contention on enqueues in RAC is more visible than in single instance environments. I ran a PeopleSoft trace to identify the objects or tables involved and found that most of the waits were on truncate table commands for the TAO tables. PeopleSoft operations use a lot of TAO, temporary tables, which are not truly temporary by Oracle’s definition, however these are actual tables where intermediate operations like sum, group by or other operation is done.

In short these are holding tables which are used to do further operations before rendering the end results.

I analyzed the details of these tables and then compared the behavior of the truncate table command on a single instance database. Oracle RAC database instance obtains the block and maintain inter-instance coordination, using two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). In order to satisfy a query or transaction, Oracle RAC instances the instance serving the transaction, then trawls through the buffer on all the nodes in the database buffer cache to identify if the required block is changed or is present and if found, uses cold fusion technology to transfer any of these blocks as required. Depending on the block size used, it might take longer or shorter to complete this work. This understanding, combined with the trace, lead me to the solution.

For the solution, please contact our database team at Dimension Systems.

Next, I plan on using this solution in a single instance environment to see if it is as effective and if the difference noticeable in single instance environment.