3 Replies Latest reply on Nov 3, 2015 7:08 AM by Midhun George

    Materialized View Refresh

    Midhun George

      Hi All,

       

      I have a requirement to reduce the refresh time of MV.

       

      The Concurrent Program calling five MV refresh which is taking almost 4 hours to complete.All these MV's are created by partition and using complete refresh mechanism.

       

      Is there any way to refresh the data which is created after some specific date in the master table?

       

      Regards,

       

      Midhun

        • 1. Re: Materialized View Refresh
          Pravin Takpire

          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.

          regards

          Pravin

          • 2. Re: Materialized View Refresh
            GUGGI

            hi,

             

            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 .

             

            Amit

            • 3. Re: Materialized View Refresh
              Midhun George

              Hi All,

               

              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.

               

              PARALLEL_MAX_SERVERS

              PARALLEL_EXECUTION_MESSAGE_SIZE

              PARALLEL_ADAPTIVE_MULTI_USER

              PARALLEL_AUTOMATIC_TUNING

               

               

              Please let me know your thoughts on this.

               

              Regards,

               

              Midhun