Forum Stats

  • 3,734,283 Users
  • 2,246,938 Discussions
  • 7,857,218 Comments

Discussions

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

User_7EQU5
User_7EQU5 Member Posts: 4 Green Ribbon
edited Jan 20, 2021 11:52AM in General Database Discussions

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?

Tagged:

Best Answers

  • User_7EQU5
    User_7EQU5 Member Posts: 4 Green Ribbon
    Accepted Answer

    Thank you again for the help.


    I have found in the trace, that the procedure "dbms_irefstats.run_sa" and DML operations on tables like wri$_adv _parameters were an issue.


    Apparently this is the segment advisor kicking in during the refresh.

    The support article Mview refresh creates Global Tempporary table names starting with WRI$_ADV_ (Doc ID 2502544.1)

    helped me with this.

    After applying the suggested solution the procedure is gone from the Top Activity and everything works fine again.

Answers

  • User_7EQU5
    User_7EQU5 Member Posts: 4 Green Ribbon

    Thank you for your response.


    I made sure that only one entry exists for collection_level and retention_period in DBA_MVREF_STATS_SYS_DEFAULTS.

    I have also set the default value for collection_level to NONE.


    After restarting the DB and refreshing the materialized view, the first run was very fast.

    However, already at the second run, sys.dbms_irefstats.run_sa was back in the top sql activity and the refresh is becoming slower again with every refresh.


    Is there any more information about sys.dbms_irefstats.run_sa or any other hints?

  • User_7EQU5
    User_7EQU5 Member Posts: 4 Green Ribbon

    After trying TYPICAL again, it looks like my problem is, that I have a very high LOG_PURGE_TIME in SYS.MVREF$_STATS

    The LOG_PURGE_TIME is increasing with every run.


    Is there a way to keep this more consistent?

  • User_7EQU5
    User_7EQU5 Member Posts: 4 Green Ribbon
    Accepted Answer

    Thank you again for the help.


    I have found in the trace, that the procedure "dbms_irefstats.run_sa" and DML operations on tables like wri$_adv _parameters were an issue.


    Apparently this is the segment advisor kicking in during the refresh.

    The support article Mview refresh creates Global Tempporary table names starting with WRI$_ADV_ (Doc ID 2502544.1)

    helped me with this.

    After applying the suggested solution the procedure is gone from the Top Activity and everything works fine again.

Sign In or Register to comment.