Re: switch redo logs off for one table

All this can be a trap for the unwary and that's why I said claims that temp tables don't generate redo is a little deceptive.

I recently had to determine why huge amounts of redo was being generated that was causing all manners of grief for a poor standby database. "We're only doing large DML operations on temp tables so that can't be the cause..."

Well actually ... :)

This should all be a little more obvious than it might be in that this is actually all documented in the doco. It's just "buried" somewhat.

Note also as would be expected, deletes cause the most grief and can contribute (say) 70% of the redo from the undo as the whole row is being stored.

How people word things is important. I picked up on the statement(s) that temp tables should be considered "as they generate no redo". As can be seen, this is misleading and "myth promoting" so if this tread has cleared things up for some people out there in Oracleland, then it's been worth it.

Cheers

Richard
"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:BcxO9.9948$jM5.28462_at_newsfeeds.bigpond.com...
>
> "Karen Abgarian" <abvk_at_ureach.com> wrote in message
> news:3E0A2711.7A77258B_at_ureach.com...
> > This is, of course, obvious, and there is not need to explain what are,
> > er, segments, and how updates on them, er, generate redo. What I
> > meant to mean, is that Oracle marketing always mentions that temporary
> > tables don't generate redo,
>
> But they don't!
>
> So what they claim is true.
>
> If you did an update on a regular table, and it generated (say) 100K of
> redo, the same update on a temporary table would generate (say) 30K of
redo.
> That 30K is the redo related to the rollback segment. It means that in the
> first case, 70K of redo was generated by the table itself. By using a
> temporary table, you have switched off 70K of redo generation occasioned
by
> the table itself.
>
> >and never mentions that rollback segments
> > thing.
>
> Well, it's a matter of emphasis, I suppose.
>
> It is well-known (or ought to be, at any rate) that -bar discrete
> transactions- it is impossible to switch of undo/rollback. It's frequently
> asked for here, and the answer has usually gone along the lines of 'you
> can't switch of rollback because it's not just your rollback: others may
> need it to generate read-consistent images of data, and Oracle needs it
for
> much the same reason internally'. So if it is assumed that one knows
> rollback can't ever be switched off, it logically follows that rollback
must
> be generated by temporary tables.
>
> And given an understanding of what a segment is, it therefore follows that
> updates to rollback segments must generate redo. The same redo that would
be
> generated by the rollback generated for transactions on ordinary tables.
>
> I suppose they (ie, marketing) don't mention it because *that* redo must
> always be generated, whatever type of table you have, and that's always
been
> the case. The 'new' feature (new in 8i anyway) is that the redo occasioned
> by updates to the table segment itself *can* be switched off if the table
is
> a temporary one.
>
> HJR
>
>
> >
> >
> > "Howard J. Rogers" wrote:
> >
> > > "Karen Abgarian" <abvk_at_ureach.com> wrote in message
> > > news:3E095A02.8E9F3998_at_ureach.com...
> > > > Actually it is great!
> > > >
> > >
> > > Well, actually it's well known. Or ought to be. Full marks to Richard
> for
> > > pointing it out, but it *ought* to be obvious.
> > >
> > > The point of Richard's demonstration is that the temporary table does
> *not*
> > > generate redo. But nothing switches off rollback. And naturally
enough,
> > > rollback segments are, er, segments, and always generate redo when
> they're
> > > modified.
> > >
> > > It's not a particularly big deal. And the benefits are still there of
> (OK,
> > > let's be pedantic) 'much less' redo generation than otherwise.
> > >
> > > > Oracle fools us again :-)
> > > >
> > >
> > > No, only a misunderstanding of Oracle leads fools to think anything
> other
> > > than this could be the result.
> > >
> > > HJR
> > >
> > > >
> > > > Jim Kennedy wrote:
> > > >
> > > > > Thanks. Good demonstration.
> > > > > Jim
> > > > > "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> > > > > news:L7gM9.6297$jM5.17207_at_newsfeeds.bigpond.com...
> > > > > > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > > > > > news:XL3M9.5895$jM5.16983_at_newsfeeds.bigpond.com...
> > > > > > > The answer is no.
> > > > > > >
> > > > > > > If a transaction generates redo (and every normal insert,
delete
> or
> > > > > update
> > > > > > > always does, regardless of your setting for NOLOGGING), then
> that
> > > redo
> > > > > > will
> > > > > > > be written to the online logs, and online logs get archived,
so
> your
> > > > > > > transactions are in the archived redo logs.
> > > > > > >
> > > > > > > As someone else has mentioned, you might find that 'create
> global
> > > > > > temporary
> > > > > > > table BLAH (col1 number, col2 char(5)) etc' fits the bill, as
> > > > > transactions
> > > > > > > in global temporary tables are never logged... largely because
> the
> > > data
> > > > > > > inserted into such a table is only visible to the session that
> put
> > > them
> > > > > > > there.
> > > > > >
> > > > > > Hi Howard, Jim and all,
> > > > > >
> > > > > > Suggestions that transactions to temporary tables are never
logged
> is
> > > not
> > > > > > quite correct and is a little deceptive. It's kinda right but
not
> > > quite.
> > > > > In
> > > > > > actual fact changes to temporary tables can produce a
significant
> > > amount
> > > > > of
> > > > > > redo.
> > > > > >
> > > > > > Let me give a simple demo then I'll try to explain.
> > > > > >
> > > > > > SQL> create global temporary table bowie_test on commit preserve
> rows
> > > as
> > > > > > select * from dba_source;
> > > > > >
> > > > > > Table created.
> > > > > >
> > > > > > SQL> select * from v$log;
> > > > > >
> > > > > > GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC
STATUS
> > > > >
> > >
>
> ---------- ---------- ---------- ---------- ---------- --- ---------------
> > > > > -
> > > > > > FIRST_CHANGE# FIRST_TIM
> > > > > > ------------- ---------
> > > > > > 1 1 64 1048576 1 NO
> INACTIVE
> > > > > > 19490665 19/DEC/02
> > > > > >
> > > > > > 4 1 65 1048576 1 NO
> INACTIVE
> > > > > > 19490834 19/DEC/02
> > > > > >
> > > > > > 5 1 66 1048576 1 NO
CURRENT
> > > > > > 19490837 19/DEC/02
> > > > > >
> > > > > >
> > > > > > SQL> update bowie_test
> > > > > > 2 set text = 'BOWIE';
> > > > > >
> > > > > > 145115 rows updated.
> > > > > >
> > > > > > SQL> select * from v$log;
> > > > > >
> > > > > > GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC
STATUS
> > > > >
> > >
>
> ---------- ---------- ---------- ---------- ---------- --- ---------------
> > > > > -
> > > > > > FIRST_CHANGE# FIRST_TIM
> > > > > > ------------- ---------
> > > > > > 1 1 139 1048576 1 NO
ACTIVE
> > > > > > 19504906 19/DEC/02
> > > > > >
> > > > > > 4 1 140 1048576 1 NO
CURRENT
> > > > > > 19505046 19/DEC/02
> > > > > >
> > > > > > 5 1 138 1048576 1 NO
> INACTIVE
> > > > > > 19504746 19/DEC/02
> > > > > >
> > > > > > I am the only user on my little baby DB and yet changes to just
a
> > > simple
> > > > > > little temporary table has produced about 74M of redo (the small
> redo
> > > logs
> > > > > > are there purely for effect ;)
> > > > > >
> > > > > > The key point is this. Changes to temporary tables produces
*undo*
> as
> > > I
> > > > > > might decide to rollback the changes above. Although the changes
> to
> > > the
> > > > > > temporary table itself are not logged, the changes to the undo
> > > segments
> > > > > > *are* logged. This is because we may need to recover a specific
> undo
> > > > > > datafile and need to determine what is what. Therefore changes
to
> > > > > temporary
> > > > > > tables can produce a significant amount of redo.
> > > > > >
> > > > > > An important point that I thought needed clarifying.
> > > > > >
> > > > > > Cheers
> > > > > >
> > > > > > Richard
> > > > > >
> > > > > >
> > > >
> >
>
>
Received on Thu Dec 26 2002 - 07:51:37 CST