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

Then by doing this, the 'D' in the ACID properties can go out the window. In a normal Oracle database, a commit is not complete until the information is written to the online redo logs. This is one of the mechanisms which ensures that a transaction is durable in case of a failure. By removing this mechanism from the picture, one's transactions only become durable once the information is written to the datafiles. If I had to do this (and I can't think of why I would), then I'd make sure the buffer cache gets its dirty blocks written to disk.

Cheers,
Brian

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

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam" from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"