Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jeff.

Asked: November 13, 2001 - 12:38 pm UTC

Last updated: October 08, 2009 - 6:37 am UTC

Version: 9.0.1.0

Viewed 1000+ times

You Asked

I am trying to understand how AUTOEXTEND works with Locally Managed Tablespaces. For example, I create a tablespace that I want to store tables with 320K extents. I size the initial datafile for the LMT Bitmap (64K) and 10 extents (3200K). The tablespace definition is:
create tablespace test_lmt
datafile '/u01/oradata/dev901/test_lmt01.dbf'
size 3264K
autoextend on next 1M maxsize 20M
extent management local uniform size 320K;

system@dev901.us> select file_name, tablespace_name, bytes/1024 k
2 from dba_data_files;

FILE_NAME TABLESPACE_NAME K
----------------------------------- --------------- ----------
/u01/oradata/dev901/test_lmt01.dbf TEST_LMT 3264


I then create an object in that tablespace with 11 extents so I know that the datafile will autoextend:

create table xyz (x number, y number, z number)
tablespace test_lmt storage (minextents 11);

If I query dba_data_files again, it shows I have 4544K in my data file and not 4288 or 5312 like I would expect because the data file extends by 1M.

system@dev901.us> select file_name, tablespace_name, bytes/1024 k
2 from dba_data_files;

FILE_NAME TABLESPACE_NAME K
---------------------------------- --------------- ----------
/u01/oradata/dev901/test_lmt01.dbf TEST_LMT 4544

My table, indeed, has 11 extents, all 320K:
system@dev901.us> l
1 select bytes/1024 k
2 from dba_extents
3* where tablespace_name = 'TEST_LMT'
system@dev901.us> /

K
----------
320
320
320
320
320
320
320
320
320
320
320

11 rows selected.

Why does the datafile extend NOT by the AUTOEXTEND NEXT value?

and we said...

It does not make sense to autoextend by 1m - it would leave 2/10 of the file unusable at the end.

If you notice,

1024/320 = 3.2 (thats what you asked for, 3.2 MORE extents)
ceil(3.2) = 4 (it wants to have uniform extents)
4*320 = 1280 (the difference between 3264 and 4544)

It does not do what you asked since what you asked is not logical given the uniform sizing strategy.

I'll agree that the doc is a little hazy on this. The SQL ref says NEXT is the size of the next chunk added, the admin guide states:

....
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can
automatically extend.
....



Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jeff Hunter, November 13, 2001 - 2:38 pm UTC

Ah, "NEXT is the minimum size" is the part I missed. Thanks!

Excellent explanation

Jimmy Lo, December 07, 2001 - 11:21 am UTC

However, I have a question regarding
"It does not make sense to autoextend by 1m - it would leave 2/10 of the file unusable at the end."

1. How do you come up this number (2/10)? and why is it unusable?

2. It doesn't make sense to make autoextend by 1M,
but doesn't Oracle fixes it automatically? ( in this case the 1st extended chunck is 1280K)
and all 1280K (4 320K extents) is usable, right?

Thank you!




Tom Kyte
December 07, 2001 - 2:15 pm UTC

1) your right, its not 2/10s, its 40k. There would be 40k at the end of the file that couldn't be used.

2) yes, that is what this demonstrated here -- we rounded the autoextend UP to accomidate the LMT extent size.

Just need a little more clarification

Jimmy Lo, December 07, 2001 - 4:34 pm UTC

Tom, thanks for your prompt response.
Continue on your follow up:

1. 1M=1024K, so it should be 64K left
(1024K - 3x320K)

2. It's not reasonalbe to extend on 1M. Because if Oracle really allocates 1M, there will be 64K left.
But Oracle WILL NOT allocate 1M, instead it will allocate 1280K (320K x4).

If the above are true. That means in this example, there will NOT be any unusable space.

I guess I'm more confused by the word 'unusable'.
In a LMT environment, if the uniform size is specified,
the datafile will extend as what you explained above.
I don't see any unusable space.
If uniform size is not specified,
let's suppose initial and next are also 320K , and
PCTINCREASE=0. let's do the same test again.
when the tablespace extends, oracle will extends exactly 1M.
The new table will have the same 11 extents.
but the datafile(or tablespace) will have 3264K + 1M
we can increase the number of extents of the table we just careated to 13, then we'll have 64K left
and this 64KB can be used
1. for other objects in the same tablespace with a smaller extent size
2. for the same table, but oracle has to extend the size of the datafile(tablespace) first.
Are these correct?
If they are correct, the 64K will always be usable, it's just a matter of how
If my statement aren't correct, would you explain more about the concept of 'unusable'?

