Support Article
High SQL query execution causes poor database performance
SA-15269
Summary
Severe performance issue occurs in all databases on the cluster due to one SQL query executing around 35,000 times/hour. The tables where clause were not indexed, are impacting the database cluster which causes performance issues on applications.
Select pzInsKey from pr_sys_queues where pzInsKey IN (select pzInsKey from (select pzInsKey from pr_sys_queues where pyItemStatus = :1 and pyAgentName = :2 and pyMinimumDateTimeForProcessing <= :3 order by pzInsKey ASC) where rownum = 1 ) FOR UPDATE SKIP LOCKED
Error Messages
Not Applicable
Steps to Reproduce
Run all processing agents across all nodes, ( around 750000 claims/day in this particular use case).
Root Cause
A defect or configuration issue in the operating environment. The stored procedure: Sppr_Sys_reservequeueitem_b sql has to be modified to fix the issue.
Resolution
Apply HFix-8364.
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.