jump to navigation

Why A Segment Really Has To Be At Least 2 Blocks In Size July 20, 2009

Posted by Richard Foote in Extent Management, Humour, Tablespace Management.
trackback

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 ;)

About these ads

Comments»

1. Kumar - July 20, 2009

Thank you Richard. Great explanation. The 2nd summary point is very good.

2. Marcin Przepiorowski - July 20, 2009

Hi Richard,
I can see that you are fighting with Don on every occasion ;)
I remember that for Oracle 8i it was some rules for minimal extent size (5 blocks) for dictionary managed tablespace. I can’t find now any description for LMT for 9 and above database but I’m sure there are some guidelines too.

Good work ;)
regards,

odenysenko - July 21, 2009

It’s always interesting to see how everybody fight with Don… ;)
but Don is… just doing his business…
and… does not pay any attention…

PS.
Tanks to Richard for good post on easy things

3. Luis Moreno Campos - July 21, 2009

If I was Don’s client I would be very worried about posts like these.
Rick you’re the best!

4. Andy - July 24, 2009

Richard, you are too kind to Don. You are fixing his mistakes for him at no charge ;)

I bet he follows your posts ( and probably Jonathan Lewis’s, etc) regularly for short cut, and I bet his mistakes will disappear from his website pretty soon without any trace…….. ;)

Richard Foote - July 27, 2009

Hi Andy

I know he peeks here now and then. In attempting to fix his Function-Based index news article, he actually cut ‘n’ pasted my exact collect hidden column stats example, including the table name I used and missing apostrophe ;)

The article is still wrong nonetheless …

5. Log Buffer #155: a Carnival of the Vanities for DBAs | Pythian Group Blog - July 25, 2009

[…] Here’s the selfsame Richard Foote, looking at why a segment really has to be at least 2 blocks in size. […]

6. olivier - July 26, 2009

Don did it it again :) I’m suprised he has not posted here yet …. :)

Richard Foote - July 27, 2009

Hi Olivier

I’m not surprised :)

He’s done it again a number of times since, including getting the CBO cost equations wrong is this recent “news article”:

http://www.dba-oracle.com/t_sql_optimizer_costing_equations.htm

and then there’s this recent gem on his forum:

http://dbaforums.org/oracle/index.php?showtopic=18391&pid=52427&mode=threaded&start=#entry52427

where he claims that the arraysize impacting the number of consistent gets is “an illusion” and that arraysize “governs the number of rows Oracle will fetch before shipping them back, but ONLY for full table scans and index fast full scans”.

Again though, I’m not surprised :(

Arian - July 28, 2009

That one is really funny!
How he manages to make a statement and then put a link to prove him wrong.

7. Richard Foote - August 20, 2009

Hi Arian

Now this one is really really funny:

http://dbaforums.org/oracle/index.php?showtopic=18497&pid=52879&mode=threaded&start=#entry52879

Here we have someone who’s attempting to use the MOVE clause when altering an index (which is not allowed) while trying to increase the number of freelist groups (which is also not allowed).

And the recommended solution to these errors ?

To use an underscore in the freelist groups clause (eg. freelist_groups) which of cause is incorrect syntax.

So instead of highlight the two things that are wrong, the recommended solution introduces yet another wrong piece of syntax in one of the few areas that was originally correct !!

When the poor sod replies that it still doesn’t work he’s told “what are you talking about” !!

Now that’s brilliant :)

Arian - August 21, 2009

O.M.G.

I just sprayed coffee over my monitor. It actually reads like a Monty Python sketch :-)

Richard Foote - August 22, 2009

Hi Arian

I love Monty Python :)

However, real-life can be even funnier at times. This is one classic example.

8. A reader - October 12, 2009

But his solution works for the clients..thats why he is alive in the business of oracle…
If he is so much wrong,then how he is successful?

Richard Foote - October 13, 2009

Hi A Reader

But a segment doesn’t have 2 extents by default and it doesn’t help “clients” to suggest otherwise.

Telling a “client” that what they’re attempting to perform is not allowed by Oracle might help them, but to suggest they should proceed by introducing even more syntax errors is not a solution that works for the client at all.

Being successful is a relative thing I guess …

Andy - October 16, 2009

Is he successful? Hmm…. ok, let’s assume he is.

That still doesn’t mean he is good at Oracle DB. It could be just a case of (1) he is good at marketing, and (2) his customers aren’t particularly technical.

You only need a short-sighted person to lead a blind man ;)

9. A reader - October 16, 2009

He seems to be quite a smart man then!! With so much false knowledge about oracle [as is said for him],he is fooling his customers….


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,915 other followers

%d bloggers like this: