Re: NOARCHIVELOG and rollback segments

"John" <jbradshaw777_at_yahoo.com> wrote in message news:f2f59d82.0311051025.6c4ca9a7_at_posting.google.com...
> Hi group,
>
> 1. Does NOARCHIVELOG offer any performance benefits other than saving
> on the archived redo logs - and maybe the extra I/O associated with
> archiving these logs? Let's say my redo log is 1G, can you expect any
> significant performance by running in NOARCHIVELOG mode?

It's an apples and oranges question. No-one would choose to run in noarchivelog mode just for a performance benefit, because it renders committed data liable to loss. So even if a database ran 4 times faster in noarchivelog mode, you would forego the speedup for the security/safety.

Besides, even in noarchivelog mode, transactions still generate redo. And LGWR still has to write that to the online redo logs. So it's not like you'd be saving yourself much work anyway. Where the real performance worries come from is when ARCH has to read and copy what LGWR has previously written. If ARCH is too slow, LGWR will recycle the online logs, catch up with ARCH, and your database will stall until ARCH has cleared the backlog. But that's solvable by proper configuration: have enough log groups. Make sure ARCH is not reading and writing from and to the same disks as LGWR is writing to. Make sure ARCH is not trying to write to a disk that has all sorts of extraneous I/O happening to it. And so on. And remember that since about version 8i, Oracle tries to tune ARCH itself by spawning additional ARCH processes when it sees a backlog of unarchived online logs building up.

So only if you configure your logs, online and archived, horrendously would performance suffer.

> 2. Can a transaction span more than one rollback segment?

No. If it starts in one rollback segment, it must finish in that rollback segment. In 9i, with "undo segments" (which are just rollback segments with a different name) that's modified slightly because one segment can 'steal extents' from another, and so a transaction can indeed appear to cross between segments. But that's uniquely 9i.

> 3. For a rollback segment contains 4 extents. Ext 1 contains committed
> data. Ext 2 contains uncomitted data. Ext 3 contains committed data.
> If data on Ext 2 remains uncommitted, will the data on Ext 3 ever get
> overwritten?

No. You can't skip extents; you have to take them in order. And you can't move into an extent that contains uncommitted transactions. Therefore, when you've filled extent 3, you move into extent 4. After that, you move into extent 1. But after that, you can't move into extent 2, and you can't skip extent 2 either. So you start acquiring additional extents. And you keep on acquiring new extents until extent 2's transactions are committed.

Regards
HJR
>
> Thanks!
>
>
> John
Received on Wed Nov 05 2003 - 13:21:13 CST