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
select <col1>, sum<col2>....
inner join < table name>
select .... from view1,table
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.
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.
We can't do FAST Refresh because
but in my case all MV's having the above 3 conditions. So we can't do Fast refresh.