Why A Segment Really Has To Be At Least 2 Blocks In Size

I just couldn’t resist this one …

In the latest Don Burleson news piece dated July 17, 2009 called “Tablespace size initial extent size” (although the referenced article itself is dated June 18, 2009), it attempts to answer the question why is there a mismatch between the uniform extent size specified in the tablespace and the actual size of extents in the tablespace.

The question uses the following basic example:

CREATE SMALLFILE
TABLESPACE “TEST2”
NOLOGGING
DATAFILE ‘/u02/oradata/ORCLBD/TEST2.dbf’
   SIZE 5M
   EXTENT MANAGEMENT LOCAL
   UNIFORM SIZE 3K
   SEGMENT SPACE MANAGEMENT MANUAL;
 

The uniform extent size is only a tiny 3K, this in an 8K block database. It’s suspected an initial extent would therefore be at least 8K in size but are puzzled why the initial extent is actually 16K.

The reason given by Don Burleson is nothing short of bizarre. The suggestion is that because “the default MINEXTENTS is 2″and because the uniform size of the tablespace is less than the database block size, therefore “the MINEXTENTS takes precedence and you see 16K, 8K * 2, for your initial tablespace size“.

It also mentions that “The initial size of a tablespace is also governed by the MINEXTENTS.  In this case, you did not specify in in your create tablespace syntax, so the default in dba_tablespaces takes effect“.

There are of course a number of things wrong with this explanation.

Firstly, the default value of MINEXTENTS is of course not 2 but 1. This has been the default value of MINEXTENTS since I can remember (with the exception of a rollback segment which is a special case).

In fact, with regard to LMTs, Oracle doesn’t even allow a non-default MINEXTENT value when defining the tablespace as it simply doesn’t make sense to do so (or any other storage clause value for that matter). It’s therefore not specified in the create tablespace syntax because it’s simply not allowed with a LMT !!

Secondly, if one simply just attempted to create an object in such a tablespace, one would clearly see that there is indeed only one extent created by default. And as the question itself suggests, this one and only extent indeed has a size of 16K (assuming it’s in an 8K block tablespace).

First create the tablespace:

SQL> CREATE SMALLFILE
  2  TABLESPACE “TEST2”
  3  NOLOGGING
  4  DATAFILE ‘c:/temp/TEST2.dbf’
  5     SIZE 5M
  6     EXTENT MANAGEMENT LOCAL
  7     UNIFORM SIZE 3K
  8     SEGMENT SPACE MANAGEMENT MANUAL
  9  /

Tablespace created.

 

Next, create a table in the tablespace …

SQL> CREATE TABLE BABY (id number) TABLESPACE TEST2;

Table created.

 

Let’s look at what we’re created …

SQL> select segment_name, tablespace_name, extent_id, bytes, blocks from dba_extents where segment_name = ‘BABY’;

SEGMENT_N EXTENT_ID BYTES BLOCKS
--------- --------- ----- ------
BABY              0 16384      2 

SQL> select segment_name, tablespace_name, min_extents, bytes, blocks from dba_segments where segment_name = ‘BABY’;

SEGMENT_N MIN_EXTENTS BYTES BLOCKS
--------- ----------- ----- ------
BABY                1 16384      2 

 

We of course notice that the table indeed only has 1 extent. We of course notice that the default value of MINEXTENTS is indeed just 1, not 2

And we of course notice that the extent is made up of 2 blocks for a combined total of 16K as suggested by the question.

In keeping with an index theme, we notice exactly the same characteristics if we create an index in this tablespace:

SQL> create index baby_i on baby(id) tablespace test2;

Index created. 

SQL> select segment_name, tablespace_name, extent_id, bytes, blocks from dba_extents where segment_name = ‘BABY_I’;

SEGMENT_N EXTENT_ID BYTES BLOCKS
--------- --------- ----- ------
BABY_I            0 16384      2 

SQL> select segment_name, tablespace_name, min_extents, bytes, blocks from dba_segments where segment_name = ‘BABY_I’;

SEGMENT_N MIN_EXTENTS BYTES BLOCKS
--------- ----------- ----- ------
BABY_I              1 16384      2 

 

Same thing. Only one extent with an initial size of 2 blocks or 16K in total (when created in an 8K tablespace).

A simple test will confirm all this. And this is the beauty of simple tests, we can use them to validate Oracle behaviour. We don’t need a large, commercial, 1000+ concurrent, 100TB database to confirm all this. A very basic test of a PC at home is all you need …

Therefore the whole suggestion that the initial extent size of 16K has anything to do with MINEXTENTS is just plain wrong, wrong, wrong.

When I used to teach all this for Oracle University, if a student on an introduction DBA Admin course didn’t know that a segment consists of one extent by default, by lunchtime on the first day, I would be disappointed.

So what’s the real explanation ?

Well, any regular reader of my blog will of course know the answer …

Every segment must have a segment header where segment information such as the extent map, freelists (for non-ASSM segments), the HWM, etc. is stored. However, it makes no sense for a segment to consist of just a segment header, it needs at least one other block in which to store actual data relevant to the segment. Therefore a segment must consist of at least 2 blocks (note for ASSM segments, we need additional blocks for the segment bitmap information to be stored as well).

It makes no sense therefore for a segment to consist of just 1 block, it must be at least 2 blocks in size as an absolute minimum.

Therefore, it makes no sense to create a 1 extent segment where the extent is only 1 block in size. If a segment must consist of at least 2 blocks in a non-ASSM tablespace, then the associated extents must also consist of at least 2 blocks.

So in summary:

1) The default MINEXTENTS is 1, not 2 (with the exception of a rollback segment)

2) The reason why an extent must consist of at least 2 blocks is because a segment must also consist of at least 2 blocks, the segment header plus an additional block

3) Don’t believe everything you read or hear, even if it’s on the “news”

 

Back to more interesting topics soon I promise 🙂

 

UPDATE: 23 July 2009.The article has been updated with the erroneous discussion of MINEXTENTS defaulting to 2 being the issue replaced with the details I’ve listed here and the fact an extent must be a minimum of 2 blocks for a non-ASSM LMT. There are still a number of errors left in the article, however I’ll leave those for Don to sort out. Here’s a tip though. Perhaps save so-called “Oracle news” articles for those questions that are actually answered correctly 😉