Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why is the refresh time for my materialized view increasing with every refresh?

Daniel JackschikJan 20 2021 — edited Jan 20 2021

After upgrading to 19c, I'm currently facing a problem with refreshing materialized views.
I dropped the materialized view and all the corresponsing materialized view logs, and created them from scratch in the 19c database.
With each execution of DBMS_MVIEW.REFRESH the fast refresh takes a little bit longer. The first few executions can be finished in less than 1 minute, and after a few more executions it already takes about 15 minutes.
At some point, the refresh time goes back to less than 1 minute, then it starts to increase again with every refresh.

In the sql trace there is a procedure called "sys.dbms_irefstats.run_sa" which seems to consume a lot of time.
I haven't seen this procedure being called in our test environment, in which the refresh seems to work fine.

What is the purpose of this procedure and could it be the reason for the performance problems?

This post has been answered by Daniel Jackschik on Jan 22 2021
Jump to Answer
Comments
Post Details
Added on Jan 20 2021
5 comments
2,651 views