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.

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:

DATAFILE ‘/u02/oradata/ORCLBD/TEST2.dbf’
   SIZE 5M


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:

  4  DATAFILE ‘c:/temp/TEST2.dbf’
  5     SIZE 5M
  9  /

Tablespace created.


Next, create a table in the tablespace …


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

--------- --------- ----- ------
BABY              0 16384      2 

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

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

--------- --------- ----- ------
BABY_I            0 16384      2 

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

--------- ----------- ----- ------
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 😉

How To Really Create A Function-Based Index (A Day In The Life) July 12, 2009

Posted by Richard Foote in Function Based Indexes, Humour, Oracle Indexes.

“I heard the news today, oh boy” (John Lennon 1967).

It was with some amusement that someone pointed out Don Burleson’s latest 11 July 2009 “Oracle News” piece : “How to index on a SQL CASE Statement” (the article it links to is dated 29 December 2008).

It must obviously be a very very slow news day as the ability to create a function-based index has been around for a long time, the SQL CASE “statement” (expression actually) being around since at least Oracle8i days. So it’s not exactly “new” news.

However, what’s particularly amusing in this little “news” piece, is the SQL statement used to demonstrate how to create an index on a SQL CASE statement:

create index
case_index as
when ‘PO’ then PO_ID
when ‘VOUCHER’ then voucher_id
ELSE journal_id
end = ‘0000000001’

No, this is not some new weird piece of SQL. The problem of course is that this most definitely is NOT how to create a function-based index with a CASE statement. It’s fundamentally syntactically totally wrong, not just in one place, but in several places.

If you were to just TEST this SQL, you would get the following error:

SQL> create table case_tab (source_tran varchar2(5), po_id varchar2(5), voucher_id varchar2(5), journal_id varchar2(5));

Table created.

SQL> create index case_index as (case source_tran when ‘PO’ then po_id when ‘VOUCHER’ then voucher_id else journal_id end = ‘00001’ end);
create index case_index as (case source_tran when ‘PO’ then po_id when ‘VOUCHER’ then voucher_id else journal_id end = ‘00001’ end)
ERROR at line 1:
ORA-00969: missing ON keyword


You see, to create an index, you must at the very least need to specify the table that is being indexed …

Additionally, the AS statement is incorrect and the CASE statement itself is wrong and incorrectly specifies an additional END condition. There’s a lot wrong here.

If you really want to create such an index, then you need the SQL syntax to at least look something like this:

SQL> create index case_tab_idx on case_tab (case source_tran when ‘PO’ then po_id when ‘VOUCHER’ then voucher_id else journal_id end);

Index created.


The “News” article then suggests you need to collect statistics on the index:

EXEC DBMS_STATS.gather_index_stats(‘OWNER’, ‘CASE_INDEX’);

However, this advice is again not quite correct. Remember, this “News” piece is only a day or two old and Oracle has been automatically collecting statistics on indexes as they’re created (or rebuilt) since Oracle 10g. Providing the table already has statistics, Oracle will automatically collect statistics on the index as it’s being created. There is therefore no need to collect index statistics again after the index has been created.

But but but, what does not have statistics and what really should be collected after you create any new function-based index are the statistics on the virtual column that is created on the parent table as part of creating any function-based index. This is vitally important, else the function-index may not be used by the CBO as expected. For example:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘CASE_TAB’, estimate_percent=>null, cascade=>true, method_opt=> ‘FOR ALL HIDDEN COLUMNS SIZE 1’);

I discuss this issue in some detail the post “Function-Based Indexes And Missing Statistics“.

So some advice in summary:

1) If you’re going to create a function-based index, get the syntax correct. It helps,  it really does.

2) Ensure you collect the associated statistics on the table virtual column that is created automatically behind the scenes.

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


UPDATE: 13 July 2009: The “News” article has now been amended to expand the discussion on statistics and reference the dbms_stats.gather_table_stats procedure for collecting hidden column statistics on the function-based index virtual columns as I discussed. The article is still dated 29 December 2008.

I also notice the newly added gathering hidden column statistics example used in both the news article and in the referenced “important notes” is simply a cut ‘n’ paste from this very blog post. I can easily tell because:

  1. It uses the same table_name as I made up in my example (CASE_TAB)
  2. It has the same error as in my original version with the missing quote when defining the table_name 😉

April Fools (I Got You Babe) !! April 1, 2008

Posted by Richard Foote in Humour, Oracle Blog, Richard's Musings.

Firstly, thank you for all the comments and emails, I’m very touched !!

Those of you that know me realise I have a naughty little streak in me that likes to get out and have some fun every now and then. April 1st is usually one of those days.

I feel a little guilty in posting my “Announcement” on 31 May GMT, however it was well and truly the morning of April 1 here in sunny Canberra, Australia, Australian Eastern Summer Time. So it’s well within the official April Fools rules of conduct. Time zones are a funny thing.

The first subtle clue that this “Announcement” was not quite what it might appear was in the title, Announcing Finale, as it has the same initials as April Fools. However, the far more obvious clue was within the post itself, with the first letter in every paragraph spelling April Fools. I modified the post to highlight how it was sitting there all along. Hee hee 😉

I must say I had thought the reference to my future career plans in the adult film industry might be stretching things a tad (pun fully intended), that and the fact I must of sounded totally demented, but it seems that many saw this as some kind of confirmation that I was really moving on !! I guess with a name like Dick Foote, anything is possible I guess.

Congratulations to those who picked it and sorry to those of you who I caught in the net 🙂

However, let me clearly state I have absolutely no intention of retiring and that this Blog will be around for quite some time yet.

I guess it only leaves me with one more thing to say, “April Fools” everyone !!

Announcing Finale (The End) March 31, 2008

Posted by Richard Foote in Humour, Richard's Musings.


After much thought, I’ve decided to finally retire from being an Oracle DBA.

Previously, I had hoped to delay this difficult decision, but I’m sure the time is now right.

Recent events have convinced me that I’m just not suited to this role and all this Oracle tuning stuff is too damn hard.

In fact, if you read some of the stuff I’ve written here, it’s clearly not right that I should continue.

Little by little I’ve come to the realisation that it’s best if I simply just call it a day.

Fortunately, some of the recent comments here have really helped me to see the light and for that I sincerely thank them.

Ordinarily, I would try and battle on, but it’s just got to be all too hard for me.

Only time will tell if I’ve made the right decision.

Let me however say a big thank you to all of you who have supported me over the years, it’s been much appreciated.

So that’s it folks, I going back to working in the adult film industry, all the very best for the future …