5 Replies Latest reply on Jul 24, 2013 1:30 PM by 969952

    materialized views performance issue



      We have existed materialized views which will run at defined scheduled time. Here the concern is the performance of the MVs  are very poor. All Mvs are defined with Completed Refresh Method ( it's having complex joins and Complex queries).

      Eg  : Create Materialized View MV1

                refresh completed  with rowid

                  as ..

                      select <col1>, sum<col2>....

                        inner  join < table name>

                  union all

                       select .... from view1,table

                            union all

                             select * from view2,mv2




      like this all mvs are defined. It consumes the running time of the MV. we tried with below methods to increase the performance.But we didn't find any improvement.

                   1. Partitions  2. parallelism to reduce the CPU cost 3. gather stats


      now we are planning to apply optimization hints. but which hints are helpful for this case. Can any one please help me out what all the other methods we can follow to increase the performance.



        • 1. Re: materialized views performance issue

          Performance poor means - they are taking longer than expected to refresh? A great lot of details is required to suggest improvement in performance. Posting the view script would be a first step.



          • 2. Re: materialized views performance issue


            Here I have some materialized views and those are defined with COMPLETE refresh method. These MV are created on current meterings which will calculate hourly,daily,weekly,monthly,yearly. these are having data from 2002. here my query is if they calculating does it refreshing from 2002 data? if yes I hope there is no advantage. so here I am planning to I would like to check from which year and date the data is getting refreshing. SO my queries are   1. What is the the start_date of the refreshing year 2. If it is refreshing from start_date=2002 i would like to create a archive folder and move the non-refreshing data to that archieve folder and start refreshing the data from recent data.


            In Mvs how would I get the start_date of the refreshing MV?


            Please let me know.



            • 3. Re: materialized views performance issue


              I have some existed materialized views which will refresh with COMPLETE methodology. It's not possible to change from Complete to FAST refresh because all Mviews are created on Complex joins and views and multiple set operators. because of this Performance of the MV's are too low.So we tried to increase the performance in the following methods.

              1. Reduced the cost of CPU from 24.. to 2(around) 2.  By using Parallelism method we reduced the cost 3. Created indexes and dropped some unnecessary indexes. 3. Used gather stats

              Now we are planning to use hints and partitions. but after reducing the CPU cost also we are getting " Out of Process Memory" we are using 32 bit system. is it required to increase the memory to 64 bit?

              or we can increase the performance on 32 bit by using any of the methodologies?


              Please suggest me , if it is fine to use Hints , what are the Hints would be helpful and why?

              If partitions which partition and why? Please suggest me.



              • 4. Re: materialized views performance issue

                Why do you think FAST refresh would not help ? . Fast refresh means it will look for updated records and get them. Complete would get everything . So if you data set which is getting updated is small - FAST might help you .

                • 5. Re: materialized views performance issue

                  We can't do FAST Refresh because

                  1. The materialized view must not contain references to non-repeating expressions like SYSDATE.
                  2. It cannot contain a SELECT list sub query.
                  3. Nested materialized views must have a join or aggregate

                  but in my case all MV's having the above 3 conditions. So we can't do Fast refresh.