Support Article
Stored procedure SPPC_DATA_UNIQUEID is performing poorly
SA-7732
Summary
Stored procedure PEGA_DATA.SPPC_DATA_UNIQUEID is performing over the threshold consistently
Error Messages
Following alert is written to the PegaALERT log file.
*Database operation took more than the threshold of 5000 ms: 384,350 ms SQL: {call PEGA_DATA.sppc_data_uniqueid (?,?,?,?)}*
Steps to Reproduce
NA
Root Cause
Issue is not seen in all the environments; It depends on the various factors of the database and its performance.
Resolution
Following steps were taken to improve the performance of stored procedure SPPC_DATA_UNIQUEID
- Create index pc_data_uniqueid_idx1 on <schemaname>.pc_data_uniqueid (pzInskey, pyLastReservedID)
- Recreate the table with higher initrans, like 10. This is to allow multiple concurrent sessions to update the same data blocks.
- Move pc_data_uniqueid table to a 4k table space.
Published January 31, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.