Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

Redo usage explosion puzzle

From: Steve B <BigBoote66_at_hotmail.com>
Date: 22 Jul 2004 15:51:43 -0700
Message-ID: <67bcf80a.0407221451.2231d6ad@posting.google.com>


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? Received on Thu Jul 22 2004 - 17:51:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US