FAQ
Hi List,


Our dbfiles are created in Solaris UFS under VxVM and we have different tablespace/filesystem for tables and indexes.
Do you think it is beneficial to separate table and index into different VxVM diskgroups?


Thanks,


Roger Xu

This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use o

This email has been scanned for all viruses by the MessageLabs Email Security System.

Search Discussions

  • Mark W. Farnham at Sep 12, 2006 at 12:15 am
    If have scripts set aside to recreate the indexes and those scripts are
    organized by table containing tablespaces and your backup is organized so
    that you can get back system and the other "gotta have'm or you can't run"
    tablespaces quickly followed by table containing tablespaces in the order
    you are prepared to run the scripts against, then, depending on your cpu
    speed (and a few other things) versus your media reload speed (and a few
    other things) it is possible that you can do a full recovery faster with the
    tables separated from the indexes.



    If you canonically name your tablespaces so that you know that only indexes
    are affected when, say, MY_BOSS_I tablespace is trashed, then you can
    evaluate whether creating the tablespace somewhere else and just building
    the indexes will be faster than recovery. (You can also run a dictionary
    query to achieve the same knowledge, but it is a heckofalot easier in a
    "partly broken" situation to see the tablespace name and immediately KNOW
    that it is only indexes.



    If you have batch operations and you have tables that are scanned by the
    batch operations and they are not usually in cache before the batch run,
    then if you can separate the relevant tables on independent units of i/o
    such that you can treat the i/o to the several batch jobs being running in
    parallel (not with a parallel degree higher than 1, but rather several jobs
    running at the same time, but not more than one job per independent i/o
    unit), then it is possible to get the disks to serve up blocks much more
    efficiently if there are no indexes on those i/o units to break up the scan
    and inject seeks. That also presumes that you have somewhere else to put the
    indexes tablespaces. A need to do this for performance together with enough
    independent units of i/o is increasingly rare what with the humoungous
    drives and cache everyone seems to have today and the tendency to SAME them
    in some fashion or use file systems that don't actually have contiguous
    blocks in large enough chunks to make attempts to avoid extraneous seeks
    worthwhile. Global 24x7 operations on all the tables also makes it
    impossible to create an independent unit of i/o unless you have some tables
    that are simply not molested by those pesky interactive users.



    If you currently have a lot of tablespaces that are mixes of tables,
    indexes, and/or clusters, then only if you certainly would gain a lot from
    the aforementioned possibilities should you re-organize. (Certainly means
    you measured on your test system and got a big win.)



    If you do choose to organize tablespaces as tables, clusters, and indexes,
    then understand that when you insert and you have more than one index a
    table, then the index tablespace will be busier than the table tablespace.
    Now if you're SAME, that won't matter much, but if you organized your disk
    farm to maximize throughput in batch it very well might matter. So while you
    might make every tablespace either all table, all cluster, or all index, you
    wouldn't make stripe sets all index unless your maximum i/o demand was well
    under the service rate of your disk farm, because the index stripe sets are
    going to be hotter than the table tablespaces when you're mostly inserting
    and when you're updating a column or columns that require changes to more
    than one index.



    If you've got an array where most of your interactively required data blocks
    are cached most of the time, or you have enough filesystem cache or SGA
    buffer cache, all this probably matters very little except in the batch case
    where the file(s) about to be pounded are very cold before the start of the
    batch window.



    So finally I'll answer your question: It can be beneficial, but be careful
    not to spend more money doing it than the provable benefit is worth, and be
    careful to avoid differentially heating your disk farm to a significant
    degree, because it can also be destructive.



    Did I just say "It depends?"



    Regards,



    mwf

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Roger Xu
    Sent: Monday, September 11, 2006 6:54 PM
    To: Oracle-L_at_Freelists. Org (E-mail)
    Subject: use different VxVM diskgroup?



    Hi List,



    Our dbfiles are created in Solaris UFS under VxVM and we have different
    tablespace/filesystem for tables and indexes.

    Do you think it is beneficial to separate table and index into different
    VxVM diskgroups?



    Thanks,



    Roger Xu

    This e-mail is intended solely for the person or entity to which it is
    addressed and may contain confidential and/or privileged information. Any
    review, dissemination, copying, printing or other use o

    This email has been scanned for all viruses by the MessageLabs Email
    Security System.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 11, '06 at 10:53p
activeSep 12, '06 at 12:15a
posts2
users2
websiteoracle.com

2 users in discussion

Roger Xu: 1 post Mark W. Farnham: 1 post

People

Translate

site design / logo © 2022 Grokbase