| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: use different VxVM diskgroup?
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 [mailto: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
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 11 2006 - 19:15:16 CDT
 |
 |