Home » RDBMS Server » Server Administration » Is there a optimal size for file next extent size? (11.2.0.4, windows server 2012 r2)
Is there a optimal size for file next extent size? [message #658285] Thu, 08 December 2016 04:10 Go to next message
juniordbanewbie
Messages: 250
Registered: April 2014
Senior Member
Dear all,


Is there a optimal size for file next extent size?
this is the ddl I obtained through dbms_metadata

CREATE TABLESPACE "PERFSTAT" DATAFILE
  'D:\APP\ORACLE\ORADATA\ORCL\PERFSTAT01.DBF' SIZE 5242880
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
   ALTER DATABASE DATAFILE
  'D:\APP\ORACLE\ORADATA\ORCL\PERFSTAT01.DBF' RESIZE 975241216;

it is quite obvious the initial script did not specified the next block size. from http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses004.htm#SQLRF01602

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. The default is the size of one data block.

default db block size will be defaulted to 8192 or 8k (https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams050.htm#REFRN10031)

This means that if the tablespace run out of free extents and need to extent 100M it will need to extend by 100M/8K, i.e. 128 times

so is there a optimal size for next extent value?

from https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1960884062300

Quote:


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.
while I totally agree with the guru, Tom Kyte on file next extent size should be a multiple of my extent size, I do not know why he suggested a size of 8mb for autoallocate scheme.

any one know the reason why?

thanks a lot!
Re: Is there a optimal size for file next extent size? [message #658287 is a reply to message #658285] Thu, 08 December 2016 04:35 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can only tell you what I do.

Nine times out of ten, use autoallocate which is what you get by default. The only problems are (a) for a large and growing table, it takes a short time for the extent sizes to ramp up to something sensible, and (b) if using ASM, the initial small extents will not map to an allocation unit (default size 1M)

The tenth time, I use uniform extent size. Put large objects in a tablespace with (for example) uniform size 64M, small object in a tablespace uniform size 1M.

How important is it? Possibly not at all.
Previous Topic: Validating external libraries in oracle 11g - AIX
Next Topic: Reclaim space from tablespace to file system
Goto Forum:
  


Current Time: Thu Mar 28 07:07:26 CDT 2024