@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.

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.

Hi @kathlk, just to let you know, the local support created a temp table to store the orginal query and changed the view to get the info there with a daily background job and on demand fetch in case of need. This has solved the the sync problem for now.

As we are moving to IFS cloud, let’s see if RnD has some solution for this issue.

the view now is like this.

CREATE OR REPLACE VIEW IFSPET.WORKFLOW_OBJECT_SURVEYS AS
SELECT survey_id survey_id,
       configuration_seq configuration_seq,
       contract contract,
       mch_code mch_code,
       item_class_id item_class_id,
       objkey objkey,
       objversion objversion,
       objid objid
FROM c_workflow_object_survey
WHERE user_id = Fnd_Session_API.Get_Fnd_User
WITH   READ ONLY;
comment on table IFSPET.WORKFLOW_OBJECT_SURVEYS is 'LU=CWorkflowObjectSurvey^PROMPT=Workflow Object Surveys^MODULE=WOCFG^';
comment on column IFSPET.WORKFLOW_OBJECT_SURVEYS.SURVEY_ID is 'FLAGS=PMI-L^DATATYPE=STRING(20)^PROMPT=Survey Id^REF=Survey/NOCHECK^';
comment on column IFSPET.WORKFLOW_OBJECT_SURVEYS.CONFIGURATION_SEQ is 'FLAGS=PMI-L^DATATYPE=NUMBER^PROMPT=Configuration Seq^REF=WorkflowCfgFilter/NOCHECK^';
comment on column IFSPET.WORKFLOW_OBJECT_SURVEYS.CONTRACT is 'FLAGS=PMI-L^DATATYPE=STRING(20)^PROMPT=Contract^REF=Site/NOCHECK^';
comment on column IFSPET.WORKFLOW_OBJECT_SURVEYS.MCH_CODE is 'FLAGS=PMI-L^DATATYPE=STRING(100)^PROMPT=Mch Code^REF=EquipmentObject(contract)/NOCHECK^';
comment on column IFSPET.WORKFLOW_OBJECT_SURVEYS.ITEM_CLASS_ID is 'FLAGS=A-IUL^DATATYPE=STRING(10)^PROMPT=Item Class Id^REF=ItemClass/NOCHECK^';
 

@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.