pepdwill Honored Contributor.
Honored Contributor.
103 views

KNTA_ELIGIBILITY_CHECK_V Performance

Hi -

 

In 9.2 service pack 3 an improved db view was introduced to replace KCRT_PARTICIPANT_CHECK_V.    Similarly, the KNTA_ELIGIBILITY_CHECK_V  view also suffers from very slow performance.   I was wondering if there are any plans for an updated (better performing) Eligibility check view?

 

Some additional context (and/or a separate question) is that we will be logging a support case with HP about this.  Following poor performance in our Prod environment this morning we had our DBA do some research, and the AWR report showed that the KNTA_ELIGIBILITY_CHECK_V view was by far the greatest bottleneck in the system, and was getting referenced up to 8 MILLION times per hour.  If anyone has insight into any specific issue that might cause such an event, please share.

 

 

Thanks,

Danny

0 Likes
4 Replies
Absent Member.. fabsvz Absent Member..
Absent Member..

Re: KNTA_ELIGIBILITY_CHECK_V Performance

Hello Danny,

 

This is a known issue, caused by KNTA_ELIGIBILITY_CHECK_V performance, which view is used in cases when the "eligible for my action" option is selected.

 

There is little to be gained in the performance of the "eligible for my action" option, as this needs to check every request in the system specifically with regards to security on the current step - it is very intensive.

I was looking at several old cases, where indexes were created that seemed to improve the performance of the eligibility check.

 

Please backup your database before making any changes, and test it on a non-production instance first:

  • CREATE INDEX kwfl_workflow_instance_step_n105

ON kwfl_workflow_instance_steps (workflow_instance_id, active_flag, workflow_step_id,

current_step_transaction_id)

COMPUTE STATISTICS;

 

  • CREATE INDEX kwfl_workflow_instances_n107

ON kwfl_workflow_instances (top_instance_source_id, top_instance_source_type_code,

current_flag)

COMPUTE STATISTICS;

 

  • In the Sql code in the PPM schema for the KNTA_ELIGIBILITY_CHECK_V view, search for the following block in the first WHERE clause

"

AND cwis.active_flag || '' = 'Y'

"

and change it to:

"

AND cwis.active_flag = 'Y'

"

 

Please let me know if this helps.

Thanks,

Fabiola Vargas

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
Micro Focus Expert
Micro Focus Expert

Re: KNTA_ELIGIBILITY_CHECK_V Performance

Checking "Eligible for my action" is indeed a performance killer, and as Fabio indicated there's no easy way to significantly improve performance as PPM has to go through all request steps one by one and verify whether the current user can act on it.

 

Such a check cannot be easily indexed, thus the need for full requests search. Performance impact increases with the number of requests in the system. There are a couple of things to help though:

 

1) If deleting old data is a possibility, you can use the purging features introduced in PPM 9.30 to delete old useless requests. 

 

2) Many users will typically create a "todo list" portlet by using a Request List portlet with "eligible for my action" checked, and put it on their dashboard landing page, resulting in frequent unnecessary execution of this performance intensive query. Creating such a portlet is not a problem, but putting it on the dashboard landing page can be, so your users should be instructed to put such "todo list" portlets in a dedicated dashboard page to be loaded only when needed.

 

Thanks,

Etienne.

 

 

0 Likes
Absent Member.. fabsvz Absent Member..
Absent Member..

Re: KNTA_ELIGIBILITY_CHECK_V Performance

Hi Danny,

 

Kindly let me know if the information sent was useful or you need any additional information. We will be more than glad to assist you with your issue.

 

If our answers were useful for you, could you please mark it as a correct answer?

 

Best regards,

Fabiola Vargas

HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.
0 Likes
pepdwill Honored Contributor.
Honored Contributor.

Re: KNTA_ELIGIBILITY_CHECK_V Performance

Thanks for the information and suggestions.  We will try the indexing suggested in our Dev environment to see if it yields any improvements to performance.

 

I should also add that in one custom portlet I am using the KCRT_REQUEST_UTIL.IS_ELIGIBLE_FOR_USER_ACTION database function rather than querying against the KNTA_ELIGIBILITY_CHECK_V table, but it does not seem all that much better performance wise (though it yields the same results).

 

 

 

 

 

 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.