Re: Next Extent?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/29
Message-ID: <33dd9030.1716002@www.sigov.si>#1/1

On Mon, 28 Jul 1997 16:31:59 +0100, Hanna LTEIF <hanna.lteif_at_infotechnique.com> wrote:

>HI,
>
>I have this situation, i created a table with theses parameters:
>
> INITIAL : 252k
> NEXT : 28k
> PCTINCREASE : 0%
>
>We do a lot of insert and delete on that table. SO the problem(?), is
>that i was selecting the information from the table sys.dba_extents for
>that specific table, to see how many extents it did consume until now,
>and the result of the select is :
>
> extent_id file_id block_id size in bytes
> --------- ------- -------- -------------
> 0 8 62 266240
>
> 1 8 127 40960
>
> 2 8 137 40960
>
>The question
>------------
>Is it normal that the extent allocated to the table ( 40960 != 28672 )
>is not equal to the one specified in the create table statement.?
>
>Any help would be appreciated ?

If you look carefully you'll notice that also the first extent is not exactly what you would expect: 266240 Bytes != 252 KBytes.

When allocating space for extents oracle follows some internal rules which override your storage deffinition of segments. This rules include rounding the number of data blocks alocated for each segment in some circumstances. The reason for this is simple - to reduce database fragmentation.

In your case it is obvious you have DB_BLOCK_SIZE = 4K. For the first extent (252K) that would be 63 blocks and oracle rounds that up to 65 blocks (65*4*1024 = 266240). For each subsequent extent (28K) your definition requires 7 blocks and oracle rounds that up to 10 blocks (10*4*1024 = 40960).

You can find the more detailed description of this mechanism in Oracle manuals and DBA handbooks.

>
>Hanna
>--

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jul 29 1997 - 00:00:00 CDT