Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Second call to DBMS_MVIEW.REFRESH takes much longer

Re: Second call to DBMS_MVIEW.REFRESH takes much longer

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 26 May 2006 10:09:35 -0700
Message-ID: <bf46380605261009s2db3925fq8e77570428f5ac11@mail.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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 26 2006 - 12:09:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US