Re: Transaction Gradually Slows to a crawl! (please help)

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3ED4D336.3ED0D491_at_exxesolutions.com...
> Richard Foote wrote:
>
> > "Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message
> > news:3ED4C338.B060678D_at_exxesolutions.com...
> > > Richard Foote wrote:
> > >
> > > > "Ryan" <rgaffuri_at_cox.net> wrote in message
> > > > news:g_UAa.115250$823.9886_at_news1.east.cox.net...
> > > > > how do you disable redo logs? you can generate no redo? really? i
have
> > a
> > > > > staging database also.
> > > > >
> > > >
> > > > Hi Ryan,
> > > >
> > > > By setting "_disable_logging"=true
> > > >
> > > > Note though that this is a somewhat naughty, unsupported and risky
thing
> > to
> > > > do .....
> > > >
> > > > Note also that if your database is in archivelog mode, it's does
rather
> > > > nasty stuff to the statuses of the poor online redo logs and they'll
> > > > probably need to be re-created when you set it back to false again.
> > > > Therefore if you were to ever set the above, I would recommend
placing
> > the
> > > > database in noarchivelog mode first.
> > > >
> > > > Note also (last one I promise) that poor LGWR has an incredibly
> > frustrating
> > > > time of it in that it is still being pinged to perform it's writes
but
> > > > nothing happens. It's rather good fun watching your log write times
> > start to
> > > > go up and up and up ...
> > > >
> > > > Not that I've ever done it of course ...;)
> > > >
> > > > Cheers
> > > >
> > > > Richard
> > >
> > > Ok Richard I'll play along. Hypothetically, of course as you've never
done
> > it,
> > > what happens with this parameter setting when you update a single row
and
> > type
> > > commit? Does it hold everything in a buffer and then write directly to
the
> > > datafile or does it kill performance by writing it out immediately?
> > >
> > > --
> > Hi Daniel
> >
> > The rest of the database remains unchanged. The buffer cache works in
> > exactly the same way, old buffers get overwritten, old dirty buffers get
> > written to disk. It's just the process of physically flushing the redo
> > buffer that gets disabled.
> >
> > Actually, I have used it (you got it out of me :)
> >
> > I used it in a very large test environment where I wanted to perform a
> > massive amount of changes (a process to convert blobs to clobs actually)
and
> > it was going to take days to complete. By disabling logging, I completed
the
> > task in hours and if anything untoward were to have happened, I was
quite
> > happy to restore the test database back from backup.
> >
> > Like I said, it's not something I'm comfortable recommending to folks at
> > there but everything in life has it's use ...
> >
> > It's a question of knowing the risks, the repercussions and the possible
> > benefits.
> >
> > Cheers
> >
> > Richard
>
> Thanks.
>
> Any speculation in this situation on the affect of such init parameters as
> log_checkpoint_interval and log_checkpoint_timeout? Are they affecting how
long
> the buffers stay dirty or are they ignored?
>

Hi Daniel,

Disabling redo logging effectively disables incremental checkpointing.

Remember, why does Oracle perform checkpointing. To have a consistent point of time from which it can perform an instance recovery. However, by disabling redo logging we of course are not protecting our transactional changes, nothing is being written to the redo logs.

So what would happen if we had an instance failure during this period of no redo logging. Well, we would be stuffed (Aussie technical term meaning Buggered). The database would be in an unrecoverable state.

This is not a pretty scenario so that's why *extreme caution* must be exercised if anyone was silly enough to do this. In my case when I took the plunge, it was with the knowledge that if anything were to go wrong with the conversion, I could always perform a complete restore from backup.

Cheers

Richard Received on Thu May 29 2003 - 07:07:53 CDT