Solved

Optimal Extent Size for large tables

Posted on 2001-09-07
8
644 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:orauser090601
8 Comments
 
LVL 3

Expert Comment

by:UsamaMunir
Comment Utility
Put some points on The question Buddy!
0
 

Author Comment

by:orauser090601
Comment Utility
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
 

Author Comment

by:orauser090601
Comment Utility
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
 

Expert Comment

by:oswaldocastro
Comment Utility
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
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.

 
LVL 3

Accepted Solution

by:
myerpz earned 0 total points
Comment Utility
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
 

Author Comment

by:orauser090601
Comment Utility
Thanks a lot myerpz.. your recommendations are a good help. Let me sugeest this to our DBA and see the result.
0
 
LVL 3

Expert Comment

by:myerpz
Comment Utility
any chance of some points ?!
;-)
0
 

Author Comment

by:orauser090601
Comment Utility
Sure !! But how do i set it?!! Pl. Tell... !!!
Also thanks for helping me when there were no points on the Q!!
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

9 Experts available now in Live!

Get 1:1 Help Now