Thank you so much for your time!

Tom Kyte
December 07, 2001 - 6:35 pm UTC

That is right, there will not be any unusable space -- because we don't allow the 1m extension, we bumped it up.

I was just pointing out that 1m was not a sensible size, we adjusted it, that is all.

Yes, you are correct in the other assumptions with regards to dictionary managed tablespaces.

With uniform LMTS, unless the file is 64k + N * uniform_extent_size, there is some space in that file that cannot be used (unless the file gets larger). As the database knows that space cannot be used -- it will round up the allocations to avoid having to re-extend the file (to gain yet more space that cannot be used until it again grows the file). Its just being a little bit smarter about how it allocates the file.

can u explain more

umesh, February 27, 2003 - 3:56 am UTC

I could not follow the calculations 1024/320

1.How do I calculate the minimum number of extents allocate used for t.space extension here

2.But another review says as 1024 - 3*320 and how is that done please explain
thanks for your patience

Tom Kyte
February 27, 2003 - 6:58 pm UTC

320 = amount in k of an extent in this tablespace:

create tablespace test_lmt
datafile '/u01/oradata/dev901/test_lmt01.dbf'
size 3264K
autoextend on next 1M maxsize 20M
extent management local uniform size 320K;

1024 = amount in k of requested autoextend file size (1m)


1) don't understand what you mean. A segment will request some number of extents -- a tablespace doesn't....


2) they are just showing there would be 64k left over (which is still NOT 320k hence same issue....)

To be clear

Drazen, October 04, 2005 - 3:53 am UTC

We've purchased an application that works on Oracle database. Company that installed application also manages Oracle database. What do you think about the settings of this tablespace? Will there be unused space or will Oracle manage to use all space required? All objects in this tablespace have initial and next extents of 99M.


CREATE TABLESPACE acc DATAFILE
'/data1/oradata/acc/acc_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
'/data1/oradata/acc/acc_05.dbf' SIZE 1486M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
'/data2/oradata/acc/acc_02.dbf' SIZE 496M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
'/data3/oradata/acc/acc_03.dbf' SIZE 298M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M,
'/data4/oradata/acc/acc_04.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 99M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;

Tom Kyte
October 04, 2005 - 2:16 pm UTC

the size should be 99mb + 64k and next could be 99m - for all space to be useable.

right now, there is 1m-64k of space that will never be used in the first file for example.

Not a big deal (use 128k if you have 32k blocksize, 64k for 16k and smaller blocks - overhead used to manage the bitmap for the file)

Tablespace size for best utilization

Syed, October 04, 2005 - 5:44 pm UTC

Hi Tom,
I need to know what should be the best size for tablespace sizing, for small, medium and large tables, e.g., in my environment, I have tables with these number of rows:
TOTAL 4269 tables
100 thousand+ rows 709
1 million+ rows 361
10 million+ rows 72
100 million+ rows 2
the db size is 3.5TB (allocated), but used is only 50% (1.5TB), we are not utilizing our space appropriately, can you please suggest what should be the extent size for best utilization of space, db block is 8092, we are Oracle9ir2 on solaris.
Thanks a lot as always for your help.

Tom Kyte
October 04, 2005 - 8:46 pm UTC

use system allocated extents maybe....

number of rows isn't meaningful,

size of data is
growth of data is


why are you not using your space appropriately? (think you mean 8096, 8k...)

Uniform extents and autoextend

A reader, September 07, 2007 - 10:30 am UTC

Tom,
I have a database with LMTs. Some of the tablespaces have uniform extent size of 4M. The datafiles for these tablespaces are set to autoextend by 8k (block size=8k). My question is that is there a relation between how much I should autoextend my datafile depending upon uniform extent size? In case of 4M extents, to allocate a single extent, the datafile might have to autoextend 512 times. Would it make more sense to have the datafile autoextend by say 1M or does it not matter? Database 10.2.0.3.

Thanks

Tom Kyte
September 12, 2007 - 10:12 am UTC

they should autoextend by 4m of course, that is the minimum size we'll extend by.

A reader, September 30, 2007 - 4:12 pm UTC

Tom,

