Re: Allocation of extents

Jack Jolly wrote:
>
> I have a question about how Oracle adds extents to tables.
> My understanding is that the initial extent is 5 blocks, and the next extent will be (initial + (initial x .5))
> - again, using the default value for NEXT of 50. Consider what I saw when looking at the extents of a
> particular table:
>
> SEGMENT_NAME EXTENT_ID BYTES BLOCKS
> -------------------- ---------- ---------- ----------
> RI05_T05 0 10240 5
> RI05_T05 1 10240 5
> RI05_T05 2 20480 10
> RI05_T05 3 30720 15
> RI05_T05 4 40960 20
> RI05_T05 5 61440 30
> RI05_T05 6 92160 45
> RI05_T05 7 133120 65
> 8 rows selected.
> From the table sizing, I'd guess that it's INITIAL 10K, NEXT 10K,
PCTINCREASE 50. What you're seeing is rounding. Back in Oracle6, Oracle would have rounded the calculated extent size up to the next whole block. In Oracle7, it rounds to the next set of 5 blocks. That way, if you drop the table, the extent is more likely to be reused. (The space allocation may change based on space availability within the tablespace).

So the extents go: initial 5 blocks, next 5 blocks, third: 5*1.5 = 7.5 blocks rounded to 10 blocks. The fourth will be 7.5*1.5 = 12 something, rounded to 15. The fifth is 12.*1.5 = 18 rounded to 20. Then 18*1.5=27 rounded to 30. Even though the allocated size is rounded, the numbers used to generate the rounded size do not get rounded.

> Now consider a different table:
>
> SEGMENT_NAME EXTENT_ID BYTES BLOCKS
> -------------------- ---------- ---------- ----------
> PDTABLE_12_2 0 10240 5
> PDTABLE_12_2 1 286720 140
> 2 rows selected.
>
> How did it jump from 5 blocks to 140, in affect "bypassing the smaller extent sizes
>along the way"?

The second extent comes directly from the NEXT setting. My bet is that this table has INITIAL 10K NEXT 280K. PCTINCREASE does not come into play until the third extent.

-Kevin Loney.
see also: Oracle DBA Handbook, ch 4, and Advanced Oracle Tuning & Admin, ch 5. Received on Thu Oct 03 1996 - 00:00:00 CEST