Optimal Extent Size for large tables

Hi,
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.

Thanks
orauser090601Asked:
Who is Participating?
 
myerpzCommented:
hi
to avoid fragmentation, check out this link :-

http://technet.oracle.com/deploy/performance/pdf/defrag.pdf

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
0
 
UsamaMunirCommented:
Put some points on The question Buddy!
0
 
orauser090601Author Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
orauser090601Author Commented:
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
0
 
oswaldocastroCommented:
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.

HIH

Oswaldo Castro
0
 
orauser090601Author Commented:
Thanks a lot myerpz.. your recommendations are a good help. Let me sugeest this to our DBA and see the result.
0
 
myerpzCommented:
any chance of some points ?!
;-)
0
 
orauser090601Author Commented:
Sure !! But how do i set it?!! Pl. Tell... !!!
Also thanks for helping me when there were no points on the Q!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.