How to troubleshoot when a customer complains end users complain that the data steward application is very slow - including taking more than 10 seconds to open a BOVBusiness View (BV), taking more than 1 minute to check out records in a duplicate management workflow, etc.

Linked to Support cases: SUPPORT-2961, SUPPORT-3212, SUPPORT-3255 

...

Slow BVs / Slow navigating through the data

Step 1: Collect information

1. Turn on logging for the data steward application. Follow these instructions.
2. Gather benchmarks metrics to track what is slow. Time however long it takes to perform the task that the customer complains is sluggish.
3. For example, pick a BOVBV. Open the golden data view only. See error log for how long that task took and retrieve the SQL that ran.
4. Run the SQL in SQL Developer. Does it take the same amount of time to execute as it does in Semarchy? 
5. Get the an explain plan.  

# 1 Culprit for slow performance: Sorting

Sort is a highly suspicious component that might be causing bad performance. Sorting requires ordering and the order by is a very expensive task that can cause the application to slow down significantly. The use of an index can have an enormous impact.

Look at the "Sort order by stopkey" cost. Does it seem like a higher cost than the other tasks (like joins) in the explain plan? 

To test, comment out the order by statement. Execution now takes much less than 10 seconds? Probably an issue with sorting.

Step 2: Troubleshooting in Semarchy

1. Under Preferences → Data Stewardship, uncheck Predictable Pagination. 
2. Open the BOV. Under MoreOpen the BV in the workbench. Under "Collection Configuration", look at Customized Sort. Are there any attributes sorted? If yes, remove all attributes. This should increase speed of application . The more attributes sorted, the longer the BOV will take because Oracle is taking a very long time to sort the attributes when the BOV opens.No attributes under customized sort? What about Customized Sort defined in the Workbench under the BOV configuration? If Customized Sort is turned on and there's a sort expression, turn off Customized Sort and performance should be improved. because the BV will now rely on default sorting based on the ID. This will normally be faster because it is indexed.
   Alternative: It's possible to manually add an index in the database schema. If your users have an important business need for the Customized Sort, then keep the sort. And add an index to provide good performance for this sort.
3. Open the BV in the workbench. Under "Collection Configuration", look at "User-Defined Sort". Is user-defined sorting allowed? If so, it's possible for users to set sorts that may perform poorly. 
   Option 1: disallow user-defined sorts.
   Option 2: educate users that default sorting is fast, but user-defined sorting may be slow. Use it only on filtered data.
4. If there's still bad performance or even a Null Pointer Exception (NPE) when opening BOVsBVs, sort preferences might have a problembeen corrupted. Go to Preferences, Under Filters restore defaults. Under Sort Criteria, also restore to defaults. Getting rid of Sort and sort User Profile → Settings. Use the 'clear' option to remove "Selected columns, sort order and type of view used" Getting rid of these saved preferences usually solves the problem. What can happen is a user who is waiting a long time for a BOV to open can get impatient and click the refresh button on the browser. This causes the preferences to get in a weird place and resetting them should fix any exceptions.
5. (Legacy 3.x reference) Under Preferences → Data Stewardship, uncheck Predictable Pagination. 

Dupes Mgmt Workflow takes a long time 

Step 1: Troubleshooting in Semarchy

1. Check there is no Customized SortUnder display settings, uncheck Autofit columns, Colorize consolidated Master values, Colors Golden IDs.

2. Best practice: Should not check out more than 1K records in basket for dupes mgmt. Tweak match rules to do autoconfirm records if a data steward is checking out more than 1K records to blindly confirm them.

3. Good idea to remove records from basket. Don’t just cancel out of workflows. Actually Select All and hit Delete. If you only cancel out of workflows and do not actually delete the records, you end up having huge large amounts of data in UG and UM tables. Probably hurts performance. Can write a script to prune the UG and UM tables.

...

1. This potentially hurts performance.

2. (Legacy 3.x reference) Under display settings, uncheck Autofit columns, Colorize consolidated Master values, Colors Golden IDs.

Stepper or workflow takes a long time to finish processing integration job

Probably You may have Calculate statistics turned on. Check out this Confluence article for details.