Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Poor performance with pc_data_uniqueid in case creation



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

  1. Create lots of cases using Pega service and let the test run.
  2. 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.


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.


  MINVALUE 100000
  START WITH 100000
  CACHE 1000;

    ReturnKey OUT VARCHAR,
    tpyPrefix IN VARCHAR,
    tpyOrganization IN VARCHAR,
    tsuffix IN VARCHAR)

-- 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);


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;


Published January 31, 2016 - Updated October 8, 2020

Was this useful?

0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us