Re: Separate Indexes and Data

[Mogens Nørgaard]

A fine response, Richard. Thank you.

One of the guys coding the index stuff was/is Jonathan Klein, and I 
remember asking him some years ago about reuse of index blocks, and he - 
at that point - said that he was pretty sure he put the reuse of leaf 
blocks into 7.1, but that branch blocks didn't get reused. That was 
then, and I think it has changed since. Not that it matters that much - 
there are few branch blocks compared to leaf blocks, and it's not often 
a branch blocks is completely emtied anyway :).

Mogens

Richard Foote wrote:

Hi All,

I'm having all sorts of problems getting these emails in a logical 
order (if at all). This is the first post on this subject I've 
received since I posted to Rachael, I haven't even received my own 
post yet !!

Anyways, going back in order

First to John, no, not all monotonically "here today, gone tomorrow" 
indexes require rebuilding. Note that fully "emptied" index blocks get 
placed on the freelist and are fully reusable by subsequent index 
splits. Therefore if you perform batch deletes over a specific period 
whereby most deleted entries fully empty a range of index nodes, then 
frequent rebuilding is highly questionable. Yes, Index Scans/Fast Full 
Index Scans etc. could be impacted in the interim, it kinda depends on 
*when* the same volume of data is to be reinserted.

Jared, please do write your article (the more solid articles out there 
the better)!! However note that Jonathan Lewis has written a couple of 
nice articles over at www.dbazine.com regarding some truths about 
indexes and index rebuilding. Unfortunately the same site hosts truly 
awful articles by John Weeg and Mike Hordila who both promote some 
shocking untruths/myths regarding indexes (that Oracle indexes become 
unbalanced, that deleted space is never reused, that 4 extents is 
sufficient for an index, etc. etc.) so one needs to exercise caution 
when reading stuff from there.

