| MView Complete Refresh Performance Issue [message #321749] |
Wed, 21 May 2008 04:19  |
sawcna
Messages: 4
Registered: July 2007
|
Junior Member |
|
|
Hi,
We are migrating database from Oracle 9.2.0.8 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
Explain Plan
------------
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.
Regards,
Chan
|
|
|
|
| Re: MView Complete Refresh Performance Issue [message #321789 is a reply to message #321749] |
Wed, 21 May 2008 05:53  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Are you performing an ATOMIC refresh? If so, that would delete the rows rather than truncating.
Your next step is to run a SQL*Trace and analyse the trace file with TKPROF. That will show you what Oracle is doing under the covers and where the time is spent.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
