Seeking PR_DATA_ADMIN tuning advices
We are encountering long wait times during startup on locks happening on the PR_DATA_ADMIN table.
We are looking on advice for tuning the access to this table (mainly during the startup of the JVMs).
To be more specific the query that leads to most of the locks is the following:
UPDATE <SCHEMA_NAME>.pr_data_admin SET PXCREATEDATETIME = :1 , PXCREATEOPERATOR = :2 , PXCREATEOPNAME = :3 , PXCREATESYSTEMID = :4 , PXINSNAME = :5 , PXOBJCLASS = :6 , PXSAVEDATETIME = :7 , PXUPDATEDATETIME = :8 , PXUPDATEOPERATOR = :9 , PXUPDATEOPNAME = :10 , PXUPDATESYSTEMID = :11 , PYACCESSGROUP = :12 , PYDEFAULTAPPNAME = :13 , PYDEFAULTAPPVERSION = :14 , PYLABEL = :15 , PYMANAGER = :16 , PYNAME = :17 , PYORGANIZATION = :18 , PYORGDIVISION = :19 , PYORGUNIT = :20 , PYOWNER = :21 , PYRULESETNAME = :22 , PYSETTING = :23 , PYSYSTEMNAME = :24 , PYWORKBASKET = :25 , PYWORKGROUP = :26 , PYWORKPOOL = :27 , PXPRODUCTNAME = :28 , PXPRODUCTPATCHVERSION = :29 , PXPRODUCTVERSION = :30 , PXSYSTEMNAMESETFROMFILE = :31 , PYEXPIRATION = :32 , PYNODENAME = :33 , PYPURPOSE = :34 , PYTIMESTAMP = :35 , PYWORKGROUPNAME = :36 , pxCommitDateTime = CURRENT_TIMESTAMP , pzPVStream = :37 WHERE PZINSKEY = :38
A last note is that we had an SR raised (SR-B69134), but were redirected here due to the category of the request.
***Updated by moderator: Lochan to add SR details***
JPMorgan Chase & Company
US
Hi,
Could you please let us know how many number records are there in pr_data_admin table. Recommended start node by node if you are having multiple nodes and reindex the table. I hope this will resolve the issue.
Pegasystems Inc.
CA
We have 675 records on the system this was initially reported on.
I am not sure I understand your recommendation, can you please elaborate. We have multiple JVMs accessing this Pega7.2 instance.
There are already 4 indexes defined on the table, and all of them are reported as valid.
Verizon Wireless
US
Hi Moderators,
Could you please take this issue to the notice of a PEGA DB expert.
Thanks a lot.
Regards,
Pradeep Pydi.
PEG
GB
Hi Pradeep,
I'm not a moderator.
But it may be difficult to solicit for assistance of a DB expert without knowing what database you are using.
Thanks
Laurence
Verizon Wireless
US
Hi Laurence,
Sure.Please find the details below.
Oracle Database 12c Enterprise Edition Release Proprietary information hidden.0 - 64bit Production
PL/SQL Release Proprietary information hidden.0 - Production
"CORE Proprietary information hidden.0 Production"
TNS for Solaris: Version Proprietary information hidden.0 - Production
NLSRTL Version Proprietary information hidden.0 - Production
Will wait for the PEGA Oracle DBA to come up with any further questions.
PEG
GB
Thank you both.
So the SQL itself looks good. The pzInsKey should be defined as the primary key so this will also have an associated unique index, and therefore with a small number of entries in this table, I would have thought it should be running quickly.
I've take a look in my 7.2 instance, by running: -
select * from v$sql
where sql_text like '%pr\_data\_admin%' escape '\'
But I'm not seeing the same update statement that you are having a problem with. So I'm not too clear what is issuing this update.
There are some techniques which you may be able to use to try to establish this.
For example, if you add the following entries to your prconfig.xml this should enable global db trace during startup
Thank you both.
So the SQL itself looks good. The pzInsKey should be defined as the primary key so this will also have an associated unique index, and therefore with a small number of entries in this table, I would have thought it should be running quickly.
I've take a look in my 7.2 instance, by running: -
select * from v$sql
where sql_text like '%pr\_data\_admin%' escape '\'
But I'm not seeing the same update statement that you are having a problem with. So I'm not too clear what is issuing this update.
There are some techniques which you may be able to use to try to establish this.
For example, if you add the following entries to your prconfig.xml this should enable global db trace during startup
Thanks
Laurence
Pegasystems Inc.
CA
Thank you for the suggestions, Laurence.
Just a heads up, the performance of the query is not our concern.
This Pega DB instance is accessed by 2 applications spread across a cluster of 40 + 20 JVMs. Usually both applications are stopped (leaving just one JVM for procedures) and then started at the same time during the deployment windows.
The main concern we have is with the locks this query causes during the startup.
So I guess our main question would be -- other than starting 60 JVMs in sequence -- is there a way to avoid or attenuate the effects of the locks on the table during the startup, when this query executes?