Jay, note that indexes generally *do* release space from deleted 
entries !! Deleted space from a index node within the current index 
structure can be totally reused by subsequent inserts. And as 
mentioned earlier, fully emptied blocks can be reused by subsequent 
index block splits. The requirement to rebuild an index is *extremely 
rare*. This subject has been raised a number of times recently on the 
Oracle newgroups (eg. 
http://groups.google.com/groups?q=g:thl4040185351d&dq=&hl=en&lr=&ie=UTF-8&selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au 
<http://groups.google.com/groups?q=g:thl4040185351d&dq=&hl=en&lr=&ie=UTF-8&selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au> ). 
It feels like fighting a lossing battle but one can only try.
 
Yes bulk deletes without subsequent re-inserts or without re-inserts 
within a "reasonable" period requires both table and hence index 
rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically* 
increasing index entries might require index rebuilds (or coalescing) 
to compact index structure for both range scan and fast full index 
scans. But these are generally *exceptions*, not the norm.
 
Hope this mail makes it ??
 
Cheers
 
Richard

- Original Message -
*From:* John Kanagaraj <mailto:[EMAIL PROTECTED]>
*To:* Multiple recipients of list ORACLE-L
    <mailto:[EMAIL PROTECTED]>
*Sent:* Tuesday, October 14, 2003 5:44 AM
*Subject:* RE: RE: Separate Indexes and Data
Jared,
 
Any indexes supporting a "In-Today; Gone-Tomorrow" status table
will require index rebuilds. Most of them have monotonically
increasing numbers which lends itself to a 'holey' index... (I
have a bunch of them with Oracle Apps Concurrent Manager and
Workflow tables)
 

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!

** The opinions and facts contained in this message are entirely
mine and do not reflect those of my employer or customers **
-Original Message-
*From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
    *Sent:* Monday, October 13, 2003 11:39 AM
*To:* Multiple recipients of list ORACLE-L
*Subject:* RE: RE: Separate Indexes and Data
hmmm... fodder for an article I've been contemplating.

"Indexes: to rebuild or not to rebuild - that is the question"

There's no need to reclaim space, except in special
circumstances.
As Kirti pointed out once, a sequentially incrementing numeric
key is
possibly one of those circumstances.
Not much point in rebuilding indexes in most cases.

If anyone cares to submit test cases for validation of the
need of an
index rebuild, you may do so here.  

Give me some test fodder!

Re: RE: Separate Indexes and Data

[Richard Foote]

Title: Message





Hi All,I'm having all sorts of problems 
getting these emails in a logical order (if at all). This is the first post on 
this subject I've received since I posted to Rachael, I haven't even received my 
own post yet !!Anyways, going back in orderFirst to John, no, 
not all monotonically "here today, gone tomorrow" indexes require rebuilding. 
Note that fully "emptied" index blocks get placed on the freelist and are fully 
reusable by subsequent index splits. Therefore if you perform batch deletes over 
a specific period whereby most deleted entries fully empty a range of index 
nodes, then frequent rebuilding is highly questionable. Yes, Index Scans/Fast 
Full Index Scans etc. could be impacted in the interim, it kinda depends on 
*when* the same volume of data is to be reinserted.
Jared, please do write your article (the more 
solid articles out there the better)!! However note that Jonathan Lewis has 
written a couple of nice articles over at www.dbazine.com regarding some truths 
about indexes and index rebuilding. Unfortunately the same site hosts truly 
awful articles by John Weeg and Mike Hordila who both promote some shocking 
untruths/myths regarding indexes (that Oracle indexes become unbalanced, that 
deleted space is never reused, that 4 extents is sufficient for an index, etc. 
etc.) so one needs to exercise caution when reading stuff from 
there.Jay, note that indexes generally *do* release space from deleted 
entries !! Deleted space from a index node within the current index structure 
can be totally reused by subsequent inserts. And as mentioned earlier, fully 
emptied blocks can be reused by subsequent index block splits. The requirement 
to rebuild an index is *extremely rare*. This subject has been raised a number 
of times recently on the Oracle newgroups (eg. http://groups.google.com/groups?q=g:thl4040185351d&dq=&hl=en&lr=&ie=UTF-8&selm=QPThb.146517%24bo1.128474%40news-server.bigpond.net.au ). 
It feels like fighting a lossing battle but one can only try. 
 
Yes bulk deletes without subsequent re-inserts or 
without re-inserts within a "reasonable" period requires both table and hence 
index rebuilds (to reset HWMs). Yes *sparse* deleting of *monotonically* 
increasing index entries might require index rebuilds (or coalescing) to compact 
index structure for both range scan and fast full index scans. But these are 
generally *exceptions*, not the norm.
 
Hope this mail makes it ??
 
Cheers
 
Richard

  - Original Message - 
  From: 
  John 
  Kanagaraj 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Tuesday, October 14, 2003 5:44 
  AM
  Subject: RE: RE: Separate Indexes and 
  Data
  
  Jared,
   
  Any 
  indexes supporting a "In-Today; Gone-Tomorrow" status table will require index 
  rebuilds. Most of them have monotonically increasing numbers which lends 
  itself to a 'holey' index... (I have a bunch of them with Oracle Apps 
  Concurrent Manager and Workflow tables)
   
  
  John KanagarajDB Soft IncPhone: 408-970-7002 
  (W)Disappointment is inevitable, but Discouragement is 
  optional!** The opinions and facts contained in this message are 
  entirely mine and do not reflect those of my employer or customers 
  **
  

-Original Message-From: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 
Monday, October 13, 2003 11:39 AMTo: Multiple recipients of list 
ORACLE-LSubject: RE: RE: Separate Indexes and 
Datahmmm... fodder for 
an article I've been contemplating. "Indexes: to rebuild or not to rebuild - that is the question" 
There's no need to reclaim space, 
except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key 
is possibly one of those 
circumstances. Not much point in 
rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an 
index rebuild, you may do so here. 
  Give me some test 
fodder! Jared 

  
  

[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 
   10/13/2003 08:59 AM 
   Please respond to ORACLE-L 
        
          
  To:        Multiple recipients of list ORACLE-L 
  <[EMAIL PROTECTED]>         cc:       
          
    Subject:        RE: RE: Separate Indexes 
  and DataI assume that what Rachel is referring to is the 
fact that indexes willgenerally not release much space when the 
underlying rows are deleted.  Theyjust keep growing, so if you have 
a large indexed table that frequentlydeletes and inserts the indexes can 
grow to fairly ridiculous sizes over aperiod of time.  We just went 
through the exercise of rebuilding indexes ona db supporting a 3rd party 
app and reclaimed about 70% of the allocatedindex space.Jay 
MillerSr. Or

Re: RE: Separate Indexes and Data

[Rachel Carmichael]

s and more places for stuff to go wrong.
> > >
> > > I saw some posts on dejanews recently from some pretty
> experienced
> > > DBAs stating that there may be 'flaws' in auto-allocate leading
> to
> > > poor extent sizes that leads to fragmentation. I believe Rachel
> > > Carmichael made a post on here a few months back with the
> similiar
> > > experience(could be wrong). Due to even the 'small' chance of
> flaws
> > > in auto-allocate, Im thinking of waiting for version 10g before
> using
> > > it. Just to be safe. Not worth risking a defrag on a production
> > > system.
> > > >
> > > > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > > 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 a

RE: RE: Separate Indexes and Data

[Rachel Carmichael]

what Jay said :)

in the beginning (hopefully this will have settled down), there were a
large number of deletes of daily data as the load process was "refined"
(ie, bugs were fixed)


--- [EMAIL PROTECTED] wrote:
> I assume that what Rachel is referring to is the fact that indexes
> will
> generally not release much space when the underlying rows are
> deleted.  They
> just keep growing, so if you have a large indexed table that
> frequently
> deletes and inserts the indexes can grow to fairly ridiculous sizes
> over a
> period of time.  We just went through the exercise of rebuilding
> indexes on
> a db supporting a 3rd party app and reclaimed about 70% of the
> allocated
> index space.
> 
> Jay Miller
> Sr. Oracle DBA
> x68355
> 
> 
> -Original Message-
> Sent: Sunday, October 12, 2003 7:39 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi Rachael,
> 
> You have me a little confused here.
> 
> What do you mean by "We over allocate space" ? To the index segments
> or to
> the tablespace ?
> 
> Why the need to rebuild the indexes ? How are they using more space
> than
> required ?
> 
> What do you mean that you adjust the pctfree so you can determine
> "how small
> you can resize them to" ?
> 
> You seem to go to a lot of trouble, I'm just failing to see what it
> all
> achieves ???
> 
> Cheers
> 
> Richard
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, October 01, 2003 4:34 AM
> 
> 
> > Nuh uh, not me... I have never used or experimented with 
> > auto-allocate.
> >
> > I separate indexes and tables so that I can reclaim space by 
> > rebuilding the indexes into smaller space.
> >
> > I've just completed writing the scripts for the following:
> >
> > we have a data warehouse, partitioned on the biggest table on date
> by 
> > month. There are 10 or 11 indexes on this table. We overallocate
> space 
> > when we create the new partition for the next month. Data is loaded
> 
> > daily. The hosting company has an automated procedure to add space
> to 
> > the datafile if the used space percentage is greater than some
> number 
> > (we get charged each time they do this, and they never allocate
> enough 
> > space so they do it over and over towards the end of the month).
> >
> > since the indexes are increasing on a daily basis, we overallocate
> the 
> > space. The next month, I go out, determine the 
> > partition/tablespace/datafiles that need to be resized (naming 
> > standards rule in this case), rebuild the indexes into an interim 
> > tablespace, rebuild them back to the original one with a smaller 
> > pctfree and then determine how small I can resize them down to.
> >
> > If there were table data in these tablespaces, I'd be out of luck
> on 
> > trying to reclaim space
> >
> >
> > --- [EMAIL PROTECTED] wrote:
> > > the defrag paper was written back in 1998 I believe. Uniform
> extents 
> > > were a good solution pre-9i. We use them here on our 8i
> databases. I 
> > > stick with an uniform 5m extent size even though I have tables
> that 
> > > can fit into 128k extents, but feel that the overall time savings
> by 
> > > using 1 extent size makes up for this.
> > >
> > > unfortunately unlike most systems we cannot break up our tables
> into 
> > > different tablespaces. We use transportable tablespaces to batch 
> > > publish data to data marts. New tablespaces mean additional 
> > > transportable tablespaces and more places for stuff to go wrong.
> > >
> > > I saw some posts on dejanews recently from some pretty
> experienced 
> > > DBAs stating that there may be 'flaws' in auto-allocate leading
> to 
> > > poor extent sizes that leads to fragmentation. I believe Rachel 
> > > Carmichael made a post on here a few months back with the
> similiar 
> > > experience(could be wrong). Due to even the 'small' chance of
> flaws 
> > > in auto-allocate, Im thinking of waiting for version 10g before 
> > > using it. Just to be safe. Not worth risking a defrag on a 
> > > production system.
> > > >
> > > > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > > Subject: RE: Separate Indexes and Data
> > >

RE: RE: Separate Indexes and Data

[John Kanagaraj]

g.> >> > I saw some posts 
  on dejanews recently from some pretty experienced > > DBAs stating 
  that there may be 'flaws' in auto-allocate leading to > > poor 
  extent sizes that leads to fragmentation. I believe Rachel > > 
  Carmichael made a post on here a few months back with the similiar > 
  > experience(could be wrong). Due to even the 'small' chance of flaws 
  > > in auto-allocate, Im thinking of waiting for version 10g before 
  > > using it. Just to be 
  safe. Not worth risking a defrag on a > > production system.> 
  > >> > > From: "MacGregor, Ian A." 
  <[EMAIL PROTECTED]>> > > Date: 2003/09/30 Tue PM 
  01:34:28 EDT> > > To: Multiple recipients of list ORACLE-L 
  <[EMAIL PROTECTED]>> > > 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.> > >

RE: RE: Separate Indexes and Data

[Jared . Still]

hmmm... fodder for an article I've been contemplating.

"Indexes: to rebuild or not to rebuild - that is the question"

There's no need to reclaim space, except in special circumstances.

As Kirti pointed out once, a sequentially incrementing numeric key is
possibly one of those circumstances.

Not much point in rebuilding indexes in most cases.

If anyone cares to submit test cases for validation of the need of an 
index rebuild, you may do so here.   

Give me some test fodder!

Jared








[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
 10/13/2003 08:59 AM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: RE: Separate Indexes and Data


I assume that what Rachel is referring to is the fact that indexes will
generally not release much space when the underlying rows are deleted.  They
just keep growing, so if you have a large indexed table that frequently
deletes and inserts the indexes can grow to fairly ridiculous sizes over a
period of time.  We just went through the exercise of rebuilding indexes on
a db supporting a 3rd party app and reclaimed about 70% of the allocated
index space.

Jay Miller
Sr. Oracle DBA
x68355


-Original Message-
Sent: Sunday, October 12, 2003 7:39 AM
To: Multiple recipients of list ORACLE-L


Hi Rachael,

You have me a little confused here.

What do you mean by "We over allocate space" ? To the index segments or to
the tablespace ?

Why the need to rebuild the indexes ? How are they using more space than
required ?

What do you mean that you adjust the pctfree so you can determine "how small
you can resize them to" ?

You seem to go to a lot of trouble, I'm just failing to see what it all
achieves ???

Cheers

Richard
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 4:34 AM


> Nuh uh, not me... I have never used or experimented with 
> auto-allocate.
>
> I separate indexes and tables so that I can reclaim space by 
> rebuilding the indexes into smaller space.
>
> I've just completed writing the scripts for the following:
>
> we have a data warehouse, partitioned on the biggest table on date by 
> month. There are 10 or 11 indexes on this table. We overallocate space 
> when we create the new partition for the next month. Data is loaded 
> daily. The hosting company has an automated procedure to add space to 
> the datafile if the used space percentage is greater than some number 
> (we get charged each time they do this, and they never allocate enough 
> space so they do it over and over towards the end of the month).
>
> since the indexes are increasing on a daily basis, we overallocate the 
> space. The next month, I go out, determine the 
> partition/tablespace/datafiles that need to be resized (naming 
> standards rule in this case), rebuild the indexes into an interim 
> tablespace, rebuild them back to the original one with a smaller 
> pctfree and then determine how small I can resize them down to.
>
> If there were table data in these tablespaces, I'd be out of luck on 
> trying to reclaim space
>
>
> --- [EMAIL PROTECTED] wrote:
> > the defrag paper was written back in 1998 I believe. Uniform extents 
> > were a good solution pre-9i. We use them here on our 8i databases. I 
> > stick with an uniform 5m extent size even though I have tables that 
> > can fit into 128k extents, but feel that the overall time savings by 
> > using 1 extent size makes up for this.
> >
> > unfortunately unlike most systems we cannot break up our tables into 
> > different tablespaces. We use transportable tablespaces to batch 
> > publish data to data marts. New tablespaces mean additional 
> > transportable tablespaces and more places for stuff to go wrong.
> >
> > I saw some posts on dejanews recently from some pretty experienced 
> > DBAs stating that there may be 'flaws' in auto-allocate leading to 
> > poor extent sizes that leads to fragmentation. I believe Rachel 
> > Carmichael made a post on here a few months back with the similiar 
> > experience(could be wrong). Due to even the 'small' chance of flaws 
> > in auto-allocate, Im thinking of waiting for version 10g before 
> > using it. Just to be safe. Not worth risking a defrag on a 
> > production system.
> > >
> > > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > Subject: RE: Separate Indexes and Data
> > >
> > > I'd be very interested to

RE: RE: Separate Indexes and Data

[JayMiller]

I assume that what Rachel is referring to is the fact that indexes will
generally not release much space when the underlying rows are deleted.  They
just keep growing, so if you have a large indexed table that frequently
deletes and inserts the indexes can grow to fairly ridiculous sizes over a
period of time.  We just went through the exercise of rebuilding indexes on
a db supporting a 3rd party app and reclaimed about 70% of the allocated
index space.

Jay Miller
Sr. Oracle DBA
x68355


-Original Message-
Sent: Sunday, October 12, 2003 7:39 AM
To: Multiple recipients of list ORACLE-L


Hi Rachael,

You have me a little confused here.

What do you mean by "We over allocate space" ? To the index segments or to
the tablespace ?

Why the need to rebuild the indexes ? How are they using more space than
required ?

What do you mean that you adjust the pctfree so you can determine "how small
you can resize them to" ?

You seem to go to a lot of trouble, I'm just failing to see what it all
achieves ???

Cheers

Richard
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 4:34 AM


> Nuh uh, not me... I have never used or experimented with 
> auto-allocate.
>
> I separate indexes and tables so that I can reclaim space by 
> rebuilding the indexes into smaller space.
>
> I've just completed writing the scripts for the following:
>
> we have a data warehouse, partitioned on the biggest table on date by 
> month. There are 10 or 11 indexes on this table. We overallocate space 
> when we create the new partition for the next month. Data is loaded 
> daily. The hosting company has an automated procedure to add space to 
> the datafile if the used space percentage is greater than some number 
> (we get charged each time they do this, and they never allocate enough 
> space so they do it over and over towards the end of the month).
>
> since the indexes are increasing on a daily basis, we overallocate the 
> space. The next month, I go out, determine the 
> partition/tablespace/datafiles that need to be resized (naming 
> standards rule in this case), rebuild the indexes into an interim 
> tablespace, rebuild them back to the original one with a smaller 
> pctfree and then determine how small I can resize them down to.
>
> If there were table data in these tablespaces, I'd be out of luck on 
> trying to reclaim space
>
>
> --- [EMAIL PROTECTED] wrote:
> > the defrag paper was written back in 1998 I believe. Uniform extents 
> > were a good solution pre-9i. We use them here on our 8i databases. I 
> > stick with an uniform 5m extent size even though I have tables that 
> > can fit into 128k extents, but feel that the overall time savings by 
> > using 1 extent size makes up for this.
> >
> > unfortunately unlike most systems we cannot break up our tables into 
> > different tablespaces. We use transportable tablespaces to batch 
> > publish data to data marts. New tablespaces mean additional 
> > transportable tablespaces and more places for stuff to go wrong.
> >
> > I saw some posts on dejanews recently from some pretty experienced 
> > DBAs stating that there may be 'flaws' in auto-allocate leading to 
> > poor extent sizes that leads to fragmentation. I believe Rachel 
> > Carmichael made a post on here a few months back with the similiar 
> > experience(could be wrong). Due to even the 'small' chance of flaws 
> > in auto-allocate, Im thinking of waiting for version 10g before 
> > using it. Just to be safe. Not worth risking a defrag on a 
> > production system.
> > >
> > > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > 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 enfor

Re: RE: Separate Indexes and Data

[Richard Foote]

Hi Rachael,

You have me a little confused here.

What do you mean by "We over allocate space" ? To the index segments or to
the tablespace ?

Why the need to rebuild the indexes ? How are they using more space than
required ?

What do you mean that you adjust the pctfree so you can determine "how small
you can resize them to" ?

You seem to go to a lot of trouble, I'm just failing to see what it all
achieves ???

Cheers

Richard
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 01, 2003 4:34 AM


> Nuh uh, not me... I have never used or experimented with auto-allocate.
>
> I separate indexes and tables so that I can reclaim space by rebuilding
> the indexes into smaller space.
>
> I've just completed writing the scripts for the following:
>
> we have a data warehouse, partitioned on the biggest table on date by
> month. There are 10 or 11 indexes on this table. We overallocate space
> when we create the new partition for the next month. Data is loaded
> daily. The hosting company has an automated procedure to add space to
> the datafile if the used space percentage is greater than some number
> (we get charged each time they do this, and they never allocate enough
> space so they do it over and over towards the end of the month).
>
> since the indexes are increasing on a daily basis, we overallocate the
> space. The next month, I go out, determine the
> partition/tablespace/datafiles that need to be resized (naming
> standards rule in this case), rebuild the indexes into an interim
> tablespace, rebuild them back to the original one with a smaller
> pctfree and then determine how small I can resize them down to.
>
> If there were table data in these tablespaces, I'd be out of luck on
> trying to reclaim space
>
>
> --- [EMAIL PROTECTED] wrote:
> > the defrag paper was written back in 1998 I believe. Uniform extents
> > were a good solution pre-9i. We use them here on our 8i databases. I
> > stick with an uniform 5m extent size even though I have tables that
> > can fit into 128k extents, but feel that the overall time savings by
> > using 1 extent size makes up for this.
> >
> > unfortunately unlike most systems we cannot break up our tables into
> > different tablespaces. We use transportable tablespaces to batch
> > publish data to data marts. New tablespaces mean additional
> > transportable tablespaces and more places for stuff to go wrong.
> >
> > I saw some posts on dejanews recently from some pretty experienced
> > DBAs stating that there may be 'flaws' in auto-allocate leading to
> > poor extent sizes that leads to fragmentation. I believe Rachel
> > Carmichael made a post on here a few months back with the similiar
> > experience(could be wrong). Due to even the 'small' chance of flaws
> > in auto-allocate, Im thinking of waiting for version 10g before using
> > it. Just to be safe. Not worth risking a defrag on a production
> > system.
> > >
> > > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > > 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 tha

RE: Separate Indexes and Data

[Jared Still]

Sure, you could do that.

It just doesn't seem like a good way to deal with
the possibility of an index tablespace possibly
having data segments in it when backing up only
data segment tablespaces.

Unless you have *really* large databases with very
generous restore time requirements , I don't see the
 point in trying to do this anyway.

Jared

On Wed, 2003-10-01 at 05:14, Mark Leith wrote:
> Couldn't you do this with a simple:
> 
> select owner, table_name
> from all_tables
> where tablespace_name = 'index_tbs';
> 
> ?
> 
> Or of course use IN for a list of tablespaces?
> 
> Or am I missing something?
> 
>   -Original Message-
>   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> [EMAIL PROTECTED]
>   Sent: 30 September 2003 22:45
>   To: Multiple recipients of list ORACLE-L
>   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 correspondi

RE: RE: Separate Indexes and Data

[MacGregor, Ian A.]

Part of the problem is self-inflicted.  We currently use separate tablespaces for each 
 major project.  For instance:  chemical inventory  gets its own data and index 
tablespaces,  dosimeter data gets the same,  network configuration data as well.  For 
many projects once the design has matured new segment creation is rare.   The holes  
remain.  Also data segments cannot be moved willy-nilly, users do not like getting 
unusable index errors.  There are also tables which cannot be easily moved such as 
tables with longs.  These were created before LOBs were available.  Moving data also 
entails a certain amount of risk. Inside a project, we let developers create the 
tables and indexes which are specific to that project.  Very few actually create 
indexes in their proper tablespaces.  Corrective action creates more  holes.  This is 
one reason why I am looking at index and data segments in the same tablespace.   

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]   
-Original Message-
Sent: Tuesday, September 30, 2003 4:25 PM
To: Multiple recipients of list ORACLE-L


But those holes of exactly the right size for new objects to fit into. Since you'll 
presumably move it once it gets about 1,000 extents or so that isn't a huge amount of 
space that's being wasted.



Jay Miller
Sr. Oracle DBA


-Original Message-
Sent: Tuesday, September 30, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L


My criticism of the defrag paper was that it did not address what to do when a segment 
grew  large enough to belong in a tablespace with a larger uniform extent size.  
Moving the segment creates  holes in its original tablespace which may close only in 
the fullness of time.  Physical backups of the files comprising the original 
tablespace include this wasted space, this is compounded by how many days backup you 
keep available, and the number of copies of backups. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: RE: Separate Indexes and Data

[Connor McDonald]

Whilst the vast majority of extents will be the
"published" ones - 64k, 1m, et al, you occasionally
get variants.

I have only three gripes with auto-allocate:

a) you can't perform the "can I extend" check on your
tablespaces.  You cannot predict with 100% certainty
what the size of the next extent will be.  You have to
make assumptions.

b) you minimise but not eliminate the fragmentation
problem.  Its rare but its relatively easy to concoct
an example where you have 'n' bytes of free space, but
cannot allocate an extent of size less than 'n' bytes

c) Cynicism.  If there is no problem with 'n' extents
(n > insert high number here) , why does Oracle
implement a solution designed to keep a lid on their
number.  Besides extent map blocks, is there some
serious problem that Oracle is not telling us at the
(say) million extent mark?  If there is, then what is
the problem.  If there is not, then why doesn't Oracle
abandon the concept altogether and just enforce
something similar to what we see in file systems where
every extent is a strict (say) 1m in size.  

Cheers
Connor


 --- "MacGregor, Ian A." <[EMAIL PROTECTED]>
wrote: > My criticism of the defrag paper was that it
did not
> address what to do when a segment grew  large enough
> to belong in a tablespace with a larger uniform
> extent size.  Moving the segment creates  holes in
> its original tablespace which may close only in the
> fullness of time.  Physical backups of the files
> comprising the original tablespace include this
> wasted space, this is compounded by how many days
> backup you keep available, and the number of copies
> of backups. 
> 
> You have chosen to get around the segment migration
> problem by using one very large extent size for
> everything.  Don't you find 5M extents wasteful? 
> What is your block size and the median number of
> used blocks for your segments outside of the system
> tablespace?  How many such segments are there?.  
> 
> Also many of us use a single backup system to
> support multiple databases.  The number of segments
> outside the system tablespace here is over 125,.
>  Making all segments at least 5M in size would have
> a major impact on file sizes, which in turn would
> have a major impact on backup times, and possibly
> the size of the  tape library needed.
> 
> I'm interested in the flaws in autoallocate. Does it
> allocate the wrong amount of space?  
> 
> 
> Ian MacGregor
> [EMAIL PROTECTED]  
> 
> -Original Message-
> Sent: Tuesday, September 30, 2003 10:50 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> the defrag paper was written back in 1998 I believe.
> Uniform extents were a good solution pre-9i. We use
> them here on our 8i databases. I stick with an
> uniform 5m extent size even though I have tables
> that can fit into 128k extents, but feel that the
> overall time savings by using 1 extent size makes up
> for this.
> 
> unfortunately unlike most systems we cannot break up
> our tables into different tablespaces. We use
> transportable tablespaces to batch publish data to
> data marts. New tablespaces mean additional
> transportable tablespaces and more places for stuff
> to go wrong. 
> 
> I saw some posts on dejanews recently from some
> pretty experienced DBAs stating that there may be
> 'flaws' in auto-allocate leading to poor extent
> sizes that leads to fragmentation. I believe Rachel
> Carmichael made a post on here a few months back
> with the similiar experience(could be wrong). Due to
> even the 'small' chance of flaws in auto-allocate,
> Im thinking of waiting for version 10g before using
> it. Just to be safe. Not worth risking a defrag on a
> production system. 
> > 
> > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> > 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 firs

