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.
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.
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.
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 .
We can't do FAST Refresh because
- The materialized view must not contain references to non-repeating expressions like SYSDATE.
- It cannot contain a SELECT list sub query.
- 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.