|
You can always schedule a script which drops all
table segments from index tablespaces ;)
Tanel.
----- Original Message -----
Sent: Wednesday, October 01, 2003 12:44
AM
Subject: RE: Separate Indexes and
Data
Good question Ian. If
anyone does have a different backup schedule for index tbs , I
would be interested to know how they
ensure that the index TBS do not have any
data segments in them.
Jared
|
| "MacGregor, Ian A." <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
09/30/2003 10:34 AM
Please respond to ORACLE-L
|
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject: RE: Separate Indexes and
Data |
I'd be very interested to know how many people have their index
tablespaces on a different backup schedule from their data tablespaces.
If so how different? What happens when a media failure
occurs and you must restore from backup? You would need to have on hand
and apply more redo logs to make the database current.
I
understand the argument proffered is separating indexes and data can mean that
when physical corruption of the file happens to an index tablespace then all
one needs do is to offline, drop, drop and rebuild the index tablespace.
I admit I have not tried off-lining the tablespace first, but you cannot
normally drop a tablespace which is being used to enforce referential
integrity. If off-lining the tablespace first does work, I can see
someone trying to do the rebuild with the database available and having
duplicate records in the parent tables and records without parents in the
child tables.
On the size of the segments: The paper entitled
"How To Start Defragmenting and Start Living" or something like that
strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M,
128M, and 4G as I recall. However the paper
Never mentioned what to
do when an object that used to fit nicely into the 128k extent
category now more properly belongs to the 4M category. If you move
the data, large holes are left in the other tablespace, and while this
does not impact Oracle performance, it does mean that your physical backups
are larger than necessary. I am in the process of migrating from uniform
to autoallocated extents. This means extents of different sizes share
the same tablespace. The extent sizes being multiples of each other.
This removes the argument about not having indexes and data in the same
tablespaces due to their different sizes.
Ian
MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Monday,
September 29, 2003 8:10 AM
To: Multiple recipients of list
ORACLE-L
Thomas,
It *is* a good idea to separate index data
from heap data into different tablespaces. But the reason isn't solely to
eliminate I/O competition. Even if I/O competition isn't an issue for you (and
the OFA Standard doesn't say that it will be), then it's *still* a good idea
to separate your index data from your heap data, for reasons
including:
* Index segments have different backup and recovery
requirements than their corresponding heap segments. For example, as Peter
mentioned, if you have an index block corruption event, then it's convenient
to just offline, kill, and rebuild an index tablespace. If the indexes and
data are mixed up in a single tablespace, this is not an option.
Another
example: If you construct your backup schedule to make media
recovery time a constant, then you probably don't need to back up your indexes
on the same schedule as you back up your heaps. But unless they're in
different tablespaces, this isn't an option either.
* Index segments
are usually smaller than their corresponding heap segments. Using separate
tablespaces allows you to use a smaller extent size to conserve disk storage
capacity.
I don't think I ever wrote that you need to put indexes and
their corresponding tables/clusters on separate disks, but you do need to
be
*able* to do that if your I/O rates indicate that you should.
For
the original OFA Standard definition, please see section 3 of the document
called "The OFA Standard--Oracle for Open Systems," and section 5 of
"Configuring Oracle Server for VLDB," both available for free at
www.hotsos.com.
Cary Millsap
Hotsos Enterprises,
Ltd.
http://www.hotsos.com
Upcoming events:
- Performance
Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- Hotsos Symposium 2004: March
7-10 Dallas
- Visit www.hotsos.com for schedule
details...
-----Original Message-----
Thomas Day
Sent:
Monday, September 29, 2003 9:05 AM
To: Multiple recipients of list
ORACLE-L
My struggle is not with the directory layout
OFA.
It is with the "mythical" OFA that every DBA that I have talked to
knows all about. Where ORACLE says that if you are a good and competent
DBA you will separate your table data and your index data into two
separate tablespaces so that one disk head can be reading index entries while
another disk head is reading the table data. You've never run into
that?
Tim Gorman <tim
@sagelogix.com>
To:
Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent by:
cc:
ml-errors
Subject: Re: BAARF
09/28/2003
09:44
PM
Please respond
to
ORACLE-L
Thomas,
Please pardon
me, but you are off-target in your criticisms of OFA.
It has never
advocated separating tables from indexes for performance purposes.
Ironically, your email starts to touch on the real reason for separating
them (i.e. different types of I/O, different recovery requirements, etc).
Tables and indexes do belong in different tablespaces, but not for
reasons of performance.
Cary first designed and implemented OFA in the
early 90s and formalized it into a paper in 1995. Quite frankly, it is a
brilliant set of rules of how Oracle-based systems should be structured, and a
breath of fresh air from the simplistic way that Oracle installers laid things
out at the time. It took several years for Oracle Development to see the light
and become OFA-compliant, and not a moment too soon either. Just imagine
if everything were still installed into a single directory tree under
ORACLE_HOME? All of things you mention here have nothing to do with
OFA.
Please read the paper.
Hope this
helps...
-Tim
P.S. By the way, multiple block sizes
are not intended for
performance
optimization; they merely enable transportable tablespaces
between
databases with different block
sizes.
on 9/25/03 11:04 AM, Thomas Day at [EMAIL PROTECTED]
wrote:
>
> I would love to have a definitive site that I could
send all RAID-F
> advocates to where it would be laid out clearly,
unambiguously, and
> definitively what storage types should be used for
what purpose.
>
> Redo logs on RAID 0 with Oracle duplexing
(y/n)?
> Rollback (or undo) ditto?
> Write intensive tablespaces
on RAID 1+0 (or should that be 0+1)? Read
> intensive tablespaces on
RAID ? (I guess 5 is OK since it's
cheaper
> than 1+0 and you won't
have the write penalty)
>
> While we're at it could we blow up the
OFA myth? Since you're
tablespaces
> are on datafiles that are
on logical volumns that are on physical
devices
> which may contain
one or many actual disks, does it really make sense
to
> worry (from
a performance standpoint) about separating tables and
indexes
> into
different tablespaces?
>
> We have killed the "everything in one
extent" myth haven't we?
Everybody's
> comfortable with tables that
have 100's of extents?
>
> And while we're at it, could we include
the Oracle 9 multiple
blocksizes
> and how to use them. The
best that I've seen is indexes in big
blocks,
> tables in small
blocks --- uh, oh, time to separate tables and
indexes.
>
>
Maybe we will never get rid of the OFA myth.
>
> Just
venting.
>
> Tired of arguing in front of management with Oracle
certified DBAs
that
> RAID 5 is not good, OFA is unnecessary, and
uniform extents is the
only
way
> to go. Looking for a big
stick to catch their attention with.
>
--
Please see the
official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim
Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services
-- 858-538-5051 http://www.fatcity.com
San Diego, California
-- Mailing list and web hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
you want to be removed from). You may also send the HELP command for
other information (like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Thomas Day
INET:
[EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California --
Mailing list and web hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
you want to be removed from). You may also send the HELP command for
other information (like subscribing).
--
Please see the
official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Cary
Millsap
INET: [EMAIL PROTECTED]
Fat City Network
Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message
BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list
you want to be removed from). You may also send the HELP command for
other information (like subscribing).
--
Please see the official
ORACLE-L FAQ: http://www.orafaq.net
--
Author: MacGregor, Ian
A.
INET: [EMAIL PROTECTED]
Fat City Network Services
-- 858-538-5051 http://www.fatcity.com
San Diego, California
-- Mailing list and web hosting
services
---------------------------------------------------------------------
To
REMOVE yourself from this mailing list, send an E-Mail message
to:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message
BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing
list you want to be removed from). You may
also send the HELP command
for other information (like
subscribing).
|
