FAQ
We've got a large materialized view log (100Gb) on a 1.7 Gb table in
9.2.0.7. We need to drop the log, but we aren't sure if dropping the log
will put any sort of lock on the base table. We're going to try to test
it, but we don't have too many other logs that are large enough to take
time to drop so we can inspect the state of the locking during the drop.

Has anyone done this before to see exactly what structures are locked
during the drop? In theory, since the log is being dropped, there's no
reason to lock the base table since we don't care about capturing
additional changes (that would normally be logged).

Thanks in advance,
Dan

Search Discussions

  • Thomas Day at Jul 31, 2008 at 5:15 pm
    Have you tried

    alter materialized view log on *master* enable row movement;

    alter materialized view log on *master* shrink size;

    This is supposed to be very DML friendly. I haven't tested it for locks but
    it's supposed to only have row locks on data in the log. It could take a
    while for 100G though.
  • Dan Norris at Jul 31, 2008 at 6:19 pm
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    There's actually 100G of data in it (the table changes a lot and this
    has been capturing a month's worth of data), so I'm not sure there's
    anything that can be shrunk at this point.
    Thanks for the tip, though, I think that may come in handy for some
    other logs at some point.
    Dan
    Thomas Day wrote:

    Have you tried

    alter materialized view log on master enable row movement;

    alter materialized view log on master shrink size;

    This is supposed to be very DML friendly.  I haven't tested it for
    locks but it's supposed to only have row locks on data in the log.  It
    could take a while for 100G though.
  • Yavor Ivanov at Aug 1, 2008 at 6:43 am
    The mvew log keeps all the data needed for refreshing the oldest non-refreshed mvew, based on the given table. I had one case years ago (on 9.1) when a master with 12 mvew sites looses one site. The site had was lost forever, which means it's mvews will never refresh. However, the master cannot know that and all mvew logs started to grow. There were about 1500 tables replicated form the master to each of the mvew sites, and all the logs would never shrink. Recreating the mvew logs was not an option, because we would have to make complete refresh to all other 11 sites.
    Since then I know, that if there is some mvew that will never refresh, the solution is to use DBMS_MVIEW.UNREGISTER_MVIEW on the master.

    There is another case. If you have 1 GB table changed frequently, and 1 GB mvew not refreshed for a long time, it may be faster to rebuild the whole mvew (1GB) rather than applying 100 GB changes. In that case you make a complete refresh:
    DBMS_MVIEW.REFRESH(...,'c');
    This will rebuild the mvew from scratch and, if there are no other mvews on that log, it will empty the log from the data.

    Regards,
    Yavor Ivanov
    Oracle Certified Master

    From: oracle-l-bounce_at_freelists.org On Behalf Of Dan Norris
    Sent: Thursday, July 31, 2008 9:20 PM
    To: Thomas Day
    Cc: Oracle L
    Subject: Re: large matview log, does it lock base table when dropping?

    There's actually 100G of data in it (the table changes a lot and this has been capturing a month's worth of data), so I'm not sure there's anything that can be shrunk at this point.

    Thanks for the tip, though, I think that may come in handy for some other logs at some point.

    Dan

    Thomas Day wrote:
    Have you tried

    alter materialized view log on master enable row movement;

    alter materialized view log on master shrink size;

    This is supposed to be very DML friendly. I haven't tested it for locks but it's supposed to only have row locks on data in the log. It could take a while for 100G though.
    -- http://www.freelists.org/webpage/oracle-l
  • Yong Huang at Aug 1, 2008 at 2:25 pm

    Date: Thu, 31 Jul 2008 10:32:51 -0500
    From: Dan Norris
    Subject: large matview log, does it lock base table when dropping?

    We've got a large materialized view log (100Gb) on a 1.7 Gb table in
    9.2.0.7. We need to drop the log, but we aren't sure if dropping the log
    will put any sort of lock on the base table. We're going to try to test
    it, but we don't have too many other logs that are large enough to take
    time to drop so we can inspect the state of the locking during the drop.

    Has anyone done this before to see exactly what structures are locked
    during the drop? In theory, since the log is being dropped, there's no
    reason to lock the base table since we don't care about capturing
    additional changes (that would normally be logged).

    Thanks in advance,
    Dan
    I tested by setting event 10704 at level 10 (Oracle 10.2.0.4). Dropping the
    materialized view took 37 TM locks in mode 3 (SX) on lots of objects, and 1
    mode 6 (exclusive) lock on the materialized view log table (MLOG$_). You can grep "^ksqgtl \*\*\* TM" on the trace file. Other locks are CU,
    TX, MD, etc. My base table is only 55M so dropping the mview log took only a
    few seconds on my laptop (even after a lot of updates). You can experiment with
    a bigger table and mview log and view the sequence of locks taken with event
    10704. Timing info is in the trace file too.

    Yong Huang

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 31, '08 at 3:32p
activeAug 1, '08 at 2:25p
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase