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.
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
* Don't forget to remove or comment this out when you've finished the investigation.
Now, when you next start the system, this should trace the SQL statements issues by the Pega code (a file stating with dbOperation should appear in your PegaTemp directory under /StaticContact/global/ServiceExport directory). So I think it would be interesting to find our whether this update is the first write action on PR_DATA_ADMIN.
If so, I'd be inclined to: -
1. Bring down the application server
2. Manually lock this table with something like
lock table <dataschema>.PR_DATA_ADMIN in exclusive mode;
3. Start the application server
4. After a period of time (which you will need to judge based on past behaviour) this Pega should hopefully be attempting to perform the first update. So this will wait due to the lock.
5. In a separate command window issue a call to kill -3 or jstack to generate thread dumps
6. Examine the dumps to try to establish which thread is issuing the update.
7. IMPORTANT: Return to the SQL command window and issue a "rollback;"
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?