Re: Oddity with NEXT_EXTENT Behavior

In article <88uegc$5nm$1_at_nnrp1.deja.com>,   Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote:
> A co-worker and I had some disagreements on how INITIAL, NEXT, and
> PCTINCREASE interacted in allocating space for a table. So I set up a
> test and the results are puzzling to both. Anyone want to explain
this?
>
> Running 8.0.5 on NT 4.0, my test script looks like this:
>
> create table my_test (lname char(10))
> storage (initial 100k
> next 101k
> pctincrease 50);
> select extent_id, bytes from dba_extents where segment_name =
'MY_TEST';
> select next_extent from dba_tables where table_name = 'MY_TEST';
>
> alter table my_test allocate extent;
> select extent_id, bytes from dba_extents where segment_name =
'MY_TEST';
> select next_extent from dba_tables where table_name = 'MY_TEST';
>
> (repeat the ALTER/SELECT/SELECT several times)
>
> Immediately after the CREATE TABLE I expected extent 0 to be 102400,
> and NEXT_EXTENT to be 106496 (101k rounded to the next 4k boundary).
> This is exactly what happened; so far so good.
>
> Now, after succeeding ALTER … ALLOCATE EXTENT commands, I expected
each
> succeeding value of NEXT_EXTENT to be the previous value multiplied by
> 1.5 and (if necessary) rounded up to the next 4k boundary. Thus the
> progression of NEXT_EXTENT should be
>
> 106496
> 159744
> 241664
> 364544
> 548864
> 823296
> 1236992
>
> Which is exactly what happened. However, I also expected the number
of
> BYTES in each additional extent to be the value of NEXT_EXTENT when
the
> extent was allocated. However, the actual values (select extent_id,
> bytes from dba_extents . . .) were:
>
> EXTENT_ID BYTES
> --------- ---------
> 0 102400
> 1 122880 (expected 106496)
> 2 159744
> 3 245760 (expected 241664)
> 4 364544
> 5 548864
> 6 823296
> 7 1236992
> 8 1236992
> 9 1236992
>
> You can see, extents 1 and 3 were not as expected, based on the values
> of NEXT_EXTENT when they were allocated. Any explanations?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

In the immortal words of Emily Letitia, "Never mind."

An email response suggested looking at factoring in rounding for increments of 5 blocks. This was closer, but not quite. However, additional experimenting also led me to the proper discussion in the docs -- not always easy to find or comprehend.

Turns out the value of NEXT_EXTENT is just a starting point, and that rounding up to 5 block increments is just a part of it. The manual explains a very convoluted algorithm of tacking on extra blocks, looking for areas of contiguous unused blocks, rounding up, rounding down, splitting . . . . . gave me a headache trying to follow it. Bottom line appears to be that after the second extent is allocated, NEXT_EXTENT will be updated (if pct_increase > zero) with something like this: (next_extent = next_extent * (1 + pct_increase), rounded up to next db_blocksize boundary). When the 3d and any subsequent extents are allocated, the value of next_extent is used as the starting point for the allocation algorithm, then updated according to the above "formula."

--
Ed Stevens

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 22 2000 - 15:24:08 CST