Re: most expeditious way to update a large number of rows

mylesv_at_gmail.com wrote:
> DA Morgan wrote:
> >
> > Unless my guess about your background is incorrect, you didn't respond
> > to that part ... the question not relevant because the size of redo
> > logs does not relate to the size of transactions.
>
> Daniel,
>
> Your guess about my background is partially correct. I've worked with
> Sybase and SQL Server in the past. I don't consider myself a DBA, I
> work _as_ a DBA. It's one of many things on my plate. That's (often)
> the reality of working for a small company.
>
> With Brian's help, I estimated that my update would create 700+ MB of
> redo. You're saying that the size and number of redo log groups is
> irrelevant? It seems I'll see a log switch 13 or so times with my five
> 10 MB log groups.

Umm no 700 divided by 10 is 70 not 13 so if your number is correct on amount of redo that's 70 log switches. If your system drives the filling of logs so quickly that the archiver cannot keep up ( ie you run out of free logs where archiving has been successfully completed ) during those internals that's potentially a bunch of times where activity in your system might freeze until the next redo log is available.

Log switches force system checkpoints where data blocks in the buffer cache that are protected by redo are flushed out to disk.

That's necessary overhead and the way oracle recovery is based but it is additional impact to your system. Is it noticeable for your system and your application? That's your call.

Tom Kyte has a bunch of good online information in the form of questions and answers over at http://asktom.oracle.com ... there's an item that talks about the relationship between log switches and checkpoints that you may want to look at. Received on Tue Oct 17 2006 - 07:07:28 CDT