Optimal Extent Size for large tables

Posted on 2001-09-07
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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

920 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now