|MView Complete Refresh Performance Issue [message #321749]
||Wed, 21 May 2008 04:19
Registered: July 2007
We are migrating database from Oracle 18.104.22.168 32-bit to Oracle 10.2.0.1 64-bit. Most of the things go well except MView Refresh. Currently MView complete refresh is taking the same time to complete in both Old and New environment and it is not acceptable. I am not sure how to improve the performance for MView Complete Refresh.
There are thress MView Refresh MV1, MV2 and MV3 and here are the tables and records for each MView.
MV1 ->two tables: t1(11.5M records) and t2(1.5K recrods)
MV2 ->three tables: t1(40M records), t2(1.5K records), t3(4K records)
MV3 ->three tables: t1(11.5M records), t2(35M records), t3(1.5K records)
Table Join Conditions
Equal Join with Grouping
Full Table Scan, Hash Join and Sort Group By
MView Creation Timing
MV1 - 2 min
MV2 - 26 min
MV3 - 12 min
MView Refresh Timing
MV1 - 9 min
MV2 - 1.5 Hr
MV3 - 22 min
I tried with parallel option in DBMS_MVIEW.REFRESH(Parallel 4) because the server it 2 x Quard Core CPU. But that didn't make any difference. The most time taken in refresh is sorting using Temp Tablespace. PGA_AGGREGATE_TARGET is set to 1G but each session cannot use more than 200M. I tried to create MView with WORKAREA_SIZE_POLICY=AUTO and set SORT_AREA=1G and HASH_AREA=500M by using alter session command. When the script was executing, I couldn't even login using the same user.
I would like to know
1. how to improve the performance instead of recreating MView.
2. Currently the server has 16G physical memory. When I tried to set SORT_AREA more than 1G, I received error message. Does it mean that I can't set SORT_AREA to more than 1G?
Any suggestion will be appreciated.