Sizing segments after more than 3 years of DB use.

[MMCP]

Hi pals!, I had read that the idea is: "that segments have as few extents as possible (less than 5) to avoid object fragmentation, and the INITIAL have the most possible amount of data".
If it's so, what happen when the object is not small, for example: We have an index with a size= 38 MB, initial=19 MB, next= 9 MB, pctincrease=0 and extents=3 .
The related table's size=75 MB, initial=9 MB, next= 37 MB, extents=6, pctincrease=0 and the sum of all its fields = 130 bytes.

Analizing this, we don't have problems about the number of extents (3 and 6), but we're concerned about the NEXT clause, because we think: are reserving too much space for this segments, that others could use. Don't you thing so ?

So, do you know what is the best way to calculate the optimal storage clause (initial,next, pctincrease) for indexes and tables, after more than "x" years of a production DB use? Any formula ?

Thanks in advance.

[slimdave]

You are suffering from bad advice. There is no problem with extents-per-segmentbeing well over 100.

the key is to keep all extents within a tablespace the same size. ideally this means using locally managed tablespaces with uniform extent sizes, but in older versions of oracle just means that you specify initial=next, pctincrease=0 at the TS level and do not specify anything for each table/index.

[tamilselvan]

The next 4 rules (guidelines) can help you in determing the size of an extent for an object:
1 Keep the extent's size greater than 64K. A single I/O can fetch max of 64K data only on most the systems.
2 Ensure that all extents are of same size within a tablespace.
3 Do allow more than 100 extents for any object.
4 Large table must have its own tablespace.

Tamilselvan

[bensmail]

The Extends should be equal:

n*db_block_size*db_file_multiblock_read_count where n depend on the size of ur object.
and the object should not h've more than 1024 Extends.

Bensmail

[abhaysk]

Originally posted by bensmail
the object should not h've more than 1024 Extends.

??

PS the debate on no of extents in the thread...

http://www.dbasupport.com/forums/sho...threadid=29593

[MMCP]

Thanks pals, i'll read more about Locally Managed Tablespaces, because despite we have Oracle 8.1.5.0, the database was created without using the Oracle 8i new features. Was created as an Oracle 8.0 database.

[SANJAY_G]

Originally posted by tamilselvan
1 Keep the extent's size greater than 64K. A single I/O can fetch max of 64K data only on most the systems.

Hi tamilselvan,
I didn't get it, can you elaborate on this?

[tamilselvan]

Most of the UNIX Operating systems cannot support more than 64K data to be read from disk and write on to disk in a single disk I/O call. Even if you set a high value for DB_FILE_MULTIBLOCK_READ_COUNT, OS will revert back to 64K only.