RE: use different VxVM diskgroup?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <Roger.Xu@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Sep 2006 20:15:16 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Roger Xu
Sent: Monday, September 11, 2006 6:54 PM
To: Oracle-L@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.
Other related posts:
