Go Premium for a chance to win a PS4. Enter to Win


Optimal Extent Size for large tables

Posted on 2001-09-07
Medium Priority
Last Modified: 2011-09-20
I have huge tables in my database (eg 10MB..) What would be the optimal size for extents for such tables. The database is relational and doesn't have star schema etc. The data is loaded everyday by SQLLoader and the tables are used extensively for querying purpose.

Presently the tables occupy 20-40 extents of 2 MB. Is it advisable to increase teh extent size and if so, what other parameters should also be set such that the database remains tuned.

Question by:orauser090601

Expert Comment

ID: 6463275
Put some points on The question Buddy!

Author Comment

ID: 6463300
Sorry Buddy,
No points!! I don't understand the points funda...I had put 50 points on it and the app failed to put up my Q... just hoping a kind hearted person may give me the precise answer

Author Comment

ID: 6463301
Sorry Buddy,
No points!! I don't understand the points funda...I had put 50 points on it and the app failed to put up my Q... just hoping a kind hearted person may give me the precise answer
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Expert Comment

ID: 6463667
Hi orauser

If you know how much your table will grow (daily, weekly, monthly, ...) you can make the initial extent and the next extent adjusted to that. For example, say you have to keep 1 year of records. Your table at the end of this period will be 600 MB in size. If you have sufficient space make the initial extent with 600 MB and the next extent with the amount needed to each month, maybe 50 MB with pctincrease set to 1. Look this is only a sugestion. A "perfect" tuned database (if this is possible at all) takes into account many other parameters.


Oswaldo Castro

Accepted Solution

myerpz earned 0 total points
ID: 6464167
to avoid fragmentation, check out this link :-


The article makes the following 12 recommendations :-

1. Use uniform extent sizes throughout a tablespace.
2. Always specify storage clauses at tablespace level only. PCTINCREASE should always be 0.
3. Use extent sizes of 128kb, 4Mb and 128Mb and no other.
4. Monitor and re-locate segments which have > 1024 extents.
5. The maximum single segent size should be somewhere between 4Gb and 128Gb.
6. Place very large tables and indexes in a private tablespace.
7. Temp segments should only be kept in TEMP tablespaces.
8. Rollback segments should only be kept in ROLLBACK tablespaces.
9. TEMP and UNDO tablespaces should contain between 1024 and 4096 extents.
10. Never put user data in the SYSTEM tablespace.
11. Data files should be a multiple of the tablespace extent size, plus 1 block.
12. Never defragment the space within a uniform-extent tablespace.

hope this helps

Author Comment

ID: 6473542
Thanks a lot myerpz.. your recommendations are a good help. Let me sugeest this to our DBA and see the result.

Expert Comment

ID: 6473879
any chance of some points ?!

Author Comment

ID: 6473887
Sure !! But how do i set it?!! Pl. Tell... !!!
Also thanks for helping me when there were no points on the Q!!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question