Re: large matview log, does it lock base table when dropping?

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 1 Aug 2008 07:25:55 -0700 (PDT)
Message-ID: <890221.97862.qm@web80608.mail.mud.yahoo.com>


> Date: Thu, 31 Jul 2008 10:32:51 -0500
> From: Dan Norris <dannorris_at_dannorris.com>
> 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$_<base table name>). 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       

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 01 2008 - 09:25:55 CDT

Original text of this message