RE: Separate Indexes and Data

[Mark Leith]

Couldn't you do this with a simple:
 
select 
owner, table_name
from 
all_tables
where 
tablespace_name = 'index_tbs';
 
?
 
Or of 
course use IN for a list of tablespaces?
 
Or am 
I missing something?
 

  -Original Message-From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]On Behalf Of 
  [EMAIL PROTECTED]Sent: 30 September 2003 
  22:45To: Multiple recipients of list ORACLE-LSubject: 
  RE: Separate Indexes and DataGood 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 
DataI'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 paperNever 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 
  MacGregorStanford Linear Accelerator Center 
  [EMAIL PROTECTED]-Original Message-Sent: Monday, 
  September 29, 2003 8:10 AMTo: Multiple recipients of list 
  ORACLE-LThomas,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. 
  Anotherexample: 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 MillsapHotsos Enterprises, 
  Ltd.http://www.hotsos.comUpcoming 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 DaySent: 
  Monday, Sep

Re: Separate Indexes and Data

[Tanel Poder]

You can always schedule a script which drops all 
table segments from index tablespaces ;)
 
Tanel.
 

  - Original Message - 
  From: 
  [EMAIL PROTECTED] 
  To: Multiple recipients of list ORACLE-L 
  
  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 
DataI'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 paperNever 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 
  MacGregorStanford Linear Accelerator Center 
  [EMAIL PROTECTED]-Original Message-Sent: Monday, 
  September 29, 2003 8:10 AMTo: Multiple recipients of list 
  ORACLE-LThomas,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. 
  Anotherexample: 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 MillsapHotsos Enterprises, 
  Ltd.http://www.hotsos.comUpcoming 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 DaySent: 
  Monday, September 29, 2003 9:05 AMTo:

