Skip to Main Content
  • Questions

  • Extent Allocation not happening as stated in the manuals

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, rashmi.

Asked: November 29, 2001 - 11:44 am UTC

Last updated: January 20, 2003 - 6:05 pm UTC

Version: 7.1.3

Viewed 1000+ times

You Asked

Although the problem I am about to state is related to a 7.1.3 database. I would like to know if the same would happen on 8.1.6.3 databases as most of ours are 8.1.6.3 databases.


While monitoring space allocation we have noticed that one of the tables needing 40m space have not used up space from one of the contguous free spaces in the tablespace of the size of 100m,120m,60m. Instead it has used space from the largest contiguous free space i.e 703m.As per our understanding Oracle should have allocated from one of the smaller bits of space mentioned earlier. I have just read a document on Oracle metalink(DOC ID :69343.1)allocation but none of the scenarios there matches our case.

Could you please tell me under what circumstances would Oracle ignore
these other smaller chunks of free space that are closer to the required next extent size for an object?If this happens how can we predict space requirements for a tablespace? Is it possible to order the extents listed as free in the SGA by their size ? How can we control the allocation such that an object uses up smaller chunks closer to its requirement instead of picking up from anywhere.

and Tom said...

The algorithm is well documented in the Server Concepts Guide.

This is a cut and paste from the Oracle7 Server concepts guide:

<quote>

How Extents Are Allocated for Segments

Oracle controls the allocation of extents for a given segment. The procedure to allocate a new extent for a segment is as follows:

1. Oracle searches through the free space (in the tablespace that contains the segment) for the first free, contiguous set of data blocks of an incremental extent's size or larger. Oracle finds the free space for the new extent by using the following algorithm:

a. Oracle searches for a contiguous set of data blocks that matches the size of new extent, rounded up to reduce internal fragmentation. For example, if a new extent requires 19 data blocks, Oracle searches for exactly 20 contiguous data blocks. However, if the new extent is 5 or fewer blocks, Oracle does not round up the request.

b. If an exact match is not found, Oracle then searches for a set of contiguous data blocks equal to or greater than the amount needed. If Oracle finds a group of contiguous blocks that is at least five blocks greater than the size of the extent that is needed, it splits the group of blocks into separate extents, one of which is the size it needs; if Oracle finds a group of blocks that is larger than the size it needs, but less than five blocks larger, it allocates all the contiguous blocks.

Continuing with the example, if Oracle does not find a set of exactly 20 contiguous data blocks, Oracle then searches for a set of contiguous data blocks greater than 20. If the first set that Oracle finds contains 25 or more blocks, it breaks the blocks up and allocates twenty of them to the new extent. Otherwise, it allocates all of the blocks (between 21 and 24) to the new extent.

c. If Oracle does not find a larger set of contiguous data blocks, Oracle then coalesces any free, adjacent data blocks in the corresponding tablespace so that larger sets of contiguous data blocks are formed. (The SMON background process also periodically coalesces adjacent free space.) After coalescing a tablespace's data blocks, Oracle performs the searches described in a. and b. again. If an extent cannot be allocated after the second search, Oracle returns an error.

2. Once Oracle finds the necessary free space in the tablespace, Oracle allocates a portion of the free space that corresponds to the size of the incremental extent. If Oracle had found a larger amount of free space than was required for the extent, Oracle leaves the remainder as free space (no smaller than five contiguous blocks).

3. Oracle updates the segment header and data dictionary to show that a new extent has been allocated and that the allocated space is no longer free.

Usually, Oracle zeros out the blocks of a newly allocated extent when the extent is first used; in a few cases (such as when a database administrator issues an ALTER TABLE or ALTER CLUSTER statement with the ALLOCATE EXTENT option while using free list groups), Oracle clears the extent's blocks when it allocates the extent.

</quote>

There is nothing in there to suggest it would search for the smallest free extent, it just searches FET$ to find one that fits -- first row it finds, it uses.

The extents are not listed in the SGA, they are in a table.

What you should do is place objects into tablespaces based on their EXTENT SIZES (eg: all objects in a tablespace use the same exact INITIAL=NEXT and PCTINCREASE always is ZERO).

In this case, fragmentation is IMPOSSIBLE (any object can use any extent since all extents are exactly the same size).

It is OK to have hundreds, even 1,000's of extents. It really is OK.

You need probably 3 tablespaces -- one for small, medium, and large objects and you can hold EVERY object. See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:730289259844 <code>
and look for "living" and read that paper for a good strategy.


In 8163, you would be using Locally managed tablespacs with uniform extent sizes (search my site for locally managed to see discussions). This just enforces the initial=next and pctincrease=0 at the tablespace level, making it IMPOSSIBLE for an object to have just any extent size it wanted to.

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 30, 2001 - 3:49 pm UTC


