Re: Second call to DBMS_MVIEW.REFRESH takes much longer

  • From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Fri, 26 May 2006 23:36:04 +0200

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@xxxxxxxxx>: 
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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l






--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist



--
Best regards,
Alex Gorbachev

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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2006