RE: RE: Separate Indexes and Data

[JayMiller]

But those holes of exactly the right size for new objects to fit into.
Since you'll presumably move it once it gets about 1,000 extents or so that
isn't a huge amount of space that's being wasted.



Jay Miller
Sr. Oracle DBA


-Original Message-
Sent: Tuesday, September 30, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L


My criticism of the defrag paper was that it did not address what to do when
a segment grew  large enough to belong in a tablespace with a larger uniform
extent size.  Moving the segment creates  holes in its original tablespace
which may close only in the fullness of time.  Physical backups of the files
comprising the original tablespace include this wasted space, this is
compounded by how many days backup you keep available, and the number of
copies of backups. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  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).

RE: Separate Indexes and Data

[Jared . Still]

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 Go

RE: RE: Separate Indexes and Data

[MacGregor, Ian A.]

My criticism of the defrag paper was that it did not address what to do when a segment 
grew  large enough to belong in a tablespace with a larger uniform extent size.  
Moving the segment creates  holes in its original tablespace which may close only in 
the fullness of time.  Physical backups of the files comprising the original 
tablespace include this wasted space, this is compounded by how many days backup you 
keep available, and the number of copies of backups. 

You have chosen to get around the segment migration problem by using one very large 
extent size for everything.  Don't you find 5M extents wasteful?  What is your block 
size and the median number of used blocks for your segments outside of the system 
tablespace?  How many such segments are there?.  

