Post To WIP Store Procedure PAC_PRC_POSTTOWIPS_SP performs a Full Table Scan causing performance degradation

Products

• CLARITY PPM FOR ITG
• CLARITY PPM FEDERAL
• Clarity PPM SaaS - Application
• Clarity PPM On Premise

Issue/Introduction

Description:

POST TO WIP: A query in the Stored Procedure PAC_PRC_POSTTOWIPS_SP performs full table scan so take a long to execute when there are many transactions. The query that takes most of the time is in 'PAC_PRC_POSTTOWIPS_SP' stored procedure. For about 1,870 records it take about 11 minutes to post.

Steps to Reproduce:

1. Create about 500,000 Financial transactions so they are ready to be Posted to WIP
2. Navigate to Post To WIP page
3. Using Date filters, filter for say 1,870 transactions
4. Click on 'Post' button to move these transaction to PPA_WIP and PPA_WIP_VALUES table

Expected Result: Expect transactions to get posted under 4 minutes.

Actual Result: It takes about 11 minutes for the Post To WIP procedure to complete.  

  

Solution:

WORKAROUND:

Contact CA Technical Support for review and possible workaround in v13 or upgrade to v14.1.  

When you open a case please provide the following for analysis so that we can confirm if you are running into this issue:

• Full Background Log files.  
• Any Database Logs showing slow performing queries.  
  • Does it show a Full Table Scan is occurring?
• If using Oracle, get an AWR.  
• Outcomes of trying to run the job in smaller batches as identified in the steps above.    
• When processing the smaller batches what are the timing statistics?  
• Provide a copy of the database stored procedure named 'PAC_PRC_POSTTOWIPS_SP' from your database schema.  
• Check if there is an existing index definition for PPA_TRANSCONTROLAPINFO_ID and provide the details from your database schema. 

  

STATUS/RESOLUTION:

CLRT-73754

Resolved in CA PPM 14.1  

Environment

Release: ESPCLA99000-13.2-Clarity-Extended Support Plus
Component: