RE: DBMS_REDEF - How online is it, really?
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "SUzzell@xxxxxxxxxx" <SUzzell@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 11 Feb 2014 16:39:56 +0000
The dbms_redefinition online concept is similar to the index rebuild online
concept - you get a journal (qua. materialized view log) that records the
changes made while the rebuild is going on. When you "finish redefinition" the
journal has to be applied before the data dictionary changes can be made to
reassign object names. Oracle needs an exclusive lock on the table while the
journal is created and while the last bits of the journal are applied. I've
seen a couple of complaints of the table being locked for a few minutes when
the time taken to do the rebuild is large - and Oracle's standard advice is (or
was) to do the table, do a resync, do each index in turn, with a resync,
resync, then finish.
You're unlikely to see (by wall-clock) the locking effects on any small test,
though, but if you construct a sequence of steps that could result in the worst
case locking you might get some idea of the potential threat through examining
trace files and wait states. Make sure you leave a handful of uncommitted
transactions around to try and capture the contention points.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Uzzell, Stephan [SUzzell@xxxxxxxxxx]
Sent: 11 February 2014 16:03
To: oracle-l@xxxxxxxxxxxxx
Cc: Uzzell, Stephan
Subject: DBMS_REDEF - How online is it, really?
11.2.0.3.7 on OEL 6, in case that matters.
We’ve been looking into partitioning some large tables (5 trillion rows, 1TB)
so that we can better manage them (drop old data, &c.). Right now I’d say
dbms_redefinition may be the leading candidate, because Oracle says it is an
online operation. I’ve been burned by Oracle’s idea of “online” operations
before – alter index rebuild online, if the code is optimized / hinted to use
that index, may be online as far as Oracle is concerned, but it sure wasn’t for
the end users.
So I’m wondering – has anyone used dbms_redefinition to partition large tables?
Is it truly online? Can Oracle keep up with inserts in the meantime?
We are thinking about using RAT to try to play back a typical workload, but
before we open another can of Oracle worms, I figured a question to the list
might be a good place to start.
Thanks!
Stephan Uzzell
Other related posts:
