confused about temp tablespace in local extent

[adewri]

Originally posted by sreddy
Did you test this behaviour or you are talking on theoritical/documentation grounds ?

i have tested it on permanent LMT with uniform size and system managed LMT but not on Temporary LMTs.

The result is based on permanent LMT . So i assume that extent allocation would be same in Temporary LMT as in Permanent LMTs.

SYS@ORCL.WORLD> create tablespace SYSTEM_MANAGED datafile      
  2  ‘e:\oracle\oradata\orcl\test01.dbf’ size 50M
  3  extent management local;

Tablespace created.

SCOTT@ORCL.WORLD> create table big_table_system tablespace SYSTEM_MANAGED as select * from scott.voyages;

Table created.

SYS@ORCL.WORLD> select tablespace_name, extent_id, bytes/1024,blocks from user_extents where segment_name ='BIG_TABLE_SYSTEM';

TABLESPACE_NAME                 EXTENT_ID BYTES/1024     BLOCKS
------------------------------ ---------- ---------- ----------
SYSTEM_MANAGED                          0         64          8
SYSTEM_MANAGED                          1         64          8
.
.
SYSTEM_MANAGED                         14         64          8
SYSTEM_MANAGED                         15         64          8
SYSTEM_MANAGED                         16       1024        128
SYSTEM_MANAGED                         17       1024        128
.
.
SYSTEM_MANAGED                         77       1024        128
SYSTEM_MANAGED                         78       1024        128
SYSTEM_MANAGED                         79       8192       1024
SYSTEM_MANAGED                         80       8192       1024
SYSTEM_MANAGED                         81       8192       1024
SYSTEM_MANAGED                         82       8192       1024

83 rows selected.

Here you can see – the first 16 extents are each 64K in size. The next 63 each 1M in size and the remaining are 8M in size. As the object grew – the extents grew as well. That is a total of about 99M of space in there – in 83 extents.

SYS@ORCL.WORLD> create tablespace UNIFORM_SIZE datafile
  2  'e:\oracle\oradata\orcl\test01.dbf' size 100M
  3  Extent management local
  4  Uniform size 5M
  5  /

SCOTT@ORCL.WORLD> create table big_table_uniform tablespace UNIFORM_SIZE as select * from scott.rd_voyages;

Table created.

SYS@ORCL.WORLD> select tablespace_name, extent_id, bytes/1024,blocks from user_extents where segment_name ='BIG_TABLE_UNIFORM';

TABLESPACE_NAME                 EXTENT_ID BYTES/1024     BLOCKS
------------------------------ ---------- ---------- ----------
UNIFORM_SIZE                            0       5120        640
UNIFORM_SIZE                            1       5120        640
UNIFORM_SIZE                            2       5120        640
UNIFORM_SIZE                            3       5120        640
UNIFORM_SIZE                            4       5120        640
UNIFORM_SIZE                            5       5120        640 
UNIFORM_SIZE                            6       5120        640
UNIFORM_SIZE                            7       5120        640
UNIFORM_SIZE                            8       5120        640
UNIFORM_SIZE                            9       5120        640
UNIFORM_SIZE                           10       5120        640
UNIFORM_SIZE                           11       5120        640
UNIFORM_SIZE                           12       5120        640
UNIFORM_SIZE                           13       5120        640
UNIFORM_SIZE                           14       5120        640
UNIFORM_SIZE                           15       5120        640
UNIFORM_SIZE                           16       5120        640
UNIFORM_SIZE                           17       5120        640
UNIFORM_SIZE                           18       5120        640

19 rows selected.

As expected - each and every extent is 5MB, every extent ever allocated in this tablespace will be 5MB, not a byte more not a byte less.

I agree that I haven't tested on temporary LMTs, in fact i have been thinking about it... Can any one let me know how do i see the temporary extents , That would help me prove if my thinking is right or wrong.

Cheers

[sreddy]

I mean to say on TEMP TABLESPACES. I know its behaviour on PERM TABLESPACES. You don't need to show me test case.I just asked have you seen behaviour with your naked eyes

[adewri]

Originally posted by sreddy
I mean to say on TEMP TABLESPACES. I know its behaviour on PERM TABLESPACES. You don't need to show me test case.I just asked have you seen behaviour with your naked eyes

