| Is there a optimal size for file next extent size? [message #658285] |
Thu, 08 December 2016 04:10  |
 |
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  |
John Watson
Messages: 8960
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
