Amount of segments per tablespace

[hacketta1]

Hello All

I am currently planning migrating my 8.1.7.3 database (soon to be 8.1.7.4) on HP-UX 11 64bit to locally managed tablespaces with uniform extent allocation (create new tablespace and move all segments to the appropriate new tablespace). I plan to have a small, medium, large, and huge tablespace for each application schema (JD Edwards OneWorld XE). Any segments that exceed 'huge' will go to it's own tablespace.

Segments up to 50 Mb - 1 Mb Extents - SMALL
Segments up to 500 Mb - 10 Mb Extents - MEDIUM
Segments up to 5000 Mb - 100 Mb Extents - LARGE
Segments up to 50000 Mb - 1000 Mb Extents - HUGE

DB_BLOCK_SIZE is 8K and DB_FILE_MULTIBLOCK_READ_COUNT IS 8K, so each extent size is a multiple of 64 Kb.

I've had a look at how many segments each tablespace will contain after the re-org, and some of the SMALL tablespaces will have over 6000 segments (a lot of these will have no data). My question is are there any known issues with having a large number of segments in a single tablespace. I'm not aware of having heard of any but I'm just wondering.

I know there is an an argument for limiting the number of segments per tablespace so it is easier to balance IO, but I don't think this applies in my case. The database sits on an EMC Symetrix disk array with raid 0+1 with a SAME configuration e.g. all files are striped and across all available spindles. Therefore, if the HP-UX max. datafile size permits, I'll just create one datafile per tablespace.

Any advice appreciated

Thanks

Austin

[slimdave]

Your setup sounds fine (don't worry about the numberofsegments per TS) but you might like to think again about having single datafiles per TS. It might provetobe inconvenientin the future to only be able to move data at an o/s level in such large chunks.

[ljayanth]

Hi hacketta1,

Ur setup is gud. set db_file_multiblock_read_count to 16 as 89 is default. but having all the data in one datafile is bad idea.
did u segregate the index, rollback segments and temp segments into other TS?? if not its again a bad practice. need not worry abt number of segments as u have db_block_size as 8K, cos 8K is less very size.

Did i asnwer ur question?
Jayanth

[slimdave]

Originally posted by ljayanth
did u segregate the index, rollback segments and temp segments into other TS?? if not its again a bad practice.

segregate indexes? rubbish.

[ljayanth]

Mr. self inventor,

i mean to seperate the indexws to another tablespace.

plz watch urself before you mail. ur mail may offend others.

[davey23uk]

why do you want to separate indexes to another tablespace, its complete rubbish and been proved many times to be so.

What is you reasoning to separate them?

[slimdave]

Originally posted by ljayanth
Mr. self inventor,

i mean to seperate the indexws to another tablespace.

plz watch urself before you mail. ur mail may offend others.

Mr Myth Believer,

Please list the benefits of separating indexes to a different tablespace.

Please don't be offended -- take it as an opportunity to learn.

[rajabalachandra]

hi,

if the data and index are on different
spindle it will increase the performance.

obviously it should be in different datafiles.
same tablespace or different tablespace no matter.

if it is in same tablespace there is a chance
of allocating into the same datafile unless
you preallocate the space in a different datafile.

if it is in different tablespace need not worry.

-Raja

[slimdave]

Originally posted by rajabalachandra
if the data and index are on different
spindle it will increase the performance.

You're guessing -- this is not the way Oracle works. Index and table access do not occur simultaneously.

Time to think again, 'cos this is a myth that needs to die.

[pando]

Originally posted by ljayanth
Hi hacketta1,

Ur setup is gud. set db_file_multiblock_read_count to 16 as 89 is default. but having all the data in one datafile is bad idea.
did u segregate the index, rollback segments and temp segments into other TS?? if not its again a bad practice. need not worry abt number of segments as u have db_block_size as 8K, cos 8K is less very size.

Did i asnwer ur question?
Jayanth

hmmm where did you get that 89 from....?!

and no, there is no need to seperate index and data in different tablespaces unless you want to do that for administration purposes