Re: MView fast refresh taking long time
Date: Tue, 8 Dec 2009 10:03:29 -0800 (PST)
Message-ID: <7fd24521-d5d4-4a10-8787-cd4efff485ff_at_c34g2000yqn.googlegroups.com>
On Dec 3, 10:11 pm, Randolf Geist <mah..._at_web.de> wrote:
> Stephen,
>
> a couple of points:
>
> 1. Even if the query was running well: I think your query refers to
> the delete part of the fast refresh and the fast refresh performs
> single row operations with the obtained ROWIDs - so potentially 3
> million single row operations by ROWID (for the delete and update
> part), so this part of the fast refresh might require millions of
> consistent gets (and potentially a lot of physical gets, too). I'm not
> sure if this is reasonable performance-wise, so with that change
> volume a full refresh might actually be faster - or as suggested
> perform the fast refresh more often if possible.
I traced this, and it seems to do a bulk delete and then maybe lots of row by row inserts. I have cooked up an inhouse version that uses the MLOG to do just about the same thing, but it still takes a long time (but at least it completes - the Oracle builtin version took 24 hours before we killed it). There are 3 indexes on the replicated table, so I guess doing the 3M deletes and then 3M inserts is doing some serious I/O and redo generation.
> 2. The index on the MLOG (MLOG$_BILLSUMMARY_AK1) looks like a custom
> index - I don't believe this is created by Oracle by default when
> creating a Materialized View log.
Yea, someone must have created that at some time - not sure why either, as all the tables are only replicated once, and the snaptime is always set to the same date, so it looks redundant to me.
> 3. The subquery cannot be unnested because the M_ROW$$ column is
> defined as NULLable by default when creating the MLOG I think - if you
> define it as mandatory more transformations like unnesting might be
> possible - you can force them (if valid) by adding a "/*+ unnest */"
> hint to the subquery for testing purposes.
You are correct, it is defined as nullable - I knew there has to be some reason it wouldn't unnest for me.
> 5. As Mark has already pointed out - what execution plan do you get
> for the statement - are the estimates in the right ballpark?
We had problems in the past with unstable plans on the replication, so Oracle recommended we truncate the mlogs, gather stats and lock the stats to stabilise the plans - the master tables all have good stats, so I guess it will push the optimizer more towards indexes than FTS, which is generally a good thing for these queries.
Thanks for the help - I am getting nowhere fast with this, but I am starting to think we are hitting the limits of our hardware on these tables ... Received on Tue Dec 08 2009 - 12:03:29 CST