The PegaRULES database using Oracle Database 9i with a corresponding 9i JDBC driver exhibits slow performance. The SystemPulse, SystemIndexer, and ProcessServiceLevel agents log alerts similar to the following examples:

Database query took more than threshold of 500 ms: 4,149 ms SQL: select
pzClearCacheType, pzObjClass, pzKeyString, pzParameters from pr_sys_updatescache where ((pzNodeName <> ?) or (pzNodeName is null)) and (pzCreateDateTime >= ?) and (pzCreateDateTime < ?) and (pzClearCacheType <> 'Index') order by pzCreateDateTime asc

Database query took more than threshold of 500 ms: 4,422 ms SQL: select pzClearCacheType, pzObjClass, pzKeyString, pzParameters from pr_sys_updatescache where (pzCreateDateTime >= ?) and (pzCreateDateTime < ?) order by pzCreateDateTime asc

Database operation took more than the threshold of 500 ms: 2,006 ms SQL: SELECT pzInsKey as "pxInsHandle", pxGoalTime AS "pxGoalTime", pxGoalExecute AS "pxGoalExecute", pxDeadlineTime AS "pxDeadlineTime", pxDeadlineExecute AS "pxDeadlineExecute", pxLateTime AS "pxLateTime", pyProcessOnNode AS "pyProcessOnNode", pyAssignmentStatus AS "pyAssignmentStatus", pxTaskName AS "pxTaskName" FROM pc_assign_workbasket WHERE pxObjClass = ? and (pxGoalExecute IS NOT NULL ) and (pxDeadlineExecute IS NULL ) and (pxDeadlineTime IS NOT NULL) And (pyErrorAssignment IS NULL) AND ( pxDeadlineTime < ?) and pxRefObjectKey not in (select pxLockHandle from pr_sys_locks where pxExpireDateTime > ? )

To improve database performance, you can now use Oracle 10g thin client drivers for access to Oracle 9i databases.

Suggested Approach

If you are using an Oracle 9i database, upgrade to a 10g thin client driver to improve performance.

Refer to the Oracle references cited below.