Re: Multiple freelist groups to reduce BBW in non-RAC systems?

I try - unfortunately I've already spotted two errors in my note, one of them a silly slip, but the other is likely to cause confusion.

Silly slip - in the bit about decreasing block sizes -

    Good point - especially in Oracle 9; and especially if the     waits are for UNDO blocks, although that might (depending on     the nature of the activity) increase the waits on segment header     blocks

• That should have been "on the UNDO segment header blocks" in the last phrase - referring to the fact that a large transaction has to update its undo segment header block every time it fills an undo block and crosses into a new undo block.

Confusing comment - in the bit about the 'gap'

    WHEN does increasing the pctused to pctfree gap help ? And     should this be done by increasing the PCTFREE or decreasing     the PCTUSED -

• It's a mistake I make quite often because of the image of two lines across a data block representing the pctused and pctfree levels. To push the pctfree line UP, you have to DECREASE the pctfree value - and the entire paragraph was written from the perspective of moving the lines, not changing the numbers.

Regarding the GAP - it's best to view this (I think) as the two lines marking the boundary conditions. The size of the gap, as a percentage of the block, is

    (100 - PCTFREE) - PCTUSED. As you point out, if both lines are near the top of the block (e.g. pctfree = 5, pctused = 95) then it is easy to spot the risk of blocks moving on and off the free lists frequently because of small updates frequent inserts. Every time the insert fails, a block goes off the free list, but if its usage is below pctused then the very next (tiny) update that forces a recalculation of free space is likely to put it back on the free list.

However, if both lines are very near the bottom of the block, a similar scenario IS possible - though much less likely to appear in a typical application. This, of course, is another good reason why "quick tips" are a very bad idea - it's easy to come up with a quick tip that suits the simple, common, occurrences, but overlook the very real possibility that there are always complex, uncommon occurrences.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Don Burleson wrote in message
<998d28f7.0301181115.4af44ee0_at_posting.google.com>...


>Hi Jonathan,

>

>Wow!  You sure know how to write a detailed answer!  Thanks!

>

>However, I keep on hearing about this PCTFREE-PCTUSED "gap", and it

>doesn't make sense to me!

>

>I asked Steve Adams about this and he wrote "A small PCTUSED to

>PCTFREE gap is a problem because most deletes and many inserts

involve


>freelist changes. "

>

>Initially when I heard "gap", I took it to mean that

>100-(pctfree+pctused)=0, but I must be mistaken because this zero-gap

>setting would not cause high "freelist changes".  For example:

>

>- Pctfree = 95 - Un-link at 95% full

>- Pctused = 5 - Re-link when block is 95% empty

>

>But . . . . . .

>

>I could not be that a high "gap" refers to the absolute difference

>between

>PCTUSED and PCTFREE?  Nah, that does not work either:

>

>- (PCTFREE 5 PCTUSED 95) - Of course, excessive freelist re-links

>happen

>when PCTUSED is high, and frequent freelist un-links happen when

>PCTFREE is

>low.

>

>- (PCTFREE 95 PCTUSED 5) - This high "gap" in the other direction

>implies

>much less freelist movement!

>

>So, neither of my definitions of "gap" make sense . . . .

>

>What is this "gap"?