Home » RDBMS Server » Performance Tuning » MView Complete Refresh Performance Issue (Oracle 10g R2 Enterprise Edition 64-bit, Windows 2003 Standard Edition 64-bit)
MView Complete Refresh Performance Issue [message #321749] Wed, 21 May 2008 04:19 Go to next message
Messages: 4
Registered: July 2007
Junior Member

We are migrating database from Oracle 32-bit to Oracle 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.

Re: MView Complete Refresh Performance Issue [message #321789 is a reply to message #321749] Wed, 21 May 2008 05:53 Go to previous message
Messages: 3727
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
Previous Topic: Finding HWM
Next Topic: Oracle partition doesn't work for select which scans entire table
Goto Forum:

Current Time: Sat Aug 19 04:00:31 CDT 2017

Total time taken to generate the page: 0.03917 seconds