Amount of segments per tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Amount of segments per tablespace

  1. #1
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367

    Amount of segments per tablespace

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2003
    Posts
    17
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2003
    Posts
    17
    Mr. self inventor,

    i mean to seperate the indexws to another tablespace.

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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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?

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width