Re: (looong) PCTFREE, PCTUSED and ASSM

Hi Tanel,

I think there's a logical reason why ASSM is designed to behave as you describe below.

There's a bit of a balancing act going on here between nice, efficient performance of inserts vs. nice compact, efficient use of storage within a segment. The issue that Oracle has is that it has no real ideal of the average row sizes to be inserted in the future. An insert will only be attempted in a block where it *could* fit, however if it still fails, then we've wasted valuable resources and have to perform additional I/O to find an appropriate block.Yuck !! So should Oracle just treat it as bad luck and "hope" that an appropriate row will eventually turn up or risk the case where the same failure occurs again and again and again with the same block ? Who know which of the two might happen ?

Oracle has chosen the side of caution. It's given the insert some kinda chance by selecting a block where it could fit in, but has decided to make the block full and ensure that similarly size rows don't fall for the same trap again.

Personally, I think an improvement could be made to "relegate" such blocks that fail on insert to the next lowest free boundary so that the same size row won't be tempted by the same block. Also, a change in behaviour in how Oracle deals with a row which it knows "must" fit in a block vs. where it "might" fit in a block might be beneficial and how it classes a particular row size. For example, if a row is 45% of a block, should it consider the 25-50 free space blocks where it "might" fit or only 50% or more free space blocks where it "will" fit. What about rows that are 26% of a block in size, should it consider the 25-50% free space blocks will it will "very likely" fit or the 50% or more free space blocks where again it will definitely fit ? By relegating a block, you help narrow down similarly based previous failures as Oracle goes for the could fit solution.

As it stands, making a block (potentially prematurely) full to prevent similar failures in the near future makes some kinda sense.

Cheers

Richard

• Original Message -----

> 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.
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.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).