Also many of us use a single backup system to support multiple databases.  The number 
of segments outside the system tablespace here is over 125,.  Making all segments 
at least 5M in size would have a major impact on file sizes, which in turn would have 
a major impact on backup times, and possibly the size of the  tape library needed.

I'm interested in the flaws in autoallocate. Does it allocate the wrong amount of 
space?  


Ian MacGregor
[EMAIL PROTECTED]  

-Original Message-
Sent: Tuesday, September 30, 2003 10:50 AM
To: Multiple recipients of list ORACLE-L


the defrag paper was written back in 1998 I believe. Uniform extents were a good 
solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m 
extent size even though I have tables that can fit into 128k extents, but feel that 
the overall time savings by using 1 extent size makes up for this.

unfortunately unlike most systems we cannot break up our tables into different 
tablespaces. We use transportable tablespaces to batch publish data to data marts. New 
tablespaces mean additional transportable tablespaces and more places for stuff to go 
wrong. 

I saw some posts on dejanews recently from some pretty experienced DBAs stating that 
there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to 
fragmentation. I believe Rachel Carmichael made a post on here a few months back with 
the similiar experience(could be wrong). Due to even the 'small' chance of flaws in 
auto-allocate, Im thinking of waiting for version 10g before using it. Just to be 
safe. Not worth risking a defrag on a production system. 
> 
> From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> Date: 2003/09/30 Tue PM 01:34:28 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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 
&g

