WorkflowObjectQuery taking to long time to sync

@lopespetro we got an edit applied to the default where clause in the underlying view to speed up this query. We added a clause that only the surveys that were tied to the objects on work orders assigned to techs would sync.

Hi, @PIINKADAM . I did this clause and seens to get a better sync performance. I’m still evaluating to close this topic.

In my case, the eforms are linked just with the objects.

&0.DATE_CREATED >= SYSDATE

Br.

Lopes

Did you add the clause in the permission set filter or the underlying view?

A clause in the permission set filter.

Hi All.

Could someone explain this logic?

This entity has a count of just 796 record mobile sync data but it taks to long to sync.

Some times it takes more than 1 million ms.

How can I solve this? 

@kathlk , @PIINKADAM , @Bandula . @James Ashmore 

Hi

Data synchronization performance issues are not always due to larger data sets; they could also be caused by the Oracle execution time required to retrieve the data.

First, enable sync trace for this user to identify the exact SQL query. Typically, mobile sync SQL joins with the mobile sync data table. Once you have the SQL query, run it in an SQL tool to obtain the explain plan and proceed to optimize it. In most cases, the root cause of the performance issue is because of slow oracle execution plan sue to the invalid Row Key index being used in the underlying view.

Hi @kathlk , tks for the info.

I’ve talked to local support and they are checking it according to your info.

Let’s see what we can do to solve this.

Hi @kathlk , some info to let you know about this situation.

After some analisys with DBA and local technical support. We got this info about this entity.

In session ID, as the user initializes the app, this entity runs this query and it takes a long time quering.

Do you know something about it? or any idea how to solve it?

The technician is going to continue the investigation tomorrow and try to find a solution.

SELECT a1.OBJID, a1.OBJVERSION, a1.CONFIGURATION_SEQ, a1.SURVEY_ID, a1.MCH_CODE, a1.CONTRACT, a1.ITEM_CLASS_ID, a1.OBJKEY, F1_MODIFIED_COLUMNS F1_MODIFIED_COLUMNS
FROM (SELECT a.*, msd.device_id f1_device_id, msd.sync_state_db f1_sync_state, msd.modified_columns f1_modified_columns, msd.processing f1_processing FROM (SELECT a1.* FROM IFSPET.WORKFLOW_OBJECT_SURVEYS a1 WHERE 1=1 ) a, IFSPET.mobile_sync_data msd  WHERE a.objkey=msd.referenced_objkey  AND   'WorkflowObjectQuery'= msd.entity  AND 'MaintEngApp'=msd.app_name  AND IFSPET.Fnd_Session_API.Get_Fnd_User=msd.user_id) a1
WHERE F1_DEVICE_ID = :1

Hi Lopes,

Yes this could be an issue in that view used by the application. And also this has been addressed in IFS Cloud and might have a fix for the view. I think it could be faster if you report this to IFS support and they can help on this as this is App10. 

Thanks

OK, @kathlk . Ths for your attention. Let’s see if the local support will find a solution. I let you know.

Hi @kathlk . Do you have any news about this issue?

We did a test with a truncate table in mobile_sync_data_tab and tried to sync again but the behaviour is the same.

Hi 

The most likely issue could be the underlying view definition, where a concatenated objkey might cause Oracle to bypass the ROWKEY index. So please discuss with IFS Support.

kathlk wrote:

Hi 

The most likely issue could be the underlying view definition, where a concatenated objkey might cause Oracle to bypass the ROWKEY index. So please discuss with IFS Support.

 

OK @kathlk . Tks for this info. I’ve already sento to local support for investigation.

One thing they did last night and improved this entity sync was the recreation of the mobile_sync_data_tab .

I’m doing new tests and the entity has been improved a lot.

From 5 milion to 6k.

My worry is as we apply in production, the problem may come back as all the users sync again.

Let’s see the local support feedback. I let you know.

@lopespetro  was this done as a mod or a product fix?

Hi @PIINKADAM . a mod. I have escaleted the case but I could not wait because the problem was too critical for us. Still waiting their answer about a product solution.