What are extents? How big they should be?

Question 1:

Would someone settle a question for me?

Is it good or bad to have multiple table extents (say 30) or not?

I have read, that multiple extents (anything over 4) is bad just for the fact that you have them, now I have been told by a DBA that it's not bad in and of itself...  I can't see the logic in that one - but maybe I'm missing something - can anyone shed some light on the subject, and maybe some pointers to reference material on the matter...

Thanks...

A novice Oracle DBA


Ans1 for Q1:
It isn't bad. Oracle reads Oracle blocks, not extents. And if you have parrallel query option you must have multiple extents, because Oracle splits tables (indexes, cluster ...) in extents for multiple query
slaves. Of course, a very big number (over 100) of extents will be bad, Oracle must manage them in data dictonary.

Torsten


Ans2 for Q1:
I too had this same question, I had an opportunity to have lunch with one of the consultants we used to install our database. A principal in the firm had attended an Oracle sponsored briefing.

Oracle explained that they were not so concerned about the number of extents, and in fact recommended that next extent size could be set to some small value (say 16K) and that extent size used for every table in the tablespace. Then if an extent was freed in one table it became available for all tables (because they all had the same next extent size). You would then set the max extents to unlimited for all tables in the tablespace. Further: one should not be concerned about hundreds of extents, but rather thousands! I'm sure all this applied to 7 and above.

I haven't tried this, but it's interesting. I was originally told that anything over 30 extents needed to be cared for.  I was told by someone in OWWS that creating tablespaces and tables with unlimited extents was not only a good idea, but was actually encouraged.

Jim Leavitt


Ans3 for Q1:
There is one potentially serious performance side-effect of large numbers of extents in a single segment. It's documented
in a note on my web-site.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk


Question 2:

I have been given conflicting information on the
 purpose of the initial extent value when creating
 a tablespace. I thought that this value was for
 aligning contiguous data if you were about to
 do an import.

 ie:
  If I am about to import 500Megs of data into a
  new TABLESPACE then I would create an initial extent
  close to the size of the data 500M and next extent
  size would be in relation to my normal growth.

 I recently read in some documentation that this
 initial value is used every time you create a table in
 the tablespace. Is this true?


Ans to Q2:

Oracle has a storage clause per table/index etc. If you don't specify a
storage clause at all, the table will be created
a) in your default tablespace
b) using whatever default storage options you specified when you created the
tablespace.
Many third party software packages don't allow you to specify a storage
clause as this is an Oracle specific feature. In that case of course the
default storage clause of the tablespace becomes very important. I have seen
people dividing their tablespaces according to the size of the object. These
tablespace simply have different default storage clauses and no need to
specify any storage clause at table creation.
So no, if you create a table with an initial extent of 500m and you do it
this way
create table emp
(....)
storage (initial 500M next 50m pctincrease 0)
tablespace huge.
the 500M will be used for this table only.
If you issued this command
create tablespace huge
default storage (initial 500M next 50m pctincrease 1 /* in order to allow
for automatic coalescing the tablespace */)
and you don't specify an initial extent, the initial extent needed will be
derived from the default storage clause.

Hth,

Sybrand Bakker, Oracle DBA
 
 
 
 

Hosted by www.Geocities.ws

1