DB Slow Performance Tuning
SummaryA systems administrator has received complaints from the business regarding exceptionally poor performance from the PRPC business application.
Specifically, users selecting to create new work items can wait as long as 70 seconds for Flow Action to begin work (user getting to the first screen of the flow action).
Screen to screen transition times also are observed to take a long time.
These observations are intermittent, but seem to occur more often when the system is under load.
Error MessagesNo error message is displayed on screen.
An examination of the Pega ALERT log shows extreme PEGA0005 alerts - some extending into the 60 and 70 second range.
There are PEGA0002 alerts indicating that Database Commit times are running very long as well.
Note that the environment is an Oracle RAC implementation.
Steps to ReproduceUnknown.
No specific steps have been identified to reproduce the issue. The issue is highly sporadic, but does seem to occur more often when the system is under load.
Root CauseInitial examination pointed at two possible causes:
- Previously identified "omitted" standard Process Commander Schema INDEX objects
- Previously identified issue with standard stored procedure SPPC_DATA_UNIQUEID
However, after providing the additional requisite INDEX schema definitions, and an updated SPPC_DATA_UNIQUEID stored procedure, database performance did improve, but only marginally.
Additional analysis of Oracle AWR report data demonstrated that the root cause of the intermittant database performance problems was related to a bottleneck in the SAN Storage Solution servicing the Oracle Instance.
Specifically, analysis indicated that the Oracle REDO logs were sharing the same storage space as the Oracle Tablespace files, thereby causing a <brief> delay when writes were made to the REDO logs, while read operations were made on the Tablespace data.
This observation was later confirmed by Oracle analysis of the AWR report data.
ResolutionUser is requested to modify the SAN Storage solution:
- change solution from a RAID5 to RAID10 configuration
- separate the REDO log and Tablespace files into their own separate locations
Published April 14, 2017 - Updated May 12, 2017