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

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Wed, 28 May 2003 08:18:14 -0700
Message-ID: <3ED4D336.3ED0D491@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?

Thanks again.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed May 28 2003 - 10:18:14 CDT