Can you give more details about the MV being refreshed and how much time individually it takes for refresh each of it. Also would like to see if they are dependent on each other or can be refreshed parallelly.
Sounds like there is significant data in the base tables, the MV refresh query is complex pulling data from multiple tables and thus taking long to refresh. Is that correct ?
If the underlying refresh query was simple , ie: selecting data from just one table , u could have done incremental refresh using MV snapshot logs. But I doubt yours would be that case.
What is the version of the database ? Try comparing the MV refresh to just pushing data into an empty table by selecting from the base query. I am aware that there is a bug listed on Oracle support related to MV refresh taking significantly long to complete.
If nothing else, U may have to just refresh data into tables by refreshing data based on data stamp column thus reducing the refresh time .
All those MV's are created with parallel clause but this will not help the refresh process.
Parallel clause used in the create statement of a materialized view is considered only during the materialized view creation and it is ignored during the refresh process.
Also we are using the PARALLELISM keyword in dbms_mview.refresh procedure will not refresh the materialized view in parallel.
We might need to add a parallel hint directly in the materialized view definition in order to expedite the refresh process.
Also as per some notes we need to consider the below parameters from the db before applying the parallel mechanism also the server availability and I/O Memory.
Please let me know your thoughts on this.