That was a Good One...

Ganesh Raja, November 30, 2001 - 11:52 pm UTC

Hi Tom,

I am being stunned by the way Oracle Allocates Extents.

The Oracle Docs say that Initial Should have a Minimum of two Extents and The Next Should Have 1 Extent.

I am using Dictionary Managed tablespace.

Now...

Create Table A ( A Number)
Storage (Initial 64K Next 64K pctincrease 0 Minextents 1 Maxextents 255);

The Tablespace is Dictionary Managed and the Block Size is 8K. Minmum Extent is 1

Now when i see this i say ... Boss Oracle Creates me these...

1 Extent with 8 Blocks.

And when i ask him for the next extent he is going to give me another 8 Blocks.

But...

it gives me 16 Blocks a Whooping 128K which is not need by me and the Next is also 128K.

Now when i go and see the DBA_Segments view the inital_extent is 8 Blocks or 64 K and the next is 64k.

What is wrong here and what is happening. or am i wrong in the calculation.

Your comments are really appreciated.

Regards,
Ganesh R.

PS Already Posted in the Google groups No replies.

Tom Kyte
December 01, 2001 - 5:41 am UTC 

It'll all depend on the free space and how its organized in the tablespace.  We will not fragment the free extents to a point.  For example, on my system it JUST SO HAPPENS that:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( A Number)
  2  tablespace system Storage (Initial 64K Next 64K pctincrease 0 Minextents 1 Maxextents 255);

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t allocate extent;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t allocate extent;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t allocate extent;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t allocate extent;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select bytes, blocks from user_extents where segment_name= 'T';

     BYTES     BLOCKS
---------- ----------
     81920         10
     81920         10
     81920         10
     81920         10
     81920         10

each extent was 80k -- not 64k.  Its just the way the data was laid out.

That was my 817 database, my 815 database got this:

     BYTES     BLOCKS
---------- ----------
     81920         10
     81920         10
     81920         10
    114688         14
     81920         10


In 8i and up -- I would strongly discourage the use of dictionary managed tablespaces -- especially if you are using different sized extents for everything.  LMTS take all of the guess work out of this and are very predicable.

But The Difference

A reader, December 03, 2001 - 12:15 am UTC

Tom, But the difference in the Dba_Segments and DBA_Extents how do u attribute that.

Regards,
Ganesh R

Tom Kyte
December 03, 2001 - 7:26 am UTC

dba_extents tells you what was allocated. Its what is really there.

dba_segments shows you the definition, what you might use to generate a CREATE TABLE script with.



The Was One Information.

A reader, December 03, 2001 - 11:51 pm UTC

That was a insight.

Thanks Tom.

Regards,
Ganesh R

Block Size Vs Tablespace Storage

Mike, July 26, 2002 - 3:26 pm UTC

Tom,
My Block Size is 16k, what happens If I give the Initial as 16K in my tablespace Storage Parameter? This is not a realistic scenario but I was asked this question in an interview.
Thanks a lot

Mike

Tom Kyte
July 28, 2002 - 3:14 pm UTC

My answer might be:

a) well, don't know if I want to work with you guys actually as you appear to be totally hung up on meaningless trivia. Why not ask me something relevant like "how does a hot backup work, draw me a 'picture' of Oracle on that whiteboard and explain the relevant processes, how they work together, what the files and memory structures are'....

A wise-acre answer could be:

b) A tablespace gets created and objects that are created in it by default would have 16k as their initial extent. or do you mean a STORAGE parameter on a create table/index statement cause there isn't anything called a "tablespace storage parameter". There are storage parameters (initial, next, pctincrease for example) and there is a tablespace parameter (what tablespace do I go into). A tablespace does have default storage parameters but then I wouldn't know what the question meant.


Or you could say,

c) well, geez, it depends. Am I using an LMT or DMT here? Cause with an LMT I would need to know if it was UNIFORM -- if so, then I need the uniform extent size to comment further. If it is system allocated then it'll be one of probably 64k, 128k and so on but we cannot really say as it depends on the space available and a totally undocumented algorithm used by Oracle (i've seen different sized initial/next extents using system allocated extents)

If it is a DMT, then I would need to know -- why, why are you using them? They are evil. If you are, I would further need to know if there were a minimum extent size on the tablespace -- to which Oracle would round up. Even after that, if there were no minimum extent size, I would either get a 5 block initial extent as Oracle likes/tends to reuse free extents like that ( in multiples of 5 blocks ) but I would get at least 2 blocks -- one for the segment header and one for the table data itself. It depends -- somewhere between 2 and say about 6 blocks depending.


I might actually answer with B, C and then A before I left ;) I despise interview questions that have nothing to do with the real world.