In oracle 10g, when tablespace is calculated, some tablespaces show as 99% used whereas actually they are only 57% used here.. Does that have any overhead when it stays at 99%, it will autoextend numerous times every day. why not it indicate and stay at actual size and only autoextend every 15 days or 1 month etc.
Tom Kyte
October 03, 2007 - 2:17 pm UTC

eh? No idea what this means:

some tablespaces show as 99% used
whereas actually they are only 57% used here..

How many extends

A reader, June 10, 2009 - 7:42 am UTC

Hi Tom,

Given the definition of the tablespace to be

CREATE TABLESPACE "TRG_DAILY_DATA_20090607" DATAFILE
'/mnt/TRG_data1/BUSINESS/TRG_DAILY_DATA_20090607_01.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data2/BUSINESS/TRG_DAILY_DATA_20090607_02.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data3/BUSINESS/TRG_DAILY_DATA_20090607_03.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data4/BUSINESS/TRG_DAILY_DATA_20090607_04.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data5/BUSINESS/TRG_DAILY_DATA_20090607_05.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data6/BUSINESS/TRG_DAILY_DATA_20090607_06.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M,
'/mnt/TRG_data7/BUSINESS/TRG_DAILY_DATA_20090607_07.dbf' SIZE 7340032000
AUTOEXTEND ON NEXT 32768 MAXSIZE 32000M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4194304 SEGMENT SPACE MANAGEMENT AUTO

and growth of the datafiles after the load has happened is

File_Name Bytes Create_bytes Diff in (GB)
/mnt/trg_data1/BUSINESS/trg_DAILY_DATA_20090607_01.dbf 15074328576 7340032000 7.203125
/mnt/trg_data2/BUSINESS/trg_DAILY_DATA_20090607_02.dbf 15074328576 7340032000 7.203125
/mnt/trg_data3/BUSINESS/trg_DAILY_DATA_20090607_03.dbf 15086911488 7340032000 7.21484375
/mnt/trg_data4/BUSINESS/trg_DAILY_DATA_20090607_04.dbf 15044968448 7340032000 7.17578125
/mnt/trg_data5/BUSINESS/trg_DAILY_DATA_20090607_05.dbf 15099494400 7340032000 7.2265625
/mnt/trg_data6/BUSINESS/trg_DAILY_DATA_20090607_06.dbf 15065939968 7340032000 7.1953125
/mnt/trg_data7/BUSINESS/trg_DAILY_DATA_20090607_07.dbf 15040774144 7340032000 7.171875

How many times did a datafile extend given the NEXT clause based on the ddl definition of the tablespace.

Is it (bytes - create_bytes)/32K as my datafile can extend by 32K at one time
OR
to create a uniform extent size of 4M does it extend by (bytes - create_bytes)/4M

Also does each extention at OS level means a system call?

Thanks
Tom Kyte
June 10, 2009 - 2:39 pm UTC

it would be a mistake on your part to make the autoextend size 32k in a tablespace where the minimum (and maximum) extent size is 4mb. It doesn't make sense.

We don't track the "extensions" of a datafile, suffice it to say - anytime a new extent was needed, we had to allocate 4mb - not your 32k. The only thing 32k could have done for you would be to slow down the process (you'd have to truss the dedicated server doing the file extends to see what calls it makes on your OS)


All I can say is:

a) if you use uniform - make the autoextend size equal or an integer multiple of your extent size. Nothing else - repeat *NOTHING ELSE* - makes any sense at all.

b) don't use uniform - use system allocated extents. Then a size of 8mb (or multiples thereof) would make sense over time.

Scofield, September 20, 2009 - 4:13 am UTC

Respecter Sir;

I know that If I specify "autoextend on", the datafile will extend 1 block by default.

Assume the datafile is full, and segments are extended uniformly. (100mb).
If I need to extend the segment, Does oracle try to extend the datafile 1 block and then 1 block ,and so on....
When 100m is available (by extending 1block by 1block), new extent will be allocated..Is that right?
Tom Kyte
September 28, 2009 - 12:47 pm UTC

...I know that If I specify "autoextend on", the datafile will extend 1 block by
default.
....

not true in current releases.

ops$tkyte%ORA10GR2> create tablespace tt1 datafile '/tmp/tt1.dbf' size 1m autoextend on;

Tablespace created.

ops$tkyte%ORA10GR2> create tablespace tt2 datafile size 1m autoextend on;

Tablespace created.

ops$tkyte%ORA10GR2> create tablespace tt3 datafile size 150m autoextend on;

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select tablespace_name, file_name, increment_by from dba_data_files where tablespace_name like 'TT_';

