How to investigate a performance issue in List of Values (LOV) where an error appears as “It took too long time to retrieve list of values, please use Find” when searching a value in a List of Values Field.

ISSUE/QUESTION

How to investigate a performance issue in List of Values (LOV) where an error appears as “It took too long time to retrieve list of values, please use Find” when searching a value in a List of Values Field.

ENVIRONMENT: IFS CLOUD

AUDIENCE: Internal

RESOLUTION/ANSWER:

The database query that is causing this performance issue must be identified first. For this, the Oracle Automatic Workload Repository (AWR) report is utilized, which is beneficial in these kinds of scenarios since it provides all the required information when executing a specific functional flow. Additionally, the problematic database query can be identified as well.

1.  Analyzing the AWR Report

Following that, the AWR Report that need to be examined in order to identify the query that took a maximum time to perform. That information is contained in the "SQL Statistics Section”. 

Given below is an example query:

Both core and customer codebases are compared to check whether there are any existing fixes available. Comparison of the above query is provided below:

 Customer environment

SELECT * FROM (SELECT a.*FROM (SELECT company, party_type, identity, party_name, city, address FROM identity_pay_info_lov) a) WHERE ((((company = 'X') AND (party_type = 'XXXX')) AND ((identity LIKE xxxxxx') OR ((LOWER (party_name)) LIKE 'xxxxxx'))));

CORE environment

SELECT * FROM (SELECT a.*FROM (SELECT company, party_type, identity, party_name, city, address FROM identity_pay_info_lov) a) WHERE ((((company = 'y') AND (party_type = 'YYYYY')) AND ((identity LIKE 'yyyyyy') OR ((LOWER (party_name)) LIKE 'yyyyyy'))));

​

2. Analyzing the Execution Time

Analyze how much time it takes to execute these queries in the Customer and Core environments. The query was executed in 49 seconds in the customer environment, but only in 0.301 seconds in the core environment.

Check the number of records in each environments.

• CORE environment = Only 1029 records.
• Customer environment = 1,024,899 records.

(It is obvious that this performance drop is due to the large amount of data in the customer’s environment. It is approximately thousand times than the Core environments data records.)

3. Finding the Database Tables which related to the erroneous query

For example, considering the same example: 

The related Tables are given below.

• IDENTITY_PAY_INFO_TAB
• CUSTOMER_INFO_ADDRESS_TAB
• CUSTOMER_INFO_ADDRESS_TYPE_TAB
• COMPANY_FINANCE_TAB
• USER_FINANCE_TAB

All indexes were similar in above tables are same when comparing with Core environments.

Furthermore, there were no apparent changes comparing the views User_Finance_Auth_Pub and Identity_Pay_Info_Lov.

4. Gathering Statistics

By simply following a few simple steps, you may obtain some statistics for this stage utilizing the IFS Application's Gather Statistics. If we run the Oracle Gather Statistics procedure to the corresponding tables, it would update the metadata accordingly. This should be something mandatory to be carried out across the database to all tables as it would improve the performance. The ideal time interval would be bi-weekly or monthly.

For example, open Customer’s IFS Application and navigate to “Oracle Objects Window”. And search the table IDENTITY_PAY_INFO_TAB as the “Object name”.

As you can see, the "Last Analyzed date" has been changed to a recent date, indicating that the Metadata for this table has recently been updated. You should see each table whether it has been analyzed within a recent date. In this example all the tables are recently analyzed. Furthermore, if the Gather Statistics job is not scheduled, it should be initiated as well.

Go to Database Tasks à Search Name “Gather Statistics”. And there is a scheduled task for that. If you need further details about this task, you can go to Details. If not, remind the customer that in order to improve performance, they must schedule the database task to gather statistics and update the metadata in a monthly/bi-weekly interval.

Finally, a conclusion may be drawn utilizing this case and the aforementioned information. Therefore, this problem can be submitted to the corresponding R&D Product such that a correction can be implemented.

If there are any changes, in addition to these aspects, further investigations are required.

CAUSE: The Performance delay is occurred due to the large amount of data (over 1 million) present in the customer’s environment compared to the core.

ADDITIONAL INFORMATION: N/A

SCRIPTS/LOGS: N/A

SOURCE: Case CS0142756

VERIFIED: YES