Re: Separate Indexes and Data

[Tanel Poder]

Hi!

In VLDB environments, it is mostly cheaper to restore and recover the index
tablespace datafile in case of block corruption. In my experience, I've been
lucky and have been able to get rid of corruptions that way, but I'm sure
some people have worse experiences, especially when redologs are corrupted
as well.

Anyway, rebuilding a huge index is much more expensive operation than
recovering & restoring a datafile (rebuild requires a lot of IO, CPU and
temp space). With 9i, the recovery is even better, if you use RMAN - you
just can restore&recover one single block is you want.
There is one nice exception -> if the corruption occurs in a local index
partition, it is possible to rebuild only this particular partition, without
need to recover anything. (Partitioning can be considered alsi as a high
availability feature, in addition to performance improvements).

Tanel.

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, September 30, 2003 8:34 PM


> 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 O

RE: RE: Separate Indexes and Data

[Jesse, Rich]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 30, 2003 1:29 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: RE: Separate Indexes and Data
> 
> 
> 
> > 
> > From: "Jesse, Rich" <[EMAIL PROTECTED]>
> > Date: 2003/09/30 Tue PM 02:09:32 EDT
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: RE: RE: Separate Indexes and Data
> > 
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, September 30, 2003 12:50 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re: RE: Separate Indexes and Data
> > > 
> > > 
> > > the defrag paper was written back in 1998 I believe. Uniform 
> > > extents were a good solution pre-9i. We use them here on our 
> > 
> > I haven't been following this thread closely, but why are 
> uniform extents no
> > longer good for 9i?
> > 
> Kyte recommends using auto-segment management

Actually, the qualifier from Mr. Kyte is to "use system-managed LMTs when
you not know how big your objects will become" (Ch 3, p19).  I don't think
this is a blanket statement not to use uniform extents.  Our DB tables grow
linearly (near enough anyway) to correctly estimate storage requirements for
at least a fiscal year.  Granted, there is a minimal amount of play
involved, mostly revolving around how our business expects to be doing in
the coming year.

I'm much more concerned about fragmentation left from the creation and
deletion of DB objects.  With LMTs, it's one thing I don't have to worry
about.

I'm looking forward to LMTs in 9iR2!  :)

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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).

Re: RE: Separate Indexes and Data

[Rachel Carmichael]

Nuh uh, not me... I have never used or experimented with auto-allocate.

I separate indexes and tables so that I can reclaim space by rebuilding
the indexes into smaller space.

I've just completed writing the scripts for the following:

we have a data warehouse, partitioned on the biggest table on date by
month. There are 10 or 11 indexes on this table. We overallocate space
when we create the new partition for the next month. Data is loaded
daily. The hosting company has an automated procedure to add space to
the datafile if the used space percentage is greater than some number
(we get charged each time they do this, and they never allocate enough
space so they do it over and over towards the end of the month). 

since the indexes are increasing on a daily basis, we overallocate the
space. The next month, I go out, determine the
partition/tablespace/datafiles that need to be resized (naming
standards rule in this case), rebuild the indexes into an interim
tablespace, rebuild them back to the original one with a smaller
pctfree and then determine how small I can resize them down to.

If there were table data in these tablespaces, I'd be out of luck on
trying to reclaim space

 
--- [EMAIL PROTECTED] wrote:
> the defrag paper was written back in 1998 I believe. Uniform extents
> were a good solution pre-9i. We use them here on our 8i databases. I
> stick with an uniform 5m extent size even though I have tables that
> can fit into 128k extents, but feel that the overall time savings by
> using 1 extent size makes up for this.
> 
> unfortunately unlike most systems we cannot break up our tables into
> different tablespaces. We use transportable tablespaces to batch
> publish data to data marts. New tablespaces mean additional
> transportable tablespaces and more places for stuff to go wrong. 
> 
> I saw some posts on dejanews recently from some pretty experienced
> DBAs stating that there may be 'flaws' in auto-allocate leading to
> poor extent sizes that leads to fragmentation. I believe Rachel
> Carmichael made a post on here a few months back with the similiar
> experience(could be wrong). Due to even the 'small' chance of flaws
> in auto-allocate, Im thinking of waiting for version 10g before using
> it. Just to be safe. Not worth risking a defrag on a production
> system. 
> > 
> > From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> > Date: 2003/09/30 Tue PM 01:34:28 EDT
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > 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,
> > 
> 

