SA-37486

Summary

A database administrator has identified two possible points of tuning their PegaRULES database. The SA wants to formally present these ideas to Pega Engineering for a sanity check before implementation. The ideas are:

SUGGESTION 1
-------------------------
Rebuild PRPC standard PR_SYS_LOCKS_PK primary key constraint with 90% PCTFREE so that INDEX entries per block are reduced. This would possibly reduce the chances of multiple transactions trying to update the same INDEX block.

If this appears effective, the next step would be to implement this change to PRPC standard schema objects: PR_SUS_QUEUE_SLA_INDEX1 and PR_SYS_QUEUEU_FTSINDEXER_PK

SUGGESTION 2
-------------------------
There are high number of DELETEs running against table ( 9000 deletes per hr ) – PR_SYS_LOCKS during the same time period as the INSERTS/Updates are running, if there is a configuration setting available , we should shift these DELETEs to an Off-peak period to reduce the number of commits generated by the application during peak periods.

Error Messages

No error messages are displayed on screen; no specific error messages are found in the Pega Logfile. However, multiple PEGA0005 ALERTs are observed in the Pega ALERT log detailing poor database performance.

Steps to Reproduce

Unknown. No specific steps were identified that are causing the poor database performance. The poor performance is sporadic and appears at random.

Root Cause

Performing additional testing, the SA has identified a possible hardware fault.

Resolution

Pega Engineering provided the following answers to the two database administrator questions:

ANSWER 1
------------------
This suggestion might help the problem, but most likely will not be enough.

Engineering has instead provided these two suggestions:

a) Recreate the primary key on pr_sys_locks using reverse index. This will help the index insert contention and the ‘global buffer busy’ in RAC system. As we don’t use range scan on primary key, this change should be without much downside impact.

b) Add freelist to this table if it’s not using Oracle ASSM. This should help the ‘log file sync’ issue.

ANSWER 2
------------------
This suggestion is invalid: Process Commander SYSTEM-LOCK is itself a function, and cannot be delayed.