confused about temp tablespace in local extent

Quote:

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.

Code:

[email protected]D> create tablespace SYSTEM_MANAGED datafile 2 ‘e:\oracle\oradata\orcl\test01.dbf’ size 50M 3 extent management local; Tablespace created. [email protected]D> create table big_table_system tablespace SYSTEM_MANAGED as select * from scott.voyages; Table created. [email protected]D> 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.

Code:

[email protected]D> create tablespace UNIFORM_SIZE datafile 2 'e:\oracle\oradata\orcl\test01.dbf' size 100M 3 Extent management local 4 Uniform size 5M 5 / [email protected]D> create table big_table_uniform tablespace UNIFORM_SIZE as select * from scott.rd_voyages; Table created. [email protected]D> 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

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

Quote:

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

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

Quote:

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.

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

Quote:

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.

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

Quote:

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

Quote:

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