Re: Second call to DBMS_MVIEW.REFRESH takes much longer

Thanks Jared. Usefull note. However, it seems that indexes made it even worth in my case. :) Tried to collect good stats on MLOG$ - not much help. Looks like indexes are mostly helpful to refresh a number of MVIEWs based on the same MLOG.

It seems it's slow in the beginning of fast refresh when Oracle gets rows for delete - introduction of indexes produced even worse execution plan. :)

During FTS it was slow not to due to FTS itself but I feel it was "walking" the cursor and DML statements on MVIEW were actially the bottleneck. I just happen to see FTS in v$session_longops but it wasn't actually an issue I believe.

In the end, I concluded that fast refresh is not easy - MLOG$ is running away faster than I can refresh it :) - Underlying table gets aroung 6000 rows updated DML'ed per second (and this is outside of peak hours).

The only way I can think of to fast refresh is to use home grown solution to parallelize refresh or learn how to parallelize with DBMS_MVIEW. So far I didn't succeed.

2006/5/26, Jared Still <jkstill_at_gmail.com>:
> You may want to build an index on the snaptime$$ column of the MV log table.
>
> The refresh mechanism finds and deletes rows based on the snaptime$$, as
> new rows may be added to the log while a refresh is goind on.
>
> If there is a lot of DML on the table being snapshotted, this will result in
> many
> entries in the LOG table.
>
> FTS will then happen at refresh time, and as you have seen, it's slow.
>
> You could periodically quiesce the source database, do a refresh from all
> targets, then truncate the log table.
>
> Building and index is easier.
>
> See ML note 258252.1
>
> The note warns against performance degradation due to the index,
> but I personally find that overhead acceptable.
>
> After building indexes on the log tables for an SAP system, there
> was a significant reduction in IO.
>
> Jared
>
>
> On 5/26/06, Alex Gorbachev < gorbyx_at_gmail.com> wrote:
> >
> Hi all,
>
> I don't have much exposure to materialized views so, perhaps, it's a
> simple question.
>
> I created materialized view log. Than I created materialized view base
> on prebuilt table (filled just before that). Than I run
> DBMS_MVIEW.REFRESH to fast refresh - it took some time and finished
> successfully. Almost right after that I run DBMS_MVIEW.REFRESH again
> and this was taking looooong time and finally failed with snapshot too
> old (well retention period is not very high on this DB).
>
> So the question is why it takes longer to refresh second time? I would
> expect the second run to be much faster. I checked in
> V$SESSION_LONGOPS and it looks like it's going quickly in the
> beginning and slows down a lot at the end (Full scan of MLOG$_ table).
>
> TIA.
>
>
> --
> Best regards,
> Alex Gorbachev
>
> http://oracloid.blogspot.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

-- 
Best regards,
Alex Gorbachev

http://oracloid.blogspot.com
--
http://www.freelists.org/webpage/oracle-l