Index in different tablespace
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Index in different tablespace

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    Index in different tablespace

    Situation:
    I have a disk raid 1 mirror, that has the os.(windows 2000), and copy of online redo logs and control files.

    And a raid 5, that has the database(2 gig).

    I have only 1 tablespaces that contains my tables(638) and indexes(993).

    I think that one of the good reason to keep index in a different tablespace is the save the io on disk. But since im on a raid 5, i dont see the utility to put the indexes on a different tablespace.

    My tablespace is a localy managed tablespace with uniform extent 128k.

    Do you have more arguments on why to put indexes on different tablespace. Maybe to use a tablespace with bigger extent?
    Any suggestion?

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    There is still a slight benefit on performance (your DB is small at 2GB) but the major benefit is logical administration ease. I'd keep Table and Indexes apart.

    You get better performance spliting the RAID 5 over two separate filesystems. Then having your TABLE and INDEX tablespaces on each indivudual filesystems.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hi

    First of all, who said that on RAID 5 i/o doesn't matter.

    Use RAID 5 only when u have high reads e.g. for LDAP.

    If u want to keep a database keep it on RAID (1+0)

    Change the RAID if u can or u will be going to be in trouble....

    U can ask any good consultant what they have to say about RAID 5

    Regards
    Amar

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I don't believe there to be any performance of maintenance banefit from separating indexes and tables into different tablespaces -- look at asktom.oracle.com or the google forums for many discussions on this.

    I've been following advice and putting objects in tablespaces based purely on their size for quite a while now, and it works great. Maybe a dedicated TS for a particular table, of for different partitions of a hash-partitioned table, but other than that make it simple for yourself.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    Well, we always sell server with raid 5, and the server usely runs only oracle, so what can i say to my supervisor to make him sell good disk configuration to clients? And what is that disk configuration.

    It is always windows 2000, with at least 1 gig of ram, the os is always on disk raid 1 mirror. I cant change the os we are selling for a lot of reasons, but i know i could change the damn disk configuration, i found at least 6 of my clients that have a lot of disk contention.

    What does good consultant have to say about RAID 5?

    Basicaly, i want to know advantage and disavantage of both configuration. Give me links if you have some.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Steve,

    Not a consultant but . . .

    I started with a RAID-1 plus RAID-5 just like your config. Quest's "Spotlight on Oracle" showed waits on the log files but (normally) nothing much else. I concluded it was due to having the pagefile on the same drive as the logs

    I bought some consulting time from Oracle and we came up with the following:

    - get the log & ctl files to their own disk (RAID-1) - every bit of doc I have read says this. Now you have make sure that the remaining 18Gb minus 80Mb is not used for anything else (at least during working hours)! I use it for "archive" material (NO, not archive logs!) and I backup my test system there over-night.

    - the more logical disks the better, so get as many RAID-1 pairs as you can afford (until you have one db file per disk - the money usually runs out first).


    The compromise I came to was 8 physical disks (4x2) instead of 5 (1x2 plus 1x3) - the question is, can you sell that?
    YES, data & index are separate. A single query with full table scans does not slow down OLTP - two at the same time does, but it's acceptable.
    (For completeness:
    Dual processor
    Disk1: OS, pagefile & "file server"
    Disk2: Data & RBS
    Disk3: Index, System, Temp & ArchLogs
    Disk4: Logs & Ctls ONLY)
    I'm sure there are arguments for different configs - I'm happy with this.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  7. #7
    Join Date
    Nov 2000
    Posts
    440
    Im suprise that log and control file generate that much io to be only those on one disk, have any doc on this?

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    They don't! A commit is not complete until the log is physically written (anything else can remain in memory). Anything that gets in the way of the writing of logs can start a traffic jam. Putting logs on their own disk makes sure nothing else can hold up the process.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You could look at the Oracle supplied doc: "8i Designing & Tuning for Performance", chapter 20, section "Solving I/O Problems".
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Nov 2000
    Posts
    440
    Thanks form the info

    I will managed for my future client uses raid 1 mirror for their database.

    But for the client i have that are on raid5, their is only 1 tablespace that has the tables and indexes.
    Does index in a different tablespace with uniform extent lager than 128k will help?

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