If this customer have a lot of finished Data Capture Sessions it might be good to remove them by activating the Task Remove Data Capture Sessions, see example here 

Maybe that can help the performance when calling the license method since it checks for active sessions.

Hi Dario

Schedule is activated and range is set to max 5 days. Table data_capture_session_line_tab contains ~220 000 rows.

Here is the problem with time consuming:

Plsqlap_Server_API.Invoke_Record_Impersonate('BrowseLicenseFile',
                                               'GetCustomLicense',
                                               record_);

Best Regards

I cannot say that I have heard about issues like this before. 

The methods you mention are connected to the License framework that we are using in wadaco.
They have been prone to be sensitive to issues with extended server or PL/SQL Access provider (see link below about license issues).

If the license check hangs it almost sounds like a table lock on one of the session tables. There have been some rare cases of table locks on the session tables but they have been very hard to re-create. I have some vague memory of lock issues connected to Windows Mobile framework that caused issues in wadaco, but that was a long time ago, it should have been fixed if you are on IFS9 UPD17.

If its Apps9 I'm not sure if it can have any connection to the Oracle RAC issues for Apps10 that is mentioned in these 2 links before:

https://community.ifs.com/technology-infrastructure-cloud-integration-dev-tools-50/wadaco-not-failing-over-even-though-ifs-does-with-oracle-rac-4654

https://community.ifs.com/products-manufacturing-products-engineering-wadaco-40/warehouse-data-collection-rac-time-sync-6463

Then we have this collection of tips for all licenses are in use issues:

https://community.ifs.com/ifs-supply-chain-employees-partners-only-105/wadaco-all-warehouse-data-collection-licenses-are-in-use-error-7172

I'm guessing this customer is not having several users with the same oracle user trying to access wadaco at the same time?  Not sure if that would cause a table lock, but it could be possible maybe.

How many licenses do they have and how many users are running wadaco when this happens? 
Are they perhaps using the Prioritized Users functionality? 
Having too many prioritized users might cause problems since they will park other users sessions all the time.

Thank You for You reply.
Customer has configured Active Directory authentication and http aliassing without RAC.
I made test with SOA Cleaner Express of web services /fndext/soapgateway?handler=DataCaptureManagement and it works ok for termininal user.
But when I use ifsapp account very often first request takes about 30s-1min. One difference is that user has no domain account.

Do You know how can I debug Wadaco terminals? Or is any unofficial CAB file with extended debug logs?

Probably this is not important but time to time following errors occur:
2021-03-23 13:01:16,958 509 ERROR [Integration] ifs.application.messageprocessor.impl.senders.ConnectSenderManager.getWorkResult(): Re-throw Exception from run() [Error GUID:a0577317-b901-45eb-9da4-69204130a44d]
ifs.fnd.integration.senders.ConnectSender$TemporaryFailureException: Exception while sending data
    at ifs.application.messageprocessor.impl.senders.HttpConnectSender.nativeSend(HttpConnectSender.java:129)
    at ifs.application.messageprocessor.impl.senders.ConnectSenderManager.run(ConnectSenderManager.java:215)
    at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:209)
    at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
    at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
    at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
    at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
    at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:652)
    at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
    at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
Caused by: APPLICATION ERROR
    at ifs.fnd.tc.http.HTTPClient.doPost(HTTPClient.java:533)
    at ifs.fnd.tc.http.HTTPClient.doPost(HTTPClient.java:403)
    at ifs.application.messageprocessor.impl.senders.HttpConnectSender.nativeSend(HttpConnectSender.java:114)
    ... 9 more
2021-03-23 13:01:16,958 509 ERROR rIntegration] ifs.fnd.sf.j2ee.EjbMethodInvoker.invokeMethod(): Caught InvocationTargetException while calling 'invokeBizapi' gError GUID:14b0427b-4e93-4c5c-9e0e-aecebc197817]
java.lang.reflect.InvocationTargetException
    at sun.reflect.GeneratedMethodAccessor418.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at ifs.fnd.sf.j2ee.EjbMethodInvoker.invokeMethod(EjbMethodInvoker.java:825)
    at ifs.fnd.sf.j2ee.EjbMethodInvoker.invoke(EjbMethodInvoker.java:374)
    at ifs.fnd.sf.j2ee.EjbMethodInvoker.invoke(EjbMethodInvoker.java:349)
    at ifs.fnd.gateway.soap.MethodInvoker.invoke(MethodInvoker.java:269)
    at ifs.fnd.gateway.soap.MethodInvoker.invokeActivity(MethodInvoker.java:224)
    at ifs.fnd.gateway.soap.IfsSOAPGateway.performDoPost(IfsSOAPGateway.java:283)
    at ifs.fnd.gateway.soap.IfsSOAPGateway.doPostImpl(IfsSOAPGateway.java:192)
    at ifs.fnd.gateway.IfsAbstractGateway.doPost(IfsAbstractGateway.java:453)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
    at ifs.fnd.gateway.IfsAbstractGateway.service(IfsAbstractGateway.java:161)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