RE: RE: Separate Indexes and Data

[rgaffuri]

> 
> From: "Jesse, Rich" <[EMAIL PROTECTED]>
> Date: 2003/09/30 Tue PM 02:09:32 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: RE: Separate Indexes and Data
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, September 30, 2003 12:50 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: RE: Separate Indexes and Data
> > 
> > 
> > the defrag paper was written back in 1998 I believe. Uniform 
> > extents were a good solution pre-9i. We use them here on our 
> 
> I haven't been following this thread closely, but why are uniform extents no
> longer good for 9i?
> 
Kyte recommends using auto-segment management


> > 8i databases. I stick with an uniform 5m extent size even 
> > though I have tables that can fit into 128k extents, but feel 
> > that the overall time savings by using 1 extent size makes up 
> > for this.
> >
> > unfortunately unlike most systems we cannot break up our 
> > tables into different tablespaces. We use transportable 
> > tablespaces to batch publish data to data marts. New 
> > tablespaces mean additional transportable tablespaces and 
> > more places for stuff to go wrong. 
> > 
> > I saw some posts on dejanews recently from some pretty 
> > experienced DBAs stating that there may be 'flaws' in 
> > auto-allocate leading to poor extent sizes that leads to 
> 
> I don't believe it's a flaw, it's by design.  At least according to Tom
> Kyte's new book (first chapter's on the web) it is, which is why I can't see
> me using it for our DBs.
> 
Ive read the book. PCTINCREASE is basically set to 100% so the extent sizes double. 
Thats 'basically' how it works. I have seen some posts on dejanews saying it doesnt 
necessarily work this way and some people are finding large extent sizes with just a 
few extents and when tables are dropped this is leading to fragmentation. It hasnt 
happened to me, but the posts on dejanews were from some pretty good posters. So Im 
playing conservative. We also had one of the contributors here mention issues. 


> > fragmentation. I believe Rachel Carmichael made a post on 
> > here a few months back with the similiar experience(could be 
> > wrong). Due to even the 'small' chance of flaws in 
> > auto-allocate, Im thinking of waiting for version 10g before 
> > using it. Just to be safe. Not worth risking a defrag on a 
> > production system. 
> 
> Rich
> 
> Rich Jesse   System/Database Administrator
> [EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jesse, Rich
>   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: <[EMAIL PROTECTED]
  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).

RE: RE: Separate Indexes and Data

[Jesse, Rich]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 30, 2003 12:50 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: RE: Separate Indexes and Data
> 
> 
> the defrag paper was written back in 1998 I believe. Uniform 
> extents were a good solution pre-9i. We use them here on our 

I haven't been following this thread closely, but why are uniform extents no
longer good for 9i?

> 8i databases. I stick with an uniform 5m extent size even 
> though I have tables that can fit into 128k extents, but feel 
> that the overall time savings by using 1 extent size makes up 
> for this.
>
> unfortunately unlike most systems we cannot break up our 
> tables into different tablespaces. We use transportable 
> tablespaces to batch publish data to data marts. New 
> tablespaces mean additional transportable tablespaces and 
> more places for stuff to go wrong. 
> 
> I saw some posts on dejanews recently from some pretty 
> experienced DBAs stating that there may be 'flaws' in 
> auto-allocate leading to poor extent sizes that leads to 

I don't believe it's a flaw, it's by design.  At least according to Tom
Kyte's new book (first chapter's on the web) it is, which is why I can't see
me using it for our DBs.

> fragmentation. I believe Rachel Carmichael made a post on 
> here a few months back with the similiar experience(could be 
> wrong). Due to even the 'small' chance of flaws in 
> auto-allocate, Im thinking of waiting for version 10g before 
> using it. Just to be safe. Not worth risking a defrag on a 
> production system. 

Rich

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech Inc, Sussex, WI USA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  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).

Re: RE: Separate Indexes and Data

[rgaffuri]

the defrag paper was written back in 1998 I believe. Uniform extents were a good 
solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m 
extent size even though I have tables that can fit into 128k extents, but feel that 
the overall time savings by using 1 extent size makes up for this.

unfortunately unlike most systems we cannot break up our tables into different 
tablespaces. We use transportable tablespaces to batch publish data to data marts. New 
tablespaces mean additional transportable tablespaces and more places for stuff to go 
wrong. 

I saw some posts on dejanews recently from some pretty experienced DBAs stating that 
there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to 
fragmentation. I believe Rachel Carmichael made a post on here a few months back with 
the similiar experience(could be wrong). Due to even the 'small' chance of flaws in 
auto-allocate, Im thinking of waiting for version 10g before using it. Just to be 
safe. Not worth risking a defrag on a production system. 
> 
> From: "MacGregor, Ian A." <[EMAIL PROTECTED]>
> Date: 2003/09/30 Tue PM 01:34:28 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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
> 

RE: Separate Indexes and Data

[MacGregor, Ian A.]

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   To:  Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
  Sent by: cc:

  ml-errorsSubject: 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 f