My manager at work asks me to create 50 Materialized views and set the refresh every 15 minutes, my question is: will refreshing all 50 MVs at the same time every 15 minutes affect DB performance?
thank you
3
- Yes, it will affect performance, possibly in a negative way. But querying your materialized views could also improve performance by making it easier to get that data out of your database.Tim Biegeleisen– Tim Biegeleisen2020-10-25 06:04:17 +00:00Commented Oct 25, 2020 at 6:04
- thank you @TimBiegeleisen for your helpful comment.S59– S592020-10-25 06:14:25 +00:00Commented Oct 25, 2020 at 6:14
- Is it necessary to refresh them all at the same time? Maybe some of them could be refreshed once a day, some others twice a day, the rest every hour, and only a few even more often. It depends on what they contain. For example, data about employees change quite rare. "Orders" table changes many times in a minute. Etc. Maybe it is worth discussing with the manager.Littlefoot– Littlefoot2020-10-25 09:36:34 +00:00Commented Oct 25, 2020 at 9:36
Add a comment |
1 Answer
Refreshing 50 at the same time is likely to stress some part of your system, generally you only make materialized views because the work required to execute the query is longer than a user would otherwise be happy waiting.
If you are using fast refreshable materialized views (which you should be striving for if you want to refresh that frequently) the amount of work required is far far less - you only need to work with the data that has changed since the last refresh. Oracle will calculate everything that requires changing and change it in a small number of SQL statements. For a 15 minute change window, there's probably not a lot of actual changes that get made - and these changes will be processed using batch operations much more efficiently.
However... even when you have made the refresh process as fast as possible, having 50 things all working at once (even for a short period) is still a lot. If they all need to do CPU work then that's 50 busy CPUs that would need to be scheduled by the OS, and then it also needs to schedule in all the remaining user sessions that are trying to do work (Oracle CPU licencing for this would be super expensive). This is why you would typically limit the number of jobs that can run at any one time using the job_queue_processes parameter (although recent versions will also help out by having Resource Manager restrict number of jobs at any one time) - set it to a sensible number depending on how much work you can physically be processing in the background at once. If you use fast refreshable materialized views then refreshing 50 should take no time even if Oracle processes them one at a time.
Another benefit of having fast refreshable materialized views (if you have upgraded to at least 12.2) is that you can have Real Time Materialized Views, this means even if it hasn't been refreshed, your users can still see completely up to date data all the time without having to do the work of the query - very clever stuff! This could allow you to have less frequent refreshes for some of your materialized views but have the data completely in sync at all times.