Adding this link here also since it might be connected a bit.

Hi Zani

PLSQL access provider is green. HTTP request works also fine. I made few times below plsql test and it takes about 5-7s. There is no any hangs.

declare
  v_num_ number;
begin
  dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss'));
  for i in 1 .. 100 loop
    SELECT Plsqlap_Server_API.Ping_Result__ into v_num_ FROM sys.dual;
  end loop;
  dbms_output.put_line(to_char(sysdate, 'hh24:mi:ss'));
end;
 

I have no idea what should I check next

Hi again,

Ok. So the main problem is still connected to the license check according to your findings? There are no table locks in the session tables when this happens or anything like that? I would guess that table locks on the session table could affect the license check since it checks for number of active sessions to compare that to number of licenses the customer have.

There is no other more generic or specific performance issues you or the customer have noticed?  Like some LOV is slow to open or some process execution takes a very long time.

Thanks for the findings in Get_Latest_Data_Item_Settings by the way, small changes like that always helps.

Does this customer have any customer adapted/created processes? The only similar case I have heard was with a customer that had a couple of processes not made by us, that did a lot extra stuff during the execution phase of the process that could sometimes cause time outs in the mobile clients, during peak hours during a couple of peak days every year. So the only thing that could be done there is change how does processes work and try and trim the execution to be faster or they have to run the execution as a background job instead (its a feature introduced in Apps10 Update8 or something like that).

When did this problem start, already since the first installation or did it work fine in the beginning and have been a gradual problem that appears more and more often? Or did it appear after some specific upgrade/patch?

The thing with 4 mln records in session line table feels a bit too much, have they tried to run the clean up job more often?

How many users/licenses are we talking about here for this customer?

Are they using the prioritized user functionality? Since that is triggered during the license check and parks users that are not on that prio-list, so maybe that could add some extra time to the license check that could explain while its only some checks that takes extra long time.

One more question, what is the latest Session ID in Data_Capture_Session  for this customer?

I remember that there were a customer a while back having issues with sessions getting parked, that they solved themselves by restarting the DATA_CAPTURE_SESSION_ID_SEQ sequence, they were up to 17 million in their sessions ids before they reset the sequence. They were running windows mobile so there might be an issue with that client and high Session ID numbers, the sequence itself is setup to to support a lot more than that (19 digits at the moment). 

Hi Dario Zani

I prepared below procedure as o copy of license check using by wadaco.
Then I noticed that when I open new test window in PLSQL Developer first every launch of anonymous block on IFSAPP account BEGIN Get_Custom_License_Info(); END;
takes about 20-60 seconds. It waits on:
  Plsqlap_Server_API.Invoke_Record_Impersonate('BrowseLicenseFile', 'GetCustomLicense', record_);

My conclusion is that there are no any table lock in session tables.
Customer is using AD authentication so maybe here is the problem. As I know Invoke_Record_Impersonate procedure is using IFSPLSQLAP user for authentication. I have got confirmation that this user is not used in AD confirmation.
I heard beside this application firstly tring find this user on LDAP server and this could take much time.

Max capture_session_id = 4 630 831

CREATE OR REPLACE PROCEDURE Get_Custom_License_Info IS
  license_name_     VARCHAR2(200);
  number_of_users_  NUMBER;
  usage_view_       VARCHAR2(200) := 'ACTIVE_DATA_CAPTURE_SESSIONS';
  record_ Plsqlap_Record_API.type_record_;
BEGIN
  dbms_output.put_line('1. '||to_char(sysdate, 'hh24:mi:ss'));
  record_ := Plsqlap_Record_API.New_record('CUSTOM_LICENSE');
--
  dbms_output.put_line('2. '||to_char(sysdate, 'hh24:mi:ss'));
  Plsqlap_Record_API.Set_Value(record_, 'VIEW',  usage_view_, Plsqlap_Record_API.dt_Text_Key, FALSE);
--
  dbms_output.put_line('3. '||to_char(sysdate, 'hh24:mi:ss')||' - '||record_.Name_||' - '||record_.buffer_);
  Plsqlap_Server_API.Invoke_Record_Impersonate('BrowseLicenseFile', 'GetCustomLicense', record_);
--
  dbms_output.put_line('4. '||to_char(sysdate, 'hh24:mi:ss'));
  license_name_    := Plsqlap_Record_API.Get_Value(record_, 'NAME');
  dbms_output.put_line('5. '||to_char(sysdate, 'hh24:mi:ss')||' - '||license_name_);
--  
  number_of_users_ := Plsqlap_Record_API.Get_Value(record_, 'NUMBER_OF_USERS');
  dbms_output.put_line('6. '||to_char(sysdate, 'hh24:mi:ss')||' - '||number_of_users_);
END Get_Custom_License_Info;

Best Regards
Piotr

Hi Piotr,

Ok. Maybe you can tag this thread with some tags on what you suspect is the problem so more technical people can assist, since it feels like we are stepping outside the wadaco area now.

Regards,

Dario