John,
I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the
table itself, occassionally].
This Saturday I will also be rebuilding some ALR indexes.
Which WorkFlow Indexes do you rebuild ?
Hemant
At 11:44 AM 13-10-03 -0800, you wrote:
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: Jared.Still@radisys.com
[mailto:Jared.Still@radisys.com]
- 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!
- Jared
- JayMiller@tdwaterhouse.com
- Sent by: ml-errors@fatcity.com
- 10/13/2003 08:59 AM
-
Please respond to ORACLE-L
-
- To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
- 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" <ORACLE-L@fatcity.com>
- 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
- >
- >
- > --- rgaffuri@cox.net 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." <ian@SLAC.Stanford.EDU>
- > > > Date: 2003/09/30 Tue PM 01:34:28 EDT
- > > > To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
- > > > 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
- > > > ian@slac.stanford.edu
- > > >
- > > > -----Original Message-----
- > > > Sent: Monday, September 29, 2003 8:10 AM
- > > > To: Multiple recipients of list ORACLE-L
- > > >
- > > >
- > > > Thomas,
- > > >
- > > > It *is* a good idea to separate index data from heap data into
- > > different tablespaces. But the reason isn't solely to eliminate I/O
- > > competition. Even if I/O competition isn't an issue for you (and the
- > > OFA Standard doesn't say that it will be), then it's *still* a good
- > > idea to separate your index data from your heap data, for reasons
- > > including:
- > > >
- > > > * Index segments have different backup and recovery requirements
- > > than their corresponding heap segments. For example, as Peter
- > > mentioned, if you have an index block corruption event, then it's
- > > convenient to just offline, kill, and rebuild an index tablespace.
- > > If the indexes and data are mixed up in a single tablespace, this is
- > > not an option. Another
- > > > example: If you construct your backup schedule to make media
- > > recovery time a constant, then you probably don't need to back up
- > > your indexes on the same schedule as you back up your heaps. But
- > > unless they're in different tablespaces, this isn't an option
- > > either.
- > > >
- > > > * Index segments are usually smaller than their corresponding heap
- > > segments. Using separate tablespaces allows you to use a smaller
- > > extent size to conserve disk storage capacity.
- > > >
- > > > I don't think I ever wrote that you need to put indexes and their
- > > corresponding tables/clusters on separate disks, but you do need to
- > > be
- > > > *able* to do that if your I/O rates indicate that you should.
- > > >
- > > > For the original OFA Standard definition, please see section 3 of
- > > the document called "The OFA Standard--Oracle for Open Systems," and
- > > section 5 of "Configuring Oracle Server for VLDB," both available
- > > for free at www.hotsos.com.
- > > >
- > > >
- > > > Cary Millsap
- > > > Hotsos Enterprises, Ltd.
- > > > http://www.hotsos.com
- > > >
- > > > Upcoming events:
- > > > - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
- > > > - Hotsos Symposium 2004: March 7-10 Dallas
- > > > - Visit www.hotsos.com for schedule details...
- > > >
- > > >
- > > > -----Original Message-----
- > > > Thomas Day
- > > > Sent: Monday, September 29, 2003 9:05 AM
- > > > To: Multiple recipients of list ORACLE-L
- > > >
- > > >
- > > > My struggle is not with the directory layout OFA.
- > > >
- > > > It is with the "mythical" OFA that every DBA that I have talked to
- > > knows all about. Where ORACLE says that if you are a good and
- > > competent DBA you will separate your table data and your index data
- > > into two separate tablespaces so that one disk head can be reading
- > > index entries while another disk head is reading the table data.
- > > You've never run into that?
- > > >
- > > >
- > > >
- > > >
- > > >
- > > > Tim Gorman <tim
- > > >
- > > > @sagelogix.com> To: Multiple
- > > > recipients of list ORACLE-L <ORACLE-L@fatcity.com>
- > >
- > > > Sent by: cc:
- > > >
- > > > ml-errors Subject: Re: BAARF
- > > >
- > > >
- > > >
- > > >
- > > >
- > > > 09/28/2003 09:44
- > > >
- > > > PM
- > > >
- > > > Please respond
- > > >
- > > > to ORACLE-L
- > > >
- > > >
- > > >
- > > >
- > > >
- > > >
- > > >
- > > >
- > > >
- > > > Thomas,
- > > >
- > > > Please pardon me, but you are off-target in your criticisms of
- > > > OFA.
- > > >
- > > > It has never advocated separating tables from indexes for
- > > performance purposes. Ironically, your email starts to touch on the
- > > real reason for separating them (i.e. different types of I/O,
- > > different recovery requirements, etc). Tables and indexes do belong
- > > in different tablespaces, but not for reasons of performance.
- > > >
- > > > Cary first designed and implemented OFA in the early 90s and
- > > formalized it into a paper in 1995. Quite frankly, it is a
- > > brilliant set of rules of how Oracle-based systems should be
- > > structured, and a breath of fresh air from the simplistic way that
- > > Oracle installers laid things out at the time. It took several years
- > > for Oracle Development to see the light and become OFA-compliant,
- > > and not a moment too soon either. Just imagine if everything were
- > > still installed into a single directory tree under ORACLE_HOME? All
- > > of things you mention here have nothing to do with OFA.
- > > >
- > > > Please read the paper.
- > > >
- > > > Hope this helps...
- > > >
- > > > -Tim
- > > >
- > > > P.S. By the way, multiple block sizes are not intended for
- > > > performance
- > > > optimization; they merely enable transportable
- > > > tablespaces
- > > between
- > > > databases with different block sizes.
- > > >
- > > >
- > > > on 9/25/03 11:04 AM, Thomas Day at tday6@csc.com wrote:
- > > >
- > > > >
- > > > > I would love to have a definitive site that I could send all
- > > RAID-F
- > > > > advocates to where it would be laid out clearly, unambiguously,
- > > and
- > > > > definitively what storage types should be used for what purpose.
- > > > >
- > > > > Redo logs on RAID 0 with Oracle duplexing (y/n)?
- > >
- > === message truncated ===
- >
- >
- > __________________________________
- > Do you Yahoo!?
- > The New Yahoo! Shopping - with improved product search
- > http://shopping.yahoo.com
- > --
- > Please see the official ORACLE-L FAQ: http://www.orafaq.net
- > --
- > Author: Rachel Carmichael
- > INET: wisernet100@yahoo.com
- >
- > 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: ListGuru@fatcity.com (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: Richard Foote
- INET: richard.foote@bigpond.com
- 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: ListGuru@fatcity.com (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:
- INET: JayMiller@tdwaterhouse.com
- 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: ListGuru@fatcity.com (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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :
http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
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: ListGuru@fatcity.com (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).
Received on Tue Oct 14 2003 - 10:39:25 CDT
