Home » RDBMS Server » Server Administration » initial extent allocation
initial extent allocation [message #153401] Mon, 02 January 2006 02:11 Go to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hi,

how can I calculate the initial extent at the time of table creation or how do I know that for existing table whether it is appropriate or not?

And one more thing, allocating the initial extent with high value affect the performance or not?

I mean is there any impact on performance because of extent size?

Please tell me in brief.

Kinjal.
Re: initial extent allocation [message #153415 is a reply to message #153401] Mon, 02 January 2006 03:52 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


As i read in books, in general

if the table is a transaction table i.e. faces lots of DML then the extent size should be smaller and
if the table faces DML very rarely i.e. once loaded and then used for SELECT then the extent size should be bigger.

It also depends on how much rows you want to store in a single extent.

The following could help you more

http://www.ixora.com.au/tips/creation/extents.htm

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5549302357655
Re: initial extent allocation [message #153525 is a reply to message #153401] Tue, 03 January 2006 00:17 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hi,

Still I have some confusions.

I have my tablespace with 65536 bytes (64K) initial extent and 8k block size.

So is it like,
If I create a table then by default the initial extent will be of 64K?
Or can I define the initial extent with small size?

And one more thing, still I don't understand how to calculate the size of the extent while creating a table?
I mean on what basis we decide the size of the extents?

And for DML and for static tables what is proper? uniform size or nonuniform size?

Kinjal
Re: initial extent allocation [message #153536 is a reply to message #153525] Tue, 03 January 2006 01:00 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


-->If I create a table then by default the initial extent will be of 64K? Or can I define the initial extent with small size?

You can specify a smaller one but Oracle will assign a minimum 64K by default.

look here
SQL> select tablespace_name, initial_extent , next_extent 
  2  from dba_tablespaces;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
EXAMPLE_SCH                            516096      516096
VOTER_DET                               65536

SQL> create table dumm1(n1 number)
  2  storage (initial 32K) tablespace voter_det;

SQL> select INITIAL_EXTENT from dba_tables
  2  where table_name='DUMM1';

INITIAL_EXTENT
--------------
         32768

SQL> select bytes from dba_extents
  2  where segment_name='DUMM1';

     BYTES
----------
     65536



-->I mean on what basis we decide the size of the extents?

It depends on the expected growth of the tables.
Look, extents hold rows for a table so you can calculate the size of a row ( sum of bytes held by all the columns). And then how many rows you want to store in an extent, its up to you.


-->And for DML and for static tables what is proper? uniform size or nonuniform size?

Cant say better to wait for other's comments ( someone good in database designing). Open for debate.
Re: initial extent allocation [message #153548 is a reply to message #153401] Tue, 03 January 2006 01:48 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Thanks Tarun.

It really helped me but still I need to wait for last question.

Lets wait then.

Kinjal
Re: initial extent allocation [message #154435 is a reply to message #153548] Sun, 08 January 2006 07:42 Go to previous message
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
Junior Member
Hi

Read this one written by Mr Tim Gorman

http://www.evdbt.com/MythsExtPerf.doc

If further questions please give your Oracle version.

Regards
Guy Lambregts
Previous Topic: Error no. 03114 & DBCA - 00016 in ORA 8i
Next Topic: Lang Java Class Not found erro - DBUA utility on unix
Goto Forum:
  


Current Time: Fri Sep 20 15:33:33 CDT 2024