Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Redo usage explosion puzzle

Re: Redo usage explosion puzzle

From: Daniel Morgan <>
Date: Thu, 22 Jul 2004 18:10:46 -0700
Message-ID: <1090545071.983836@yasure>

Steve B wrote:

> Hi,
> We recently did some table maintenance on our 8.1.6 Oracle server - we
> had tables that had grown too large (several years of data), and the
> most efficient way to cut them down to size (several months of data)
> with a minimal amount of down time was to create identical "shadow
> tables" (with no indexes or triggers) in a new tablespace, and put
> "audit triggers" on the old tables such that any new data coming in
> would be echoed to the shadows.
> After a month or so, we took the system offline, renamed the tables so
> that the shadows were now the real ones, dropped the old triggers &
> rebuilt the appropriate indexes & triggers on the new tables & turned
> it all back on again. We didn't drop the old tables or indexes
> because we couldn't afford the load on the system while objects
> dropped (it would take hours to drop some of our larger tables).
> Anyway, our problem:
> About 5 days after the switchover, as usage on the system cranked back
> up to normal levels, we noticed a huge increase (5x) in the generation
> of archived redo logs (previously about 100MB in 40 minutes, now about
> 100MB every 5 minutes) and a similar increase in demand on rollback
> segments.
> We can't figure out where the heck all this new i/o is coming from.
> Doing an analysis of the data going into the system (via count(*) on
> the various tables) yields a slight increase (maybe 20%), and we've
> confirmed that old tables are not being used (Logminer shows no
> activity on the old tables).
> I would think that Logminer would be able to help us decode this, but
> I see nothing in it that would let you determine how many bytes of
> rollback/redo are used for a given row in the V$LOGMNR_CONTENTS table.
> The only thing I can think that may have some connection is that the
> new tablespace is locally managed, whereas the old tablespace was
> dictionary managed, and the extents in the high volume tables for the
> new objects have been made much larger (500MB extents instead of 1 MB
> extents).
> Any ideas or directions to investigate?

Excuse me but I'm wondering why you are using one of the buggiest versions of Oracle released in the last decade. Why not at least

Hopefully your 8.1.6 has at least been patched to but about now your resume must be feeling a bit shaky.

Daniel Morgan Received on Thu Jul 22 2004 - 20:10:46 CDT

Original text of this message