Re: large matview log, does it lock base table when dropping?
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Aug 2008 07:25:55 -0700 (PDT)
> Date: Thu, 31 Jul 2008 10:32:51 -0500
> From: Dan Norris <dannorris@xxxxxxxxxxxxx>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