Sagi, October 16, 2002 - 1:33 pm UTC 

Hi Tom!

My doubt might seem a bit silly. But I am confussed. 

My DB Block Size is 8K. So if i try to create a table with 

initial=next=8K  or
initial=next=40K or
initial=next=64K 

we can'nt say definetly that Oracle would give such and such blocks. That is if it was 8K it should have given me 1 Block (it would be 5 Blocks if oracle rounds up) in CASE-1, 5 Blocks in CASE-2 and 8 Blocks in CASE-3 (It would say 10 blocks if oracle rounds)

My Test Case and Output:
========================
SQL> CREATE TABLE T (X NUMBER)
  2  STORAGE (INITIAL 8K NEXT 8K MINEXTENTS 3) ;

Table created.

SQL> CREATE TABLE T1 (X NUMBER)
  2  STORAGE (INITIAL 40K NEXT 40K MINEXTENTS 3) ;

Table created.

SQL> CREATE TABLE T2 (X NUMBER)
  2  STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 3) ;

Table created.

SQL> COLUMN SEGMENT_NAME FORMAT A30
SQL> SELECT SEGMENT_NAME, BYTES, BLOCKS FROM USER_EXTENTS
  2  WHERE SEGMENT_NAME LIKE 'T%'
  3  ORDER BY 1;

SEGMENT_NAME                        BYTES     BLOCKS
------------------------------ ---------- ----------
T                                  131072         16
T                                  131072         16
T                                  131072         16
T1                                 131072         16
T1                                 131072         16
T1                                 131072         16
T2                                 131072         16
T2                                 131072         16
T2                                 131072         16

9 rows selected.

Therefore the conclusion is that we dont know how many blocks are consumed when a Table is created by specifying INITIAL and NEXT Storage Parameters in the TABLE Creations....Right?

Thanx in advance.

Regards,
R. K.

Tom Kyte
October 16, 2002 - 1:53 pm UTC

USE LOCALLY MANAGED TABLESPACES SAGI

STOP USING DICTIONARY MANAGED

and all things will be nice.....

initial, next, pctincrease -- i hate them. glad to see them go.

Sagi, October 17, 2002 - 7:24 am UTC

Hi Tom !

I am using LMT only. That question was just for understand and knowing the facts.

Therefore I hope you would explain. Please

Regards,
Sagi.

Tom Kyte
October 17, 2002 - 7:40 am UTC

why you want to understand that which is not relevant when there is so much to understand.... OK, here is the algorithm:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c02block.htm#2846 <code>

(that pesky concepts manual strikes again)

Space Allocation

George Spears, October 17, 2002 - 2:20 pm UTC

Tom,

If I recall right, under dictionary managed space allocation, Oracle will try to find an appropriate sized extent anywhere (aka any datafile), and if unable, at some point, will use some algorythm (round-robin maybe??) to lay out the extents across ALL datafiles. Although SIMPLISTIC, think of it as extent 1 in datafile 1, extent 2 in datafile 2, etc. This helps to ensure a better disk I/O pattern.... This is why Oracle is NOT trying to allocate a 40MB extent in the smallest datafile. If Oracle did NOT do this, then it would try to fill up the entire datafile before using a different datafile, which would negatively impact I/O. The original question states that this behaviour is seen in 7.1.3. I do NOT recall at what point Oracle introduced this behaviour...

Clarification

r, January 20, 2003 - 7:05 am UTC

Hi Tom,

Please help me understand this. I read your book and was trying to understand something about oracle blocks. In the book you say that a single data block may contain rows from many tables. My question is that when we create a database object say a table ,oracle allocates an extent( which is 'n' number of blocks) if there is none available. If this is true then a data block should always belong to a single table and store data for only 1 table.

Am I getting some concepts wrong here ?

Please clarify

Thanks


Tom Kyte
January 20, 2003 - 10:53 am UTC

when Oracle creates a SEGMENT -- Oracle allocates an extent. That extent can only be used by that SEGMENT.

On of the segment types we have is called a CLUSTER.

Now, you are assuming that all tables are SEGMENTS and they are not.

create table t ( x int ); <<<===== creates a segment for T

create table t ( x int ) partitioning_clause <<<=== does NOT create a segment for T, but rather each of T's partitions

create table t ( x int ) cluster foo <<<=== does not create a segment for T, puts T into the already created segment which is a cluster.


So, re-read chapter 5 on clusters ;)



Thanks

raju, January 20, 2003 - 6:05 pm UTC

Tom,

Thanks very much for the answer. Actually, I got this doubt while I was reading the chapter on "Architecture" where you have briefly mentioned about the blocks. Haven't got far to Chapter 5 yet but will be there soon ;)

Thanks again.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library