TABLESPACE_NAME                FILE_NAME                      INCREMENT_BY
------------------------------ ------------------------------ ------------
TT1                            /tmp/tt1.dbf                              1
TT2                            /home/ora10gr2/oradata/ora10gr          128
                               2/ORA10GR2/datafile/o1_mf_tt2_
                               5d1w4c2d_.dbf

TT3                            /home/ora10gr2/oradata/ora10gr        12800
                               2/ORA10GR2/datafile/o1_mf_tt3_
                               5d1w4cgh_.dbf



for Oracle Managed Files (OMF), the default autoextend size is the minimum of 100M or the size of datafile.



And it'll will autoextend in a single call - using the INCREMENT_BY in multiples.

you can see this (my blocksize is 8k) by doing something like:

ops$tkyte%ORA10GR2> create tablespace tt1 datafile '/tmp/tt1.dbf' size 1m autoextend on
  2  extent management local uniform size 64k;

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x number, y char(2000) ) tablespace tt1;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_start number;
  3          l_curr  number;
  4  begin
  5          select bytes into l_start
  6            from dba_data_files
  7           where file_name = '/tmp/tt1.dbf';
  8
  9          dbms_output.put_line( 'start size ' || l_start );
 10
 11          for i in 1 .. 330
 12          loop
 13                  insert into t values ( i, 'x' );
 14                  commit;
 15          end loop;
 16  end;
 17  /
start size 1048576

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>         select bytes
  2            from dba_data_files
  3           where file_name = '/tmp/tt1.dbf';

     BYTES
----------
   1048576

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @getspid
ops$tkyte%ORA10GR2> select a.spid dedicated_server,
  2             b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5     and b.sid = (select sid from v$mystat where rownum=1)
  6  /

DEDICATED_SE CLIENTPID
------------ ------------
22604        614

ops$tkyte%ORA10GR2> pause

ops$tkyte%ORA10GR2> insert into t values ( 331, 'x' );

1 row created.




I know (by testing) that 330 rows fit in the 1m datafile, 331 rows would need to autoextend by 64k (my uniform size)


when I paused, I attached to my dedicated server (it'll do the file resize) with strace.

I observed:

stat64("/tmp/tt1.dbf", {st_mode=S_IFREG|0660, st_size=1056768, ...}) = 0
statfs("/tmp/tt1.dbf", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=34430738, f_bfree=20666604, f_bavail=18917612, f_files=17498112, f_ffree=16974608, f_fsid={0, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/tmp/tt1.dbf", O_RDWR|O_LARGEFILE) = 19
lseek(19, 0, SEEK_SET)                  = 0
write(19, "\0\242\0\0\0\0\300\377\0\0\0\0\0\0\0\0\376\372\0\0\0 \0"..., 8192) = 8192
ftruncate64(19, 1122304)                = 0
close(19)                               = 0


ftruncate64 can grow or shrink a file, it grew the file by 64k - the next extent size.

John Carew, October 05, 2009 - 2:09 am UTC

Hi Tom
I have also some doubts about the above question.

Regarding the datafile, If I specify autoextend on,and omit next clause,by default the datafile will extend 1 block.(not OMF)

Consider the datafile is full and segments are extended uniformly or autoallocate.

When oracle attempts to extend the segment, it has to extend datafile 1 block.and then 1 block until autoallocate or uniform size.
and Finally new extent will be allocated to segment.

Is that right? If it is right this will yield poor performace issues, am I right Tom?


Tom Kyte
October 08, 2009 - 6:37 am UTC

but I just demonstrated above that it will do the resize in one call - it would not make lots of one block increases - it would figure out "hey, I need 1mb of space - I better grow by 1mb - in 1 block units"

meaning if the next size was .75mb - you would end up with 1.5mb of new space - allocated in a single call.

A reader, October 08, 2009 - 8:52 am UTC

Greetings Tom,


What I understood from your example to "scofield" is

when there is a need to autoextend the file Oracle extend it by the size of extent (for UNIFORM size)
and for autoallocate what ever size come up as per autoallocate algorithm in one system call

Please correct me if I am wrong?


Regard's

John Carew, October 10, 2009 - 2:20 am UTC

According to your demostration:
Is this assumption correct?
If I specify autoextend on,and omit next clause (not OMF).

Oracle extend the datafile by the size of extent (for UNIFORM size)
and for autoallocate what ever size come up as per autoallocate algorithm.
and it never extents 1block,1block,etc..

Is this right Tom?