Re: undo header congestion? (8.0.5)

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0401291724.48ebdaa7_at_posting.google.com...
>
> Maybe it's just too late in the day for me to think clearly, but why
> would there be too many physical I/O's simply from an excess of
> rollback segs? Won't they just sit there unwanted and lonely? I've
> never really tried an excess, since I've always run into disk
> limitations.
>

Hi Joel,

The point of course is that they don't sit there unwanted. It just takes a relatively long long time until an particular rollback block is required again. Therefore additional I/O could result because of two reasons.

Firstly, as I said, if we have many rollback segs, we therefore have many rollback blocks. And it would be a relatively long time since an individual rollback block was last accessed by a transaction. This means the chance of it still being cached in memory is minimal. Therefore, the undo block *must* have been written to disk before a subsequent transaction had the chance to reuse it again. Therefore, it was previously accessed by a transaction, updated and written to disk *before* the next transaction that required the block had any chance to use it. Therefore each and every use of a rollback block by the writing transaction results in a physical I/O. Note that it's *writes* that are of concern. The request for a new rollback block doesn't require a physical read because Oracle knows the existing contents are of no concern and are to be overwritten anyways. However the dirty rollback block that eventuates must eventually be written down. This is what we are trying to reduce as most rollback block writes are unnecessary as we seldom ever need to subsequently re-read them (very long running read consistent queries perhaps).

If however we had small/few rollback segments such that they could be effectively cached in memory, then a subsequent transaction would be able to find the required block in memory. This means *multiple* changes to an individual rollback block could occur before it had to be written to disk. The net result, less I/O as effectively multiple changes can be piggy backed onto the rollback block, although of course only the last transaction(s) are still stored.

Key point here though is what actually causes the block to be written down if it's effectively cached and subsequently reused. The answer is our beloved checkpoints. So even small, well cached and frequently used rollback blocks could be written to disk "prematurely" if checkpointing is set too aggressively, particularly with aggressive incremental checkpoints.

The second reason why many/large rollback segs cause additional I/O is because of rollback flooding of the buffer cache. If we keep requesting a different rollback block with each transaction, this means space needs to be made for these new blocks. And for every new block that gets in, something has had to make way. Now of course Oracle's touch count algorithm ensures that it's the old and less useful stuff that goes, however by flooding the buffer cache with excessive rollback blocks, you increase the stress on the buffer cache and increase the likelihood that more useful stuff that needs to be re-accessed is pushed out. The net result, increased I/O.

Imagine a soccer team where you "size" the team appropriately. You have 12 players (ie. one sub) and every one of the 12 players gets a fair go with minimal "I/O" (comings and goings).

Imagine a soccer team where you had 100 players and each player had to have a fair go. Imagine the excessive I/O's (coming and goings) you would have as only 11 players can play at the same time.

Imagine a rule change to soccer whereby a new player had to come on every 5 seconds (a "checkpoint" occurred every five seconds). Even my squad of 12 would have heaps on comings and goings (I/O)

Hope this makes some kinda sense ;)

Cheers

Richard Received on Sat Jan 31 2004 - 07:07:25 CST