Skip to main content
Kofax

MarkView Performance : Slow performance caused by SF_USER_ITEMS view sql

Summary

11914

Applies To

  • ERP System: Oracle.
  • MarkView Version: 6.x and later.

Behavior

Performance issues may be encountered in MarkView as a result of the SF_USER_ITEMS view in the MarkView schema containing a "+0" in the WHERE clause. The Oracle Optimizer may choose a sub-optimal plan as a result.

Resolution

Customers should modify the SF_USER_ITEMS view to remove the "+0" from teh WHERE clause as shown in the example below:

Before:

WHERE RQP.ROLE_ID = EUR.ROLE_ID AND Q.QUEUE_ID = RQP.QUEUE_ID

AND RQP.FUNCTION_ID = F.FUNCTION_ID AND Q.QUEUE_ID = I.QUEUE_ID

AND FR.ROLE_QUEUE_PRIVILEGE_ID = RQP.ROLE_QUEUE_PRIVILEGE_ID + 0 AND I.WORKITEM_INSTANCE_ID + 0 = FR.WORKITEM_INSTANCE_ID

AND RQP.PRIVILEGE_CODE = 'RETRV'

After:

WHERE RQP.ROLE_ID = EUR.ROLE_ID AND Q.QUEUE_ID = RQP.QUEUE_ID

AND RQP.FUNCTION_ID = F.FUNCTION_ID AND Q.QUEUE_ID = I.QUEUE_ID

AND FR.ROLE_QUEUE_PRIVILEGE_ID = RQP.ROLE_QUEUE_PRIVILEGE_ID AND I.WORKITEM_INSTANCE_ID + 0 = FR.WORKITEM_INSTANCE_ID

AND RQP.PRIVILEGE_CODE = 'RETRV'

Note: Customers should perform this change on a quiet system and should test in Non-Production prior to making the change in Production.

After the change is made, it is recommended that customers rebuild indexes and gather statistics referencing the following articles:

Keywords: role queue privilege retrieval retrieve