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

[hacketta1]

Thanks for all your replies.

The re-org is actually addressing a couple of issues. Firstly, fragmentation is so bad that the number of records in fet$ and so on is making administration difficult; queries on cetain data dictionary views still haven't returned records after a few hours!

In addition, the previous DBA had created a volume group for indexes and a volume group for tables, each using different spindles. I'm intentionally undoing this by having a single volume group with indexes and tables sharing the same tablespaces. For the benefit of future readers I'd recommend doing a search on the Google groups comp.database.oracle.server. There is a load of information on why there is no point in separating tables and indexes.

I reckon I'll be leaving db_file_multiblock_read_count alone. Given it effects the CBO, it's something to look at another day.

With regard to a single datafile, it seems the file size limit on HP-UX is 64GB so I'll be forced to have multiple files per tablespace whether I like it or not.

Anyway, thanks for the confirmation that the number of segments of per tablespace should not be a concern.

Austin

[efrijters]

IMHO it is wiser to figure out how much bytes your server can read in 1 I/O and determine your extent sizes and db_file_multiblock_read_count based on this value.

If you follow this link (http://www.dbasupport.com/forums/showthread.php?threadid=38997&highlight=extent+tablespace) , you'll find the procedure that I used to determine my extent sizes. I learned this at the AskTom-site.

HTH

[pando]

the procedure is setup a big table and full scan it using event 10046, then compare the wait times

my server reads up to 1MB in each I/O however it seems that best performance is achieved by setting db_file_multiblock_read_count to 16, since my db_block_size is 8k seems that 128K is the best I/O although it can read 1MB

[efrijters]

Pando: how do you calculate the wait times of event 10046? Summarize the 'ela='-values in the trace file?

[hacketta1]

Thank you for replies. I'm going to look into this. I'll be using Trace Analyzer to summarize the 10046 trace file for me. Ejfritters, you can get this from MetaLink, it's very good:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=224270.1

[efrijters]

Wow! Thanks for the link, hacketta1!