Tablespaces/Datafiles Management - Better Practices
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tablespaces/Datafiles Management - Better Practices

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    Question Tablespaces/Datafiles Management - Better Practices

    Hi All,
    We have a 500GB Oracle database on HP-UX 11
    currently it's on EMC disk array with RAID 1 mirroring (single controller)
    In a few weeks we plan to move to a SAN device with RAID 5 (probably 3 controllers)
    During/after this period I plan to reorg my tablespaces and datafiles to practice what is thought to be a better design in DBASupport.com community.
    This system was migrated from legacy and has seen times of earlier versions of Orcale 7.x and 8.0; currently it's on 8.1.7
    Currently some of the tablespaces are as big as 90GB (tables underneath are upto 20GB)
    Most of the datafiles are 2GB (because large files option was not set on unix) so in all there are about 250 datafiles.

    My questions to you -

    - should I try to set the tablespace size to a smaller number?
    - should I expand datafiles to 5GB or even more? the new system has an array of 7GB disks

    Thanks,
    - Rajeev

    ps. most tablespaces are LMT
    Rajeev Suri

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    RAID 5 eh? Go to www.oraperf.com, register, and download the white paper on I/O tuning.

    Best practice is to isolate big tables into their own tablespaces - smaller tables/indexes can be divided into categories by size and assigned to TS's appropriately. Small, Medium and Large Segment Tablespaces can hold both tables and indexes at once -- no need to seperate them.

    You data files could easily afford to grow up to 4Gb.

    Migrate to LMT for all TS's (except SYSTEM until you are at 9i), using uniform extents, as soon as possible.

    I'm sure there are other things to add. anyone?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131
    Hmmm
    On one hand I'd say sure take full advantage of newer technologies.
    On the other hand if it ain't broke don't fix it.

    Have you been able to simulate/test any of this in development?


    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Would it be good to move things around? Sure. If you can convert to LMT, then go for it.

    However, you could be setting yourself up to be the fall guy. If there are any performance problems after the move, all fingers will be pointing at you because you futzed th the tables and indexes. Personally, I would do it gradually:

    1. Move everything to RAID 5 on the SAN.
    2. The next weekend, move 1/3 of your tables and indexes to LMT's (system managed where appropriate).
    3. repeat 2 the next weekend
    4. repeat 2 the next weekend

    Also, measure your I/O performance before and after each step. This way you can prove/disprove improvements.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I have a feeling than RAID5 is going to take a big bite out of your I/O potential, so Jeff' advice seems v. prudent. So many changes at once, you won't know where to point your finger if something goes pear-shaped.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    And be sure to involve other people in the disk reorganization. That way, there are other people to point fingers at. Strength in numbers. Think of swimming with sharks.

    http://www.marquitta.com/Sharks%20and%20Suits.html

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    To add to Steve's insightful comment -- If possible, introduce a deliberate performance damper on the system at the time of migration. Preferably it'll be something that others wouldn't spot, but it'll allow you to step in and boost performance by 200% whenver you feel like it, thus making you Hero Of The Hour, and Employee Of The Nanosecond.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    An excellent choice by the way would be to introduce DBMS_LOCK.SLEEP(5) into a key procedure. If the code is wrapped no-one will be able to easily tell, and when you remove it you can say you simply removed some of the inefficient developers' code and replaced it with your own. Double Kudos!!!
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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