confused about temp tablespace in local extent

I am a bit confused about the temp tablespace in local extent.

below is the script of my temp tablespace in local extent:

CREATE TEMPORARY TABLESPACE TEMP_01
TEMPFILE 'D:\ORADATA\HUOXYD01\TEMP_01.DBF' SIZE 61440K AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K

/

ok what happened if the user need more than 60M??? can we set the autoextend on for TEMP tablespace??? It seem like we can't b/c I tried the below scripts and it still set autoextend of after I run the script below:

ALTER DATABASE TEMPFILE 'D:\ORADATA\HUOXYD01\TEMP_01.DBF' AUTOEXTEND ON NEXT 10240K MAXSIZE 2048000K
/

So if I can't set the autoextend on, then I have to allocate a certain space for temp tablespaces??? which mean that I have to use a lot of spaces for TEMP tablespaces????

Please correct me if I am wrong and give me some advises

SQL> create temporary tablespace t1
2 tempfile 'e:\oracle\oradata\nt817\t101.dbf'
3 size 10M autoextend on next 10M maxsize 40M
4 extent management local uniform size 1M;

Tablespace created.

BTW its not a good idea to have temporary tablespace with uniform sized extents. Let it be system managed (autoallocate) LMT.

Quote:

Originally posted by adewri
BTW its not a good idea to have temporary tablespace with extent management local uniform size. Let it be system managed LMT.

Why?

Because if i have a Temporary LMT with uniform size say 10M. Then even for a small sort operation requiring say 64k of space an extent of 10M will be allocated.

Where as in system managed LMT the first few extents allocated would of 64k, then 1M, then 8M and so on. So for a small sort of 64k only one extent of 64k will be allocated instead on 10M as in case of uniform sized Temporary LMT. And of course for larger sorts the extent size will grow accordingly from 64k to 1M to 8M and so on.

Hi Amar,

The recommendation I'm working to is to have the uniform extent size equal to the SORT_AREA_SIZE - so your example will not arise. (I think that makes sense.)

Hi Dapi,

But in 9i where i'm using workarea_size_policy=auto and setting a value of pga_aggregate_target. Then the sort_area_size wont come into picture as the amount of space to be allocated would be controlled by ORACLE automatically depending upon the value of pga_aggregate_target and the amount of memory required for sorting.

Cheers...

PS: Advices are welcome as i'm still testing on these grounds.

Quote:

Originally posted by adewri
Where as in system managed LMT the first few extents allocated would of 64k, then 1M, then 8M and so on. So for a small sort of 64k only one extent of 64k will be allocated instead on 10M as in case of uniform sized Temporary LMT. And of course for larger sorts the extent size will grow accordingly from 64k to 1M to 8M and so on.

Did you test this behaviour or you are talking on theoritical/documentation grounds ?

Quote:

Originally posted by adewri
But in 9i ....

Ah! Some of us are still carrying a 7.3.4 CD in our pockets as a good luck talisman - just in case 8i doesn't deliver!

Quote:

Originally posted by DaPi
Ah! Some of us are still carrying a 7.3.4 CD

;) I would say 7.1 not even stable 7.3.4. Just saw somebody here today morning... who lost last night sleep with some issues .

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

I can take that Q for Amar:

That means no matter how big/small table you create, Oracle creates in multiple of distinct extent sizes mentioned(64K,1M,8M,64M).

In practical terms:

If you create 512K table in LMT with AUTOALLOCATE it uses 64K

If you use 10M , it uses IM extent size and if you use 50M, it uses 8M size and so on.

In first case 8 extents, second case 10 extents, third case 7 extents as opposed to
6+partial extent for 2MB.

Quote:

Originally posted by sreddy
I can take that Q for Amar:

That means no matter how big/small table you create, Oracle creates in multiple of distinct extent sizes mentioned(64K,1M,8M,64M).

In practical terms:

If you create 512K table in LMT with AUTOALLOCATE it uses 64K

If you use 10M , it uses IM extent size and if you use 50M, it uses 8M size and so on.

In first case 8 extents, second case 10 extents, third case 7 extents as opposed to
6+partial extent for 2MB.

Well, this is only the first (less important, IMHO) part of the autoallocate process. The basic idea is not about the initial extent sizes when table is created, but the adoption of next extent sizes when segment grows. Roughly, the basic idea is something like the following:

