RE: (looong) PCTFREE, PCTUSED and ASSM

Actually row migration is a big problem for FTS also(whether serially or using PQ).
You end up waiting for too many "db file sequential read" single block reads instead of
MBRC in (direct path read, db file scattered read)

Regards,

Waleed

-----Original Message-----
Sent: Tuesday, November 11, 2003 9:09 PM To: Multiple recipients of list ORACLE-L

> From that, it would follow that free lists as such are gone in ASSM
tablespaces
> and are replaced by bitmaps. As Richard Foote has shown, PCTFREE is not
ignored,
> but without free lists, it doesn't make much sense. Does any of the gurus
(Cary, Steve,
> Jonathan, Wolfgang, Pete Sharman) have any knowledge of how exactly
PCTFREE is
> implemented in the ASSM situation? I can see and verify that it is not
ignored, but
> I can't figure how does it work.

Mladen,

The reason for existence of PCTFREE parameter is completely irrelevant to free space management issues, regardless whether we use FLM or ASSM. The only reason for PCTFREE is preventing/reducing row migration, through reserving space for row growth.

As you know, row migration is bad for performance because reading migrated rows using index rowid access means reading datablock from original location of the row (the rowid stored in index), then the actual block where row has migrated (original block contains pointer to new location). Migrated rows don't cause that much of a problem for full table scans because all blocks are read anyway, but for index range scans they do.

The problem comes from that whenever a row is migrated, corresponding index entries are not updated with the new location of a row, a pointer to it is left behind into datablock instead. This is not because of lazyness of a kernel programmer, this is intended behaviour, imagine if we'd have to scan and update all indexes on a table whenever it migrates due even 1-byte growth...

So, it's cheaper to reserve some space in a block for future growth of a row, instead of trying to keep blocks 100% full and maintain all indexes on a table (plus find a new block for the row) every time row increases even a bit.

So, hopefully this clears a bit, why we still need PCTFREE, regardless of free space management mechanism we use.

In ASSM, as you said, we have currently 6 different freeness statuses for table blocks, 0 = unformatted, 1 = logically full, 2 = 0-25% free, 3 = 25-50% free, 4 = 50%-75% free, 5= 75-100% free. When a datablock is filled to some level, it's corresponding bit vector in it's level 1 bitmap block is updated to reflect its "freeness". When a block is 90% full for example, it's FS will be set to 0-25% free. However, if the block freeness drops below PCTFREE, it's freeness state will go to "FULL" regardless what's the percentage of free space in your block - it's PCTFREE that matters. You can easily trace it using events 10612 and 10613. At least in 9.2 it seems that also these blocks are marked FULL, which are rejected for an insert because the new row would have caused the free space drop below PCTFREE. Even if the existing block is 99% free and could accommodate several smaller rows in the future. This seemed a bit odd for me, because I thought that ASSM was supposed to eliminate the problem with inserting heavily varying sized rows, but few of my tests didn't show that good results. Maybe we'll see this improved in future versions.

PCTUSED, however, is purely a space management parameter, which helped to reduce hit on freelists when block free space was fluctuating on PCTFREE level. Since freelists are implemented as linked lists of regular datablocks, it could get quite problematic if we'd have several blocks on freelists which actually don't have any real space for inserts. Although you can't specify PCTUSED in ASSM table, it exists there internally as well - a block is cleared the "logically full" status if it's freeness state drops below the next level under PCTFREE. This means if PCTFREE is 10%, then block is going to have FULL bit until it's free space increases at least to 25% (Remember the 0-25, 25-50, 50-75 and 75-100% freeness states)

So, we could somewhat say that Oracle has hardcoded PCTUSED into it's ASSM mechanism, but one shouldn't consider ASSM an improved version of Freelists, it's completely different approach for managing free space and it has really lot's of optimizations in it.

I now realized how much time it is, so I won't continue on how Oracle searches for free blocks, I found it to be way more complex (and fascinating :) when I did some research on it. The guy's at Oracle have really spent a lot of effort on figuring out a good solution for high-concurrency environments.

Even though I don't have a whitepaper there, you could get some more information out from my presentation:
http://integrid.info/Poder_Freelists_vs_ASSM.ppt

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).