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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 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