- Untill your table/index is less than 1MB in size, oracle uses 64K extents for it.
- Once the segment grows beyond 1MB, oracle will start allocating larger extents for it, namely 1MB. So suppose your table is occupying 16 extents of 64KB and all your extents are full, when it needs more space for additional data, it will allocate 17th extent, but this time it will be of size 1MB.
- Once your segment grows beyong 64MB, the follwing extents allocated will be of size 8MB. So continuing from our previous example, once the table size reaches 64MB (16 extents of 64KB each plus 63 extents of 1MB), the size of next extents used will be 8MB.
- Once that table grow beyongd 1GB, the remaining extents allocated will be of size 64MB. So continuing with our previos example, once the table grows to 1GB (16 extents of 64KB each plus 63 extents of 1MB plus 120 extents of 8MB), all the following extents allocated for that table will be of size 64MB.

Now that is *the general theory*, but in praxis and with different releases you could notice some of the "distortions" from that theory. Here is the quote from Jonathan Lewis that discusses his own finding about how AUTOALLOCATE behaves in praxis (the original question was about how AUTOALLOCATE algorithm works and if it has been officialy published by Oracle Corp.):

Quote:

From: Jonathan Lewis 05-Feb-03 08:33
Subject: Re : LMT Autoallocate Algorithm

I doubt if an algorithm will ever be published.
Every time someone asks the question the official
reply is that it is internal. This does mean that
it can change to address the possible honey-comb
effect you describe.

One minor detail - your experiments on sizes and rates
look as if they were based largely on a nice clean
tablespace. Oddities can happen (if in a clean start).
For example, try creating a table with (initial 64M) -
Oracle will create it with no 64K extents, and 64 x 1M
extents. Do the same with (initial 65M) and 8.1.7.4
and 9.2.0.2 will both create the first extent at 8M.
the next 40 something at 1M, and a couple more at 8M.

This makes it rather hard to guarantee that moving
a table, or exp/imp on a table will manage to fill
any funny little holes - possibly everything you
move will start allocating at larger sizes.

On the plus side - extents of size X are allocated on
a boundary matching X - in other words, they always
start in 'the right place' for their size. This does
reduce the probability of excessive honey-combing if
you have an early period of dropping and recreating
objects in the tablespace before things stabilise.

Jonathan Lewis
Author: Practical Oracle 8i

A rectification from my side.

You cannot create SYSTEM managed LMT ie cannot use autoallocate for a Temporary LMT it has to be uniform and will be uniform.

HTH

You can create system LMT in 9.2.0.1

Quote:

Originally posted by pando
You can create system LMT in 9.2.0.1

No you cannot :D

Code:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production lsc@LNX920-RAC1> lsc@LNX920-RAC1>select tablespace_name, extent_management from dba_tablespaces; TABLESPACE_NAME EXTENT_MAN ------------------------------ ---------- SYSTEM LOCAL UNDOTBS1 LOCAL TEMP LOCAL DRSYS LOCAL EXAMPLE LOCAL INDX LOCAL TOOLS LOCAL USERS LOCAL XDB LOCAL

Quote:

Originally posted by pando

Code:

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production lsc@LNX920-RAC1> lsc@LNX920-RAC1>select tablespace_name, extent_management from dba_tablespaces; TABLESPACE_NAME EXTENT_MAN ------------------------------ ---------- SYSTEM LOCAL UNDOTBS1 LOCAL TEMP LOCAL DRSYS LOCAL EXAMPLE LOCAL INDX LOCAL TOOLS LOCAL USERS LOCAL XDB LOCAL

I was think in the same line before. :D

Check the ALLOCATION_TYPE :D its uniform always and not system.

well I think we have some missunderstandings, I thought you was saying SYSTEM tablespace but it seems that you meant AUTOALLOCATE?

Quote:

Originally posted by pando
well I think we have some missunderstandings, I thought you was saying SYSTEM tablespace but it seems that you meant AUTOALLOCATE?

Yes, earlier i had suggested, if you read the thread, to use SYSTEM managed Temporary tablespaces ie autoallocate.

But while testing i saw that each and every extent was 1M in size for the Teporary LMT even if uniform size is not mentioned.

While for a permanent tablespace even if you do not mention uniform size or autoallocate clause in create tablespace command it creates a system managed LMT.

So i thought i would rectify my suggestions :)