Solved

Light Cleanup very slow when Materialized View refresh is running - how to fix it?

  • 7 June 2023
  • 2 replies
  • 90 views
Maja Stanislawska
Rank
Userlevel 2
Badge +5

Hi,

IFS developed a workaround for us to improve performance to display large amounts of data by using Materialized View (MV). The MV refresh job runs every hour to keep the data reasonably fresh for users.

The Light Cleanup job should run every 10 min. It normally takes around 3-4 seconds but when the MV refresh background job is running, the Light Cleanup is taking about 30 min. - a major change. Do we have someone here who could explain why the Light Cleanup execution would increase so dramatically? What can we do to mitigate it?


Sometimes, the MV refresh also gets killed by the Light Cleanup. A similar case is in this useful thread:


For clarity, we have Managed Cloud Services with Apps 10, so not much access to the DB. Our case with Platinum Support is not moving fast enough and consulting is prioritizing other topics at the moment, hence my question in the Community.

icon

Best answer by Maja Stanislawska 7 June 2023, 15:23

View original

2 replies

Michael Kaiser
Rank
Userlevel 4
Badge +7

Hello Maja,

I’m not shure but guess the following.
Light Cleanup job should run every 10 min  = second job. It normally takes around 3-4 seconds.

MV refresh background job every 60 min = first job.

How long does it take?
 

The moment your second job tries to read data from the MW, ORACLE need a “finished” MV.
If the creation is still in process this could be a dead lock situation on database level.

I suggest you talk to an ORACLE admin.
If not possible try this workaround.
Do not schedule the second job every 10 Minutes but every 15  Minutes and start 5 minutes before the first job is scheduled. So at least it got 9 Minutes to finish.

By the way: what kind of data you are using for what kind of purpose?
(“improve performance to display large amounts of data by using Materialized View (MV):”)

All the best

Michael

PS:
If you dig deeper into IFS you will find uge amounts of APIs creating data “on the flight”.
For keeping an IFS mask filled with customer data, customer order, shop order, etc. absolutely fine.
For getting a large amount of data … hmmm.

So IFS created the workaround MV I would like to ask
what kind of SELECT command you are using and what APIs are within this Select.

We learned the hard way that some APIs are really performance killers, some others not bad at all.

 

Know your goal, ask relevant questions, decide strong, fast and smart.
Maja Stanislawska
Rank
Userlevel 2
Badge +5

Hi Michael,

thank you for taking the time to reply and sharing good tips.

The MV refresh is a long job and it will keep growing in time because IFS implemented it such that it refreshes all rows, not working incrementally. Currently, the MV refresh takes roughly 44 min. to finish.
Both jobs run in two separate queues (we moved them out from the Default Queue), so they are not blocking each other. However, there is some possibility they may be in a deadlock from time to time (we have no proof of it currently, but will check with IFS Cloud).

Looking at your suggestion, I assume you are aiming at preventing the jobs from running concurrently. One way to go about it might be to schedule the Light Cleanup once per hour. With many thousands of users of the ERP, I would be hesitant to decrease the frequency too much, but of course it is one way to check - thank you for the idea.

I do not have details of the code delivered - this is all in the hands of IFS. The data set was apparently large and the system did not cope well with calculations in several columns on the flight, so MV was the best option to take. I would like to see it improved, so that the refresh is done incrementally.

Do we have any other experiences in the Community? Perhaps we are not the only ones with similar issues.
 

Reply


Terms & ConditionsCookie settings