Support Article
Poor performance with pc_data_uniqueid in case creation
Summary
Pega case creation in production is slow. There is a bottle neck with pc_data_uniqueid table when work is created.
Error Messages
Not Applicable
Steps to Reproduce
- Create lots of cases using Pega service and let the test run.
- Monitor the pc_data_uniqueid table performance.
Root Cause
A defect or configuration issue in the operating environment
The pc_data_uniqueid table is not scaling well because the table is part of the case creation transaction and this table is locked until the case is created. If there are multiple pega nodes for case creation, then contention for the pc_data_uniqueid table increases.
Resolution
Perform the following local-change using Oracle sequence:
Note: Oracle sequence does not guarantee the sequence, and there could be gaps in work item numbers. If there is more than one work type, then create the unique Oracle sequence for each work type such that the next work ID for each work type is maintained by the respective Oracle sequence.
CREATE SEQUENCE PRPCUNIQUEID
MINVALUE 100000
START WITH 100000
INCREMENT BY 1
CACHE 1000;
CREATE OR REPLACE PROCEDURE SPPC_DATA_UNIQUEID (
ReturnKey OUT VARCHAR,
tpyPrefix IN VARCHAR,
tpyOrganization IN VARCHAR,
tsuffix IN VARCHAR)
AS
--
--
-- Copyright (c) 2013 Pegasystems Inc.
-- Generate a Unique ID for a Work Item
--
-- Date Who RuleProID Description of Modification
-- 17-Jan-13 your_userID Use a sequence rather than a cursor. Accept/expect gaps between work id's--
tpxCreateDateTime date;
tpxCreateOpName varchar2(128);
tpxCreateOperator varchar2(128);
tpxCreateSystemID varchar2(32);
tpxInsName varchar(128);
tpxObjClass varchar2(96);
tpxUpdateDateTime date;
tpxUpdateOpName varchar2(128);
tpxUpdateOperator varchar2(128);
tpxUpdateSystemID varchar2(32);
tpyLabel varchar2(64);
tpyLastReservedID number(18);
tpzInsKey varchar(255);
tnewLastReservedID number(18);
tupdater char(1);
begin
tpxCreateDateTime := NULL;
tpxCreateOpName := NULL;
tpxCreateOperator := NULL;
tpxCreateSystemID := NULL;
tpxInsName := upper(tpyOrganization || '!' || tpyPrefix);
tpxObjClass := 'Data-UniqueID';
tpxUpdateDateTime := NULL;
tpxUpdateOpName := NULL;
tpxUpdateOperator := NULL;
tpxUpdateSystemID := NULL;
tpyLabel := NULL;
tpzInsKey := upper(tpxObjClass || ' ' || tpyOrganization || '!' || tpyPrefix);
tnewLastReservedID := 1;
tpyLastReservedID := 1;
ReturnKey := tpyPrefix || tnewLastReservedID || tsuffix;
-- Select pyLastReservedID into tpyLastReservedID from pc_data_uniqueid where pzInsKey = tpzInsKey for update;
-- newLastReservedID := tpyLastReservedID + 1;
select PRPCUNIQUEID.nextval into tnewLastReservedID from dual;
ReturnKey := tpyPrefix || tnewLastReservedID || tsuffix;
end;
/
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.