You got me, no i haven't seen but i assume that extent allocation would be same in both. Or am i wrong ??

[mike2000]

look like AUTOALLOCATE produce fragmentation on your tablespaces, below is a copy from Oracle in Metalink

From: Oracle, Ken Robinson 31-Mar-03 16:41 Subject: Re : Autoallocate vs Uniform Allocation Mark is correct, AUTOALLOCATE can produce fragmentation, using UNIFORM is a better option to eliminate fragmentation. A couple of places to start on this topic: Check in Metalink (a number of good threads posted in the past). Do a search using keyword "autoallocate" and/or "uniform". There is also some great information at http://asktom.oracle.com on this topic. Regards, Ken Robinson Oracle Server EE Analyst


Is that true LOCAL EXTENT using AUTOALLOCATE produce fragmentation????

[sreddy]

Originally posted by mike2000
Is that true LOCAL EXTENT using AUTOALLOCATE produce fragmentation????

TRUE, They are talking about PERMANENT LMTS not LMTS for TEMPORARY TABLESPACE. No issue of fragmentation for Temprary tablespace as tablespace contents are temporary.

Go with uniform allocation for Permanent and autoallocate option for temporary.

[mike2000]

so if it's the case, why Oracle had LOCAL EXTENT and AUTOALLOCATE which cause fragmentation, which return to the back old day when we use dictionary extent. I guess AUTOALLOCATE is for TEMPORARY not for permenant tablespaces, but if it's designed for TEMPORARY, why can't you use DICTIONARY extent for TEMPORARY tablespaces????

thanks

[adewri]

Originally posted by mike2000
so if it's the case, why Oracle had LOCAL EXTENT and AUTOALLOCATE which cause fragmentation, which return to the back old day when we use dictionary extent. I guess AUTOALLOCATE is for TEMPORARY not for permenant tablespaces, but if it's designed for TEMPORARY, why can't you use DICTIONARY extent for TEMPORARY tablespaces????

thanks

Use auto allocate when you don't know the size of the objects that will be created in the tablespace. So that smaller tables can have 64k or 1M extents ( that will save space, as compared to keeping smaller tables in a 20M uniformed sized extent LMT ). You can monitor the growth of the table and estimate the size of the tables and then move them accordingly to an new LMT having uniform sized extents.

Use Uniform sized LMT for tablespaces where you know the size of the objects.

[sreddy]

If you go back to couple of threads may be an year and year and half old, Jmodic talks about the internals of fragmentation and its effect on database which is minimal or very less.

So, even if there is fragmentation how oracle deals with in LMTs is different in DMTs. LMTs extents are based on Bitmap search and very effective(read LMTs concepts).

If I were you, I design my own way and make my own guidelines for which option(AUTOALLOCATE, UNIFORM) based on the kinda tables I have and their size). I do not use AUTOALLOCATE for LMTS on Permanent tablespaces and feel more comfortable creating SMALL,MEDIUM,LARGE tablespaces with reasonable UNIFORM ALLOCATION EXTENT and direct my tables and indexs to specific tablespace based on the size of the table/index and its growth.

There will always little pros/cons for everything, you have got to adopt which is good for environment you are dealing with...

[adewri]

Originally posted by mike2000
so if it's the case, why Oracle had LOCAL EXTENT and AUTOALLOCATE which cause fragmentation, which return to the back old day when we use dictionary extent. I guess AUTOALLOCATE is for TEMPORARY not for permenant tablespaces, but if it's designed for TEMPORARY, why can't you use DICTIONARY extent for TEMPORARY tablespaces????

thanks

Don't worry about fragmentation as in system managed LMT Oracle uses only a small number of distinct extent sizes, and the larger sizes are integer multiples of the lower sizes ie 64k,1M,8M,64M and so on. That way you never have any unusable fragments of free space as it will be used up no matter what (provided that they are free).

[mike2000]

Originally posted by adewri
Don't worry about fragmentation as in system managed LMT Oracle uses only a small number of distinct extent sizes, and the larger sizes are integer multiples of the lower sizes ie 64k,1M,8M,64M and so on. That way you never have any unusable fragments of free space as it will be used up no matter what (provided that they are free).

What do you mean by Oracle uses only a small number of dictinct extents sizes, and the larger sizes are interger multiples of low sizes??? Could you interpret a little more on this ???