RE: re Rebuilding Indexes in Oracle Apps
John At my location I was not finding those tables without rows so simple truncate was not the easy option. However, from time to time I was truncating them by removing rows into temp type of tables and placing those rows back. However, index rebuilding was more practical under that situation. Truncating gl_interface table was also reducing HWM for better performance. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 14 Oct 2003 17:29:25 -0800 Rafiq, >John >What about gl_interface table indexes? I think indexes on all >*interface( >tables must be rebuild on a regular interval...I was building >indexes on >gl_interfaces and fnd_request* tables on monthly basis. Indeed the interface tables suffer as well. I would suggest a TRUNCate of these tables after processing monthend (or at an agreed time with the users), so the index will be chopped as well 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 ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps --
Ah well ! I do not have statistics to present my case.
However, it is not my case that Index rebuilds are necessary,
only that in the case of some Indexes, rebuilds do make sense.
Last week, I'd just purged about 80% of the rows in a few tables and, of
course,
then rebuilt both the tables [to reset the HWM] and indexes.
{These were some Alert tables where the application administrator
had never setup purging and we had 2 years of alerts}
FND_CONCURRENT_REQUESTS is a case of a table with monotonically
increasing values for certain columns [REQUEST_ID and REQUEST_START_DATE].
Although the Note that I referred to in my earlier email [Note 182699.1]
has been updated on 17-Oct, it still includes the paragraph
"Unoccupied space on indexes occurs when a key value changes, and the
index
row is deleted from one place (Leaf Block) and inserted into another.
Deleted Leaf Rows are not reused. Therefore, indexes whose columns are
subject to intensive value change should be rebuilt periodically, since
they become naturally fragmentated. "
Hemant
At 06:44 PM 17-10-03 -0800, you wrote:
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, October 18, 2003 4:42 AM
Hi Hermant,
>
> I wonder if "it is not necessary to rebuild indexes" is also a myth.
It might be but I've yet to hear it. It's certainly not something I've ever
claimed, unless it's a quote taken out of context (the start and end are
missing) which would be unfortunate.
I would re-phrase it as "it is *rarely* necessary to rebuild indexes" and
it would be a hell of a lot more accurate than many quotations on this
subject. So let's not confuse and cloud the issue.
>
> It IS in some cases necessary
Yes it is but the point I'm trying to make that the "some cases" are
relatively *rare*. The "emphasis" as I often hear it is that indexes
"usually/always" need to be rebuilt. This is simply incorrect. The Metalink
note claims that deleted space is not reused. This is not only incorrect but
helps promote the myth that indexes hence need frequent rebuilding. One
incorrect claim promotes one incorrect conclusion.
> 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID
> based on a Sequence
As I've previously stated *but* and it's a big BUT only if there are
subsequent sparse deletions. No spares deletions, no rebuilds are necessary.
What ratio of indexes in Oracle financials actually meet this criteria ?
Monotonically increasing *and* sparse deletions.
> or even on date columns which signify when the record is created] if the
> table is also
> purged by the same columns frequently
Similar case to the above. But this implies a specific range of index values
being deleted which results in a range of index nodes being emptied. These
blocks therefore *can* be reused. If records are subsequently inserted *at
the same rate* they are being purged, then again index rebuilds are
potentially unnecessary.
> 2. Because the disk space used by an Index can be inordinately larged
> after a couple of years
> and index fast_full_scans are impacted
How ?
We covered one case above. Another is that we simply reduce the volume of
data within a table (and hence index). How does "time" result inordinately
enlarged indexes ? As previously discussed, Oracle is very efficient in the
way it reuses space within an index, suggestions that indexes just become
unnecessarily enlarged over time are generally false.
>
> Have you administered an Oracle Applications database ?
No, but I have a number of SAP applications and they suffer from the same
bad advice that indexes generally require frequently rebuilding. In actual
fact, the ratio of indexes that actually benefit from rebuilding is tiny and
then it's generally the table that needs rebuilding more so than the indexes
directly and then the tiny tiny ratio of indexes that remain generally need
coalescing rather than rebuilding.
Indexes that exist in Oracle Applications are not special, they follow the
same rules as those indexes in SAP, or in-house applications, etc.
Hemant, take a look at Jonathan Lewis's article "When Should You Rebuild An
Index" at www.dbazine.com . In it he concludes "Will the total cost of
rebuilding the index be a reasonable price to pay for the resulting benefit
to the system ? The answer to this question is frequently a resounding NO.
In fact, sometimes the overall impact of rebuilding an active index will be
detrimental to the system. However, there are still plenty of misconceptions
about indexes that result in DBAs the world over wasting valuable time and
effort rebuilding indexes unnecessarily".
Amen to that !!
Cheers ;)
Richard
Author: Richard Foote
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 li
Re: re Rebuilding Indexes in Oracle Apps --
Hi Hemant,
If you purge 80% of rows from a table without intending to reinsert them
anytime soon, then yes, a table reorg would be a recommended step. No
arguments from me there ;)
I had lunch with Pete Sharman today and he mentioned that he sent an email
to the support person responsible for the note with a copy of my little demo
highlighting how deleted row space can be reused. I also received an email
form Lex de Haan at Oracle inviting me to provide feedback on how the note
can be improved.
So hopefully, a modified note might be available soon. That being the case,
one has to give Oracle much credit for trying to put things to the right.
The tide is turning ;)
Cheers
Richard
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, October 22, 2003 1:14 AM
>
> Ah well ! I do not have statistics to present my case.
> However, it is not my case that Index rebuilds are necessary,
> only that in the case of some Indexes, rebuilds do make sense.
>
> Last week, I'd just purged about 80% of the rows in a few tables and, of
> course,
> then rebuilt both the tables [to reset the HWM] and indexes.
> {These were some Alert tables where the application administrator
> had never setup purging and we had 2 years of alerts}
>
> FND_CONCURRENT_REQUESTS is a case of a table with monotonically
> increasing values for certain columns [REQUEST_ID and REQUEST_START_DATE].
>
> Although the Note that I referred to in my earlier email [Note 182699.1]
> has been updated on 17-Oct, it still includes the paragraph
> "Unoccupied space on indexes occurs when a key value changes, and the
> index
> row is deleted from one place (Leaf Block) and inserted into another.
> Deleted Leaf Rows are not reused. Therefore, indexes whose columns
are
> subject to intensive value change should be rebuilt periodically,
since
> they become naturally fragmentated. "
>
>
> Hemant
>
> At 06:44 PM 17-10-03 -0800, you wrote:
> >- Original Message -
> >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> >Sent: Saturday, October 18, 2003 4:42 AM
> >
> >Hi Hermant,
> >
> >
> > >
> > > I wonder if "it is not necessary to rebuild indexes" is also a myth.
> >
> >It might be but I've yet to hear it. It's certainly not something I've
ever
> >claimed, unless it's a quote taken out of context (the start and end are
> >missing) which would be unfortunate.
> >
> >I would re-phrase it as "it is *rarely* necessary to rebuild indexes"
and
> >it would be a hell of a lot more accurate than many quotations on this
> >subject. So let's not confuse and cloud the issue.
> >
> > >
> > > It IS in some cases necessary
> >
> >Yes it is but the point I'm trying to make that the "some cases" are
> >relatively *rare*. The "emphasis" as I often hear it is that indexes
> >"usually/always" need to be rebuilt. This is simply incorrect. The
Metalink
> >note claims that deleted space is not reused. This is not only incorrect
but
> >helps promote the myth that indexes hence need frequent rebuilding. One
> >incorrect claim promotes one incorrect conclusion.
> >
> > > 1. Indexes on monotonically increasing values [eg
Conrurrent_Request_ID
> > > based on a Sequence
> >
> >As I've previously stated *but* and it's a big BUT only if there are
> >subsequent sparse deletions. No spares deletions, no rebuilds are
necessary.
> >What ratio of indexes in Oracle financials actually meet this criteria ?
> >Monotonically increasing *and* sparse deletions.
> >
> > > or even on date columns which signify when the record is created] if
the
> > > table is also
> > > purged by the same columns frequently
> >
> >Similar case to the above. But this implies a specific range of index
values
> >being deleted which results in a range of index nodes being emptied.
These
> >blocks therefore *can* be reused. If records are subsequently inserted
*at
> >the same rate* they are being purged, then again index rebuilds are
> >potentially unnecessary.
> >
> > > 2. Because the disk space used by an Index can be inordinately larged
> > > after a couple of years
> > > and index fast_full_scans are impacted
> >
> >How ?
> >
> >We covered one case above. Another is that we simply reduce the volume of
> >data within a table (and hence index). How does "time" result
inordinately
> >enlarged indexes ? As previously discussed, Oracle is very efficient in
the
> >way it reuses space within an index, suggestions that indexes just become
> >unnecessarily enlarged over time are generally false.
> >
> > >
> > > Have you administered an Oracle Applications database ?
> >
> >No, but I have a number of SAP applications and they suffer from the same
> >bad advice that indexes generally require frequently rebuilding. In
actual
> >fact, the ratio of indexes that actually benefit from rebuilding is tiny
and
> >then it's generally the table that needs rebuilding more so than the
indexes
> >directly and then the tiny tiny ratio of ind
RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
"Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence. "
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
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: [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 Rebuilding Indexes in Oracle Apps -- an update
> COALESCE would be a better option than REBUILD for Indexes on
> monotonically increasing sequences where older values are purged periodically.
Unless you happen to be doing index_ffs on that particular index, in which
case a rebuild *may* be in order. Rebuilding the index may cause insert
performance problems for a time due to block splits.
If the index is a composite index with some other non-unique value(s)
making up the index, a higher than normal number of block splits may be
in your future. I haven't tested the composite index yet, but this seems reasonable.
If the index is based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will cut down the number of scanned blocks.
It would be interesting to see how skip scans are affected by this as well.
Jared
Hemant K Chitale <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
"Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence. "
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
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: [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 Rebuilding Indexes in Oracle Apps -- an update
Jared,
I don't see how index skip scans could benefit more
from a rebuild than from coalesce (providing the index height remains the same).
Skip scan doesn't scan the whole index like FFS does, it just does several scans
for each value set in beginning of concatenated index (+some more
mechanisms).
Maybe I'm missing something here, what did you have
in mind?
Tanel.
If the index is
based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will
cut down the number of scanned blocks. It would be interesting to see how skip scans are affected by this as
well. Jared
Hemant K Chitale
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]> cc:
Subject: RE: re Rebuilding Indexes in
Oracle Apps -- an updateRichard et al,{for those who've been
following the thread on Rebuilding Indexes ...}I've just been reading
the AskTom thread on rebuilding indexesat
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730and
picked on the important line"Coalesce... reclaim the free space from
mostly empty index leaf blocks that will not be reused otherwise due to
your increasing sequence. "Richard has also pointed COALESCE as a
better option.COALESCE would be a better option than REBUILD for Indexes
onmonotonically increasing sequences where older values are purged
periodically.Hemant K ChitaleOracle 9i Database Administrator
Certified ProfessionalMy personal web site is :
http://hkchital.tripod.com-- Please see the official
ORACLE-L FAQ: http://www.orafaq.net-- Author: Hemant K
Chitale INET: [EMAIL PROTECTED]Fat City Network
Services -- 858-538-5051 http://www.fatcity.comSan Diego,
California -- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
Re: re Rebuilding Indexes in Oracle Apps -- an update
Now that I think about it, you're probably right.
I'll test it anyway, I like to see numbers. :)
Jared
"Tanel Poder" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/04/2003 10:04 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: re Rebuilding Indexes in Oracle Apps -- an update
Jared,
I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans for each value set in beginning of concatenated index (+some more mechanisms).
Maybe I'm missing something here, what did you have in mind?
Tanel.
If the index is based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will cut down the number of scanned blocks.
It would be interesting to see how skip scans are affected by this as well.
Jared
Hemant K Chitale <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
"Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence. "
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
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: [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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
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: [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! 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 th
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
At 01:34 PM 14-10-03 -0800, you wrote: Hemant, John, My apologies for the delay. I hadn't logged on to my email last night. Here's the output from my site 11.0.3 "Purge Obsolete Workflow Runtime Data" set to AGE=90 days [ITEM_TYPE and ITEM_KEY null in parameters] SQL> set time on 14:57:42 SQL> 14:57:42 SQL> select activity_status, count(*) 14:57:46 2 from applsys.wf_item_activity_statuses 14:57:46 3 group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; ACTIVITY COUNT(*) -- ACTIVE 18761 COMPLETE 1039949 DEFERRED 1082 ERROR 5541 NOTIFIED 10489 14:58:03 SQL> 14:58:03 SQL> 14:58:03 2 14:58:03 3 14:58:03 4 ITEM_TYP ACTIVITY COUNT(*) -- APVRMDER COMPLETE 29739 CREATEPO COMPLETE 154074 POAPPRV COMPLETE 309445 REQAPPRV COMPLETE 546767 14:59:01 SQL> Currently I do not have too many rows in the tables but I still plan to rebuild the tables and indexes. Hemant This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L 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: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). 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: [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 Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Richard, Quoting Metalink Note "182699.1" on " bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0)" Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: > On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > > Jared, > > > > Those tables are transit type of tables and depending on your volume of > > data, there are lot of deletes and inserts all the time resuling index > > fragmentation(holes due to deletes) and space usage. > > > > The rebuilding not only release the space but also reduces the index > > fragmentation. If you don't have table truncation option for such tables > > then it is much better to rebuid indexes on such tables at regular interval > > to release space and for better performance. > > Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL> create table bowie_test (ziggy number); Table created. SQL> insert into bowie_test values (1); 1 row created. SQL> insert into bowie_test values (2); 1 row created. SQL> insert into bowie_test values (3); 1 row created. SQL> insert into bowie_test values (4); 1 row created. SQL> insert into bowie_test values (6); 1 row created. SQL> insert into bowie_test values (7); 1 row created. SQL> insert into bowie_test values (8); 1 row created. SQL> insert into bowie_test values (9); 1 row created. SQL> insert into bowie_test values (10); 1 row created. SQL> insert into bowie_test values (100); 1 row created. SQL> commit; Commit complete. SQL> create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL> analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows .. SQL> delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL> commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum .." SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL> insert into bowie_test values (5); 1 row created. SQL> commit; Commit complete. SQL> analyze index bowie_test_idx validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be "detrimental" to performance. Cheers Richard 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Hemant, It is absolutely true with Oracle Financials Databases and I have seen performance degradation when indexes on such databses are not rebuilt at a regular interval meaning indexes on certain tables on mothly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 16 Oct 2003 07:49:44 -0800 _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es --- Begin Message --- Richard, Quoting Metalink Note "182699.1" on " bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0)" Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: > On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > > Jared, > > > > Those tables are transit type of tables and depending on your volume of > > data, there are lot of deletes and inserts all the time resuling index > > fragmentation(holes due to deletes) and space usage. > > > > The rebuilding not only release the space but also reduces the index > > fragmentation. If you don't have table truncation option for such tables > > then it is much better to rebuid indexes on such tables at regular interval > > to release space and for better performance. > > Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL> create table bowie_test (ziggy number); Table created. SQL> insert into bowie_test values (1); 1 row created. SQL> insert into bowie_test values (2); 1 row created. SQL> insert into bowie_test values (3); 1 row created. SQL> insert into bowie_test values (4); 1 row created. SQL> insert into bowie_test values (6); 1 row created. SQL> insert into bowie_test values (7); 1 row created. SQL> insert into bowie_test values (8); 1 row created. SQL> insert into bowie_test values (9); 1 row created. SQL> insert into bowie_test values (10); 1 row created. SQL> insert into bowie_test values (100); 1 row created. SQL> commit; Commit complete. SQL> create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL> analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows .. SQL> delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL> commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum .." SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL> insert into bowie_test values (5); 1 row created. SQL> commit; Commit complete. SQL> analyze index bowie_test_idx validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available
Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Doesn't reuse leaf rows?! The myth is perpetuated. Try running these scripts. In a nutshell, a table is created with about 20k rows, with an incrementing id. There is a non-unique index created on the id column. An analyze is done on the table then index_stats is populated with 'analyze index validate structure', and the number of leaf rows, All rows with an id that is evenly divisible by 2 are copied to another table and then deleted. The rows are then reinserted into the first table. Between each step, statistics are computed on the table and index, the index structure validated, and the number of leaf rows, leaf blocks, deleted leaf rows and deleted leaf blocks are displayed. === ai.sql === analyze table t1 compute statistics; analyze index t1_id_idx validate structure; === is.sql === select lf_rows, lf_blks, lf_blk_len, del_lf_rows, pct_used from index_stats / === j1.sql === drop table t1 cascade constraints; drop table t2 cascade constraints; create table t1 ( id number(8) , last_name varchar2(30) , first_name varchar2(30) ) nologging / insert /*+ append */ into t1( id, last_name, first_name) select rownum id , substr(owner,1,30) last_name , substr(object_name,1,30) first_name from dba_objects / create index t1_id_idx on t1(id); @@ai @@is create table t2 nologging as select * from t1 where 1=0 / insert /*+ append */ into t2 select * from t1 where id/2 = floor(id/2) / delete from t1 where id/2 = floor(id/2) / commit; @@ai @@is insert into t1 (id, last_name, first_name) select id+1 id, last_name, first_name from t2 / @@ai @@is == Hemant K Chitale <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/16/2003 08:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps Richard, Quoting Metalink Note "182699.1" on " bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0)" Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: > On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > > Jared, > > > > Those tables are transit type of tables and depending on your volume of > > data, there are lot of deletes and inserts all the time resuling index > > fragmentation(holes due to deletes) and space usage. > > > > The rebuilding not only release the space but also reduces the index > > fragmentation. If you don't have table truncation option for such tables > > then it is much better to rebuid indexes on such tables at regular interval > > to release space and for better performance. > > Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL> create table bowie_test (ziggy number); Table created. SQL> insert into bowie_test values (1); 1 row created. SQL> insert into bowie_test values (2); 1 row created. SQL> insert into bowie_test values (3); 1 row created. SQL> insert into bowie_test values (4); 1 row created. SQL> insert into bowie_test values (6); 1 row created. SQL> insert into bowie_test values (7); 1 row created. SQL> insert into bowie_test values (8); 1 row created. SQL> insert into bowie_test values (9); 1 row created. SQL> insert into bowie_test values (10); 1 row created. SQL> insert into bowie_te
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time. Hemant K Chitale <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/17/2003 11:42 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: I wonder if "it is not necessary to rebuild indexes" is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: >Hi Hemant, > >One word perfectly describes the Metalink article you highlighted: > >Crap ;) > >A nice example of how Oracle Corp is the greatest myth generator of them >all !! It's all rather sad and embarressing isn't. > >Thanks for the headsup. Anyone in a position to get the note removed ? > >Cheers > >Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds >Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become >naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its Leaf >Rows space is > >empty because of the implicit deletes caused by indexed columns value >changes. > > > >Fragmentated indexes degrade the performance of index range scan >operations. > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Richard Foote > 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). 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: [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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
If they don't have time to fix bugs, how do you expect them to spend time to correct documentation and technical notes? Now many people pay to buy documentation nowadays ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, October 17, 2003 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time.**This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
I wonder if "it is not necessary to rebuild indexes" is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0->9.0) > >Index fragmentation occurs when a key value changes, and the index row is >deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are >subject to value change must be rebuilt periodically since they become naturally fragmentated. > >An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is >empty because of the implicit deletes caused by indexed columns value changes. > >Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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). 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: [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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Title: Message
Well, in a small step in our defense, it DOES state at the top of the
note:
*
This article is being
delivered in Draft form and may contain
errors. Please use the
MetaLink "Feedback" button to advise
Oracle of any issues related
to this article.
*
and in an even larger step, the author of the document
has been asked to review and correct it.
Pete
"Controlling
developers is like herding cats."
Kevin
Loney, Oracle DBA Handbook
"Oh
no, it's not. It's much harder than
that!"
Bruce
Pihlamae, long-term Oracle DBA
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]Sent: Saturday, October 18, 2003 5:35
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
re Rebuilding Indexes in Oracle Apps -- was RE:
RE:The article states
that leaf blocks are not reused, which is indeed incorrect, and has been for a very long
time.
Hemant K Chitale
<[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED]
10/17/2003 11:42 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]> cc:
Subject: Re: re Rebuilding Indexes in
Oracle Apps -- was RE: RE:I wonder if "it is not necessary to rebuild
indexes" is also a myth.It IS in some cases necessary1.
Indexes on monotonically increasing values [eg Conrurrent_Request_ID
based on a Sequenceor even on date columns which signify when the
record is created] if the table is alsopurged by the same columns
frequently2. Because the disk space used by an Index can be
inordinately larged after a couple of yearsand index fast_full_scans
are impactedHave you administered an Oracle Applications database
?hemantAt 03:29 AM 17-10-03 -0800, you wrote:>Hi
Hemant,>>One word perfectly describes the Metalink article you
highlighted:>>Crap ;)>>A nice example of how
Oracle Corp is the greatest myth generator of them>all !! It's all
rather sad and embarressing isn't.>>Thanks for the headsup.
Anyone in a position to get the note removed
?>>Cheers>>Richard>> >Quoting
Metalink Note 182699.1 bde_rebuild.sql Validates and
Rebuilds>Fragmentated Indexes (8.0->9.0)> >>
>Index fragmentation occurs when a key value changes, and the index row
is> >deleted from one place (Leaf Block) and inserted into
another.> >> > Deleted Leaf Rows are not reused. Therefore
indexes whose columns are> >subject to value change must be
rebuilt periodically since they become>naturally fragmentated.>
>> >An index is considered to be 'fragmentated' when more
than 20% of its Leaf>Rows space is> >empty because of the
implicit deletes caused by indexed columns value>changes.>
>> >Fragmentated indexes degrade the performance of index
range scan>operations.>>>-->Please see the
official ORACLE-L FAQ: http://www.orafaq.net>-->Author: Richard
Foote> 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).Hemant K
ChitaleOracle 9i Database Administrator Certified ProfessionalMy
personal web site is : http://hkchital.tripod.com--
Please see the official ORACLE-L FAQ: http://www.orafaq.net--
Author: Hemant K Chitale 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 messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
- Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, October 18, 2003 4:42 AM Hi Hermant, > > I wonder if "it is not necessary to rebuild indexes" is also a myth. It might be but I've yet to hear it. It's certainly not something I've ever claimed, unless it's a quote taken out of context (the start and end are missing) which would be unfortunate. I would re-phrase it as "it is *rarely* necessary to rebuild indexes" and it would be a hell of a lot more accurate than many quotations on this subject. So let's not confuse and cloud the issue. > > It IS in some cases necessary Yes it is but the point I'm trying to make that the "some cases" are relatively *rare*. The "emphasis" as I often hear it is that indexes "usually/always" need to be rebuilt. This is simply incorrect. The Metalink note claims that deleted space is not reused. This is not only incorrect but helps promote the myth that indexes hence need frequent rebuilding. One incorrect claim promotes one incorrect conclusion. > 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID > based on a Sequence As I've previously stated *but* and it's a big BUT only if there are subsequent sparse deletions. No spares deletions, no rebuilds are necessary. What ratio of indexes in Oracle financials actually meet this criteria ? Monotonically increasing *and* sparse deletions. > or even on date columns which signify when the record is created] if the > table is also > purged by the same columns frequently Similar case to the above. But this implies a specific range of index values being deleted which results in a range of index nodes being emptied. These blocks therefore *can* be reused. If records are subsequently inserted *at the same rate* they are being purged, then again index rebuilds are potentially unnecessary. > 2. Because the disk space used by an Index can be inordinately larged > after a couple of years > and index fast_full_scans are impacted How ? We covered one case above. Another is that we simply reduce the volume of data within a table (and hence index). How does "time" result inordinately enlarged indexes ? As previously discussed, Oracle is very efficient in the way it reuses space within an index, suggestions that indexes just become unnecessarily enlarged over time are generally false. > > Have you administered an Oracle Applications database ? No, but I have a number of SAP applications and they suffer from the same bad advice that indexes generally require frequently rebuilding. In actual fact, the ratio of indexes that actually benefit from rebuilding is tiny and then it's generally the table that needs rebuilding more so than the indexes directly and then the tiny tiny ratio of indexes that remain generally need coalescing rather than rebuilding. Indexes that exist in Oracle Applications are not special, they follow the same rules as those indexes in SAP, or in-house applications, etc. Hemant, take a look at Jonathan Lewis's article "When Should You Rebuild An Index" at www.dbazine.com . In it he concludes "Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system ? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily". Amen to that !! Cheers ;) Richard > hemant > > > At 03:29 AM 17-10-03 -0800, you wrote: > >Hi Hemant, > > > >One word perfectly describes the Metalink article you highlighted: > > > >Crap ;) > > > >A nice example of how Oracle Corp is the greatest myth generator of them > >all !! It's all rather sad and embarressing isn't. > > > >Thanks for the headsup. Anyone in a position to get the note removed ? > > > >Cheers > > > >Richard > > > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds > >Fragmentated Indexes (8.0->9.0) > > > > > >Index fragmentation occurs when a key value changes, and the index row is > > >deleted from one place (Leaf Block) and inserted into another. > > > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > > >subject to value change must be rebuilt periodically since they become > >naturally fragmentated. > > > > > >An index is considered to be 'fragmentated' when more than 20% of its Leaf > >Rows space is > > >empty because of the implicit deletes caused by indexed columns value > >changes. > > > > > >Fragmentated indexes degrade the performance of index range scan > >operations. > > > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Richard Foote > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com > >San Diego, California-- Mailing li
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
Unfortunately, the lines "Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. " are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about "index fragmentation" have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0->9.0) > >Index fragmentation occurs when a key value changes, and the index row is >deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are >subject to value change must be rebuilt periodically since they become naturally fragmentated. > >An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is >empty because of the implicit deletes caused by indexed columns value changes. > >Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). 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: [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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines "Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. " are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: >Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements >about "index fragmentation" have been removed. > > >Cary Millsap >Hotsos Enterprises, Ltd. >http://www.hotsos.com > >Upcoming events: >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney >- SQL Optimization 101: 12/8-12 Dallas >- Hotsos Symposium 2004: March 7-10 Dallas >- Visit www.hotsos.com for schedule details... > > >-Original Message- >Richard Foote >Sent: Friday, October 17, 2003 6:29 AM >To: Multiple recipients of list ORACLE-L >Separate > >Hi Hemant, > >One word perfectly describes the Metalink article you highlighted: > >Crap ;) > >A nice example of how Oracle Corp is the greatest myth generator of >them >all !! It's all rather sad and embarressing isn't. > >Thanks for the headsup. Anyone in a position to get the note removed ? > >Cheers > >Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds >Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row >is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become >naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its >Leaf >Rows space is > >empty because of the implicit deletes caused by indexed columns value >changes. > > > >Fragmentated indexes degrade the performance of index range scan >operations. > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Richard Foote > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Cary Millsap > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). 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: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMA
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT&p_id=182699.1 Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines "Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. " are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: >Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements >about "index fragmentation" have been removed. > > >Cary Millsap >Hotsos Enterprises, Ltd. >http://www.hotsos.com > >Upcoming events: >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney >- SQL Optimization 101: 12/8-12 Dallas >- Hotsos Symposium 2004: March 7-10 Dallas >- Visit www.hotsos.com for schedule details... > > >-Original Message- >Richard Foote >Sent: Friday, October 17, 2003 6:29 AM >To: Multiple recipients of list ORACLE-L >Separate > >Hi Hemant, > >One word perfectly describes the Metalink article you highlighted: > >Crap ;) > >A nice example of how Oracle Corp is the greatest myth generator of >them all !! It's all rather sad and embarressing isn't. > >Thanks for the headsup. Anyone in a position to get the note removed ? > >Cheers > >Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds >Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row >is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become >naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its >Leaf >Rows space is > >empty because of the implicit deletes caused by indexed columns value >changes. > > > >Fragmentated indexes degrade the performance of index range scan >operations. > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Richard Foote > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Cary Millsap > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). 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: [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 ma
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
Yes. However, every time he has replied to me, he has been confident that he IS right. Mind you, Richard, you are "immortalised" now ! Hemant At 05:04 PM 22-10-03 -0800, you wrote: So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT&p_id=182699.1 Pete "Controlling developers is like herding cats." Kevin Loney, Oracle DBA Handbook "Oh no, it's not. It's much harder than that!" Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines "Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. " are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: >Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements >about "index fragmentation" have been removed. > > >Cary Millsap >Hotsos Enterprises, Ltd. >http://www.hotsos.com > >Upcoming events: >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney >- SQL Optimization 101: 12/8-12 Dallas >- Hotsos Symposium 2004: March 7-10 Dallas >- Visit www.hotsos.com for schedule details... > > >-Original Message- >Richard Foote >Sent: Friday, October 17, 2003 6:29 AM >To: Multiple recipients of list ORACLE-L >Separate > >Hi Hemant, > >One word perfectly describes the Metalink article you highlighted: > >Crap ;) > >A nice example of how Oracle Corp is the greatest myth generator of >them all !! It's all rather sad and embarressing isn't. > >Thanks for the headsup. Anyone in a position to get the note removed ? > >Cheers > >Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds >Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row >is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become >naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its >Leaf >Rows space is > >empty because of the implicit deletes caused by indexed columns value >changes. > > > >Fragmentated indexes degrade the performance of index range scan >operations. > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Richard Foote > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Cary Millsap > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 http://www.fatcity.com >San Diego, California-- Mailing list and web hosting services >- >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the >message BODY, include a line containing: UNSUB ORACLE-L (or the name of >mailing list you want to be removed from). You may also send the HELP >command for other information (like subscribing). 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: [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-Ma
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
sold out na lahat-yung Tower 3 sa Eastwood is only for Lease?? At 11:34 AM 10/17/2003 -0800, you wrote: The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time. Hemant K Chitale <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/17/2003 11:42 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: I wonder if "it is not necessary to rebuild indexes" is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: >Hi Hemant, > >One word perfectly describes the Metalink article you highlighted: > >Crap ;) > >A nice example of how Oracle Corp is the greatest myth generator of them >all !! It's all rather sad and embarressing isn't. > >Thanks for the headsup. Anyone in a position to get the note removed ? > >Cheers > >Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds >Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become >naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its Leaf >Rows space is > >empty because of the implicit deletes caused by indexed columns value >changes. > > > >Fragmentated indexes degrade the performance of index range scan >operations. > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Richard Foote > 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). 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: [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: Jerome Roa 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Hi Hemant, How I dislike being immortalised ;) The note basically quoted me word for word on my feedback and that's fine, it's certainly an improvement on what was previously suggested (and yes, Oracle asked for my permission). A point I would add though is that the whole subject of how Oracle indexes function and the various cases when one should or should not rebuild indexes is not black and white and is not easily covered in a couple of paragraphs. There are always exceptions and oddities, the key is determining when these scenarios arrive and taking the appropriate action. Many books/articles emphasise the need to rebuild generally and often, I'm suggesting the emphasis should be far more considered and "practical". If anyone reading the note now questions the rebuild generally and often approach, then my comments serve their intentions. Cheers Richard - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, October 24, 2003 1:19 AM > > Yes. However, every time he has replied to me, he has been confident that he > IS right. > > Mind you, Richard, you are "immortalised" now ! > Hemant > > At 05:04 PM 22-10-03 -0800, you wrote: > >So now the blame rests solely on Richard for any material in the note that's > >wrong. :) > > > >Check the latest update: > >http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data b > >ase_id=NOT&p_id=182699.1 > > > >Pete > >"Controlling developers is like herding cats." > >Kevin Loney, Oracle DBA Handbook > >"Oh no, it's not. It's much harder than that!" > >Bruce Pihlamae, long-term Oracle DBA > > > > > > > >-Original Message- > >Millsap > >Sent: Wednesday, October 22, 2003 2:35 AM > >To: Multiple recipients of list ORACLE-L > > > > > >Oops, I didn't see that part. Thanks for the catch, Hemant. > > > > > >Cary Millsap > >Hotsos Enterprises, Ltd. > >http://www.hotsos.com > > > >Upcoming events: > >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney > >- SQL Optimization 101: 12/8-12 Dallas > >- Hotsos Symposium 2004: March 7-10 Dallas > >- Visit www.hotsos.com for schedule details... > > > > > >-Original Message- > >Hemant K Chitale > >Sent: Tuesday, October 21, 2003 10:15 AM > >To: Multiple recipients of list ORACLE-L > > > > > >Unfortunately, the lines > >"Unoccupied space on indexes occurs when a key value changes, and > >the > >index > > row is deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore, indexes whose columns are > > subject to intensive value change should be rebuilt periodically, since > > they become naturally fragmentated. " > >are still visible in Note 182699.1 > > > >Hemant > > > >At 08:29 AM 20-10-03 -0800, you wrote: > > >Fyi, Oracle updated note 182699.1 last Friday. The inaccurate > >statements > > >about "index fragmentation" have been removed. > > > > > > > > >Cary Millsap > > >Hotsos Enterprises, Ltd. > > >http://www.hotsos.com > > > > > >Upcoming events: > > >- Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney > > >- SQL Optimization 101: 12/8-12 Dallas > > >- Hotsos Symposium 2004: March 7-10 Dallas > > >- Visit www.hotsos.com for schedule details... > > > > > > > > >-Original Message- > > >Richard Foote > > >Sent: Friday, October 17, 2003 6:29 AM > > >To: Multiple recipients of list ORACLE-L > > >Separate > > > > > >Hi Hemant, > > > > > >One word perfectly describes the Metalink article you highlighted: > > > > > >Crap ;) > > > > > >A nice example of how Oracle Corp is the greatest myth generator of > > >them all !! It's all rather sad and embarressing isn't. > > > > > >Thanks for the headsup. Anyone in a position to get the note removed ? > > > > > >Cheers > > > > > >Richard > > > > > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds > > >Fragmentated Indexes (8.0->9.0) > > > > > > > >Index fragmentation occurs when a key value changes, and the index > >row > > >is > > > >deleted from one place (Leaf Block) and inserted into another. > > > > > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns > >are > > > >subject to value change must be rebuilt periodically since they > >become > > >naturally fragmentated. > > > > > > > >An index is considered to be 'fragmentated' when more than 20% of > >its > > >Leaf > > >Rows space is > > > >empty because of the implicit deletes caused by indexed columns value > > >changes. > > > > > > > >Fragmentated indexes degrade the performance of index range scan > > >operations. > > > > > > > > >-- > > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > > >-- > > >Author: Richard Foote > > > 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
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L 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: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L 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: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Rafiq, >John >What about gl_interface table indexes? I think indexes on all >*interface( >tables must be rebuild on a regular interval...I was building >indexes on >gl_interfaces and fnd_request* tables on monthly basis. Indeed the interface tables suffer as well. I would suggest a TRUNCate of these tables after processing monthend (or at an agreed time with the users), so the index will be chopped as well 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 ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi, I do rebuild index for table AP_INVOICES_ALL but it seems that no effect on extents. select owner, segment_name, tablespace_name, count(*), sum(bytes) 2 from sys.dba_extents 3 where segment_name like 'AP_INVOICES_N3' and tablespace_name='APX' 4 group by owner, segment_name, tablespace_name result : AP AP_INVOICES_N3 APX 45 46202880 and then : alter index AP.AP_INVOICES_N3 2* rebuild compute statistics online nologging tablespace APX but the extents still as above. any advice? regards On Tue, 14 Oct 2003, John Kanagaraj wrote: > Hemant, > > This applies on 11i only. I would rebuild all indexes supporting the > WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: hernawan 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared "M Rafiq" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L 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: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Not again :) At least we have to justify our pay :) Waleed Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 5:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparatePlease explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared "M Rafiq" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparateJohnWhat about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis.RegardsRafiqReply-To: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>Date: Tue, 14 Oct 2003 13:34:24 -0800Hemant,This applies on 11i only. I would rebuild all indexes supporting theWF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have beenworking on some AOL table(space) problems in the background and noticed thatin 11i by default, we are not be purging _all_ the WF data that we should bepurging. I believe the current Purge routine purges activity rows whosepersistence has expired and are marked 'TEMPORARY' and ignores those thatare COMPLETE (see below). My contention is that it should be deleting oldrows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.You could check this using the following SQLsselect activity_status, count(*)from applsys.wf_item_activity_statusesgroup by activity_status;select item_type,activity_status,count(*)fromapplsys.wf_item_activity_statuses where activity_status='COMPLETE'group by item_type,activity_status;Once the 'correct' purge is complete, the 'holey' indexes will need to berebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM toreasonable levels.Let me know what your install shows up.John KanagarajDB Soft IncPhone: 408-970-7002 (W)Grace - Getting something we do NOT deserveMercy - NOT getting something we DO deserveClick on 'http://www.needhim.org' for Grace and Mercy that is freelyavailable!** The opinions and facts contained in this message are entirely mine and donot reflect those of my employer or customers **-Original Message-Sent: Tuesday, October 14, 2003 8:39 AMTo: Multiple recipients of list ORACLE-LJohn,I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and thetable itself, occassionally].This Saturday I will also be rebuilding some ALR indexes.Which WorkFlow Indexes do you rebuild ?HemantAt 11:44 AM 13-10-03 -0800, you wrote:--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing)._Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: M Rafiq INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
List,
The %INTERFACE% tables (usually) consist of rows that are temporary in
nature. The indexes supporting them are 'fragmented' (the term can be argued
I suppose). I did test this out on the GL_INTERFACE_N2 index -
ANALYZE/VALIDATE and record INDEX_STATS, Rebuild index, ANALYZE/VALIDATE and
record INDEX_STATS again. The figures are below, but just to highlight a
few:
HEIGHT (Index depth) dropped from 3 to 2; BLKS_GETS_PER_ACCESS (expected
number of CR reads to get to a row) dropped from 12 to 3; the PCT_USED
(percentage of space allocated that is used) increased from 38% to 99%...
HEIGHT 3 2
BLOCKS 44804432
LF_ROWS 362409 22552
LF_BLKS 423075
LF_ROWS_LEN 12531538578797
LF_BLK_LEN 79487780
BR_ROWS 422974
BR_BLKS 58 1
BR_ROWS_LEN 134043 1919
BR_BLK_LEN 80288028
DEL_LF_ROWS 339857 0
DEL_LF_ROWS_LEN 119527410
DISTINCT_KEYS 20869 9548
MOST_REPEATED_KEY 38594 8430
BTREE_SPACE 34085664591528
USED_SPACE 12665581580716
PCT_USED38 99
ROWS_PER_KEY17.3659016 2.36196062
BLKS_GETS_PER_ACCESS12.1829508 3.68098031
PRE_ROWS0 0
PRE_ROWS_LEN0 0
For a detailed explanation, look at the definition of INDEX_STATS. YMMV, but
you will probably get the most from Non-unique indexes... (as in this case).
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Wednesday, October 15, 2003 2:19 PM
To: Multiple recipients of list ORACLE-L
Please explain why these indexes must be built.
What benefits do you see from it?
Are they quantifiable?
Jared
"M Rafiq" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 14 Oct 2003 13:34:24 -0800
Hemant,
This applies on 11i only. I would rebuild all indexes supporting the
WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been
working on some AOL table(space) problems in the background and noticed that
in 11i by default, we are not be purging _all_ the WF data that we should be
purging. I believe the current Purge routine purges activity rows whose
persistence has expired and are marked 'TEMPORARY' and ignores those that
are COMPLETE (see below). My contention is that it should be deleting old
rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes
141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.
You could check this using the following SQLs
select activity_status, count(*)
from applsys.wf_item_activity_statuses
group by activity_status;
select item_type,activity_status,count(*)
from
applsys.wf_item_activity_statuses where activity_status='COMPLETE'
group by item_type,activity_status;
Once the 'correct' purge is complete, the 'holey' indexes will need to be
rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to
reasonable levels.
Let me know what your install shows up.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Tuesday, October 14, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L
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:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Jared,
Those tables are transit type of tables and depending on your volume of
data, there are lot of deletes and inserts all the time resuling index
fragmentation(holes due to deletes) and space usage.
The rebuilding not only release the space but also reduces the index
fragmentation. If you don't have table truncation option for such tables
then it is much better to rebuid indexes on such tables at regular interval
to release space and for better performance.
As regard quantification, you many release sufficient amount of space if
your usage is higher. Here it was 7.3.4 database so no LMT involved.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 15 Oct 2003 13:19:24 -0800
Please explain why these indexes must be built.
What benefits do you see from it?
Are they quantifiable?
Jared
"M Rafiq" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 14 Oct 2003 13:34:24 -0800
Hemant,
This applies on 11i only. I would rebuild all indexes supporting the
WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been
working on some AOL table(space) problems in the background and noticed
that
in 11i by default, we are not be purging _all_ the WF data that we should
be
purging. I believe the current Purge routine purges activity rows whose
persistence has expired and are marked 'TEMPORARY' and ignores those that
are COMPLETE (see below). My contention is that it should be deleting old
rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes
141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.
You could check this using the following SQLs
select activity_status, count(*)
from applsys.wf_item_activity_statuses
group by activity_status;
select item_type,activity_status,count(*)
from
applsys.wf_item_activity_statuses where activity_status='COMPLETE'
group by item_type,activity_status;
Once the 'correct' purge is complete, the 'holey' indexes will need to be
rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to
reasonable levels.
Let me know what your install shows up.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and
do
not reflect those of my employer or customers **
-Original Message-
Sent: Tuesday, October 14, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L
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:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
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).
_
Concerned that messages may bounce because your Hotmail account has
exceeded
its 2MB storage limit? Get Hotmail Extra Storage!
http://join.msn.com/?PAGE=features/es
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: M Rafiq
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
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John
Thanks foe detailed explanation.
Regards
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 15 Oct 2003 14:34:47 -0800
List,
The %INTERFACE% tables (usually) consist of rows that are temporary in
nature. The indexes supporting them are 'fragmented' (the term can be argued
I suppose). I did test this out on the GL_INTERFACE_N2 index -
ANALYZE/VALIDATE and record INDEX_STATS, Rebuild index, ANALYZE/VALIDATE and
record INDEX_STATS again. The figures are below, but just to highlight a
few:
HEIGHT (Index depth) dropped from 3 to 2; BLKS_GETS_PER_ACCESS (expected
number of CR reads to get to a row) dropped from 12 to 3; the PCT_USED
(percentage of space allocated that is used) increased from 38% to 99%...
HEIGHT 3 2
BLOCKS 44804432
LF_ROWS 362409 22552
LF_BLKS 423075
LF_ROWS_LEN 12531538578797
LF_BLK_LEN 79487780
BR_ROWS 422974
BR_BLKS 58 1
BR_ROWS_LEN 134043 1919
BR_BLK_LEN 80288028
DEL_LF_ROWS 339857 0
DEL_LF_ROWS_LEN 119527410
DISTINCT_KEYS 20869 9548
MOST_REPEATED_KEY 38594 8430
BTREE_SPACE 34085664591528
USED_SPACE 12665581580716
PCT_USED38 99
ROWS_PER_KEY17.3659016 2.36196062
BLKS_GETS_PER_ACCESS12.1829508 3.68098031
PRE_ROWS0 0
PRE_ROWS_LEN0 0
For a detailed explanation, look at the definition of INDEX_STATS. YMMV, but
you will probably get the most from Non-unique indexes... (as in this case).
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Wednesday, October 15, 2003 2:19 PM
To: Multiple recipients of list ORACLE-L
Please explain why these indexes must be built.
What benefits do you see from it?
Are they quantifiable?
Jared
"M Rafiq" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/14/2003 03:49 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
John
What about gl_interface table indexes? I think indexes on all *interface(
tables must be rebuild on a regular interval...I was building indexes on
gl_interfaces and fnd_request* tables on monthly basis.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 14 Oct 2003 13:34:24 -0800
Hemant,
This applies on 11i only. I would rebuild all indexes supporting the
WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been
working on some AOL table(space) problems in the background and noticed that
in 11i by default, we are not be purging _all_ the WF data that we should be
purging. I believe the current Purge routine purges activity rows whose
persistence has expired and are marked 'TEMPORARY' and ignores those that
are COMPLETE (see below). My contention is that it should be deleting old
rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes
141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.
You could check this using the following SQLs
select activity_status, count(*)
from applsys.wf_item_activity_statuses
group by activity_status;
select item_type,activity_status,count(*)
from
applsys.wf_item_activity_statuses where activity_status='COMPLETE'
group by item_type,activity_status;
Once the 'correct' purge is complete, the 'holey' indexes will need to be
rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to
reasonable levels.
Let me know what your install shows up.
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-Original Message-
Sent: Tuesday, October 14, 2003 8:39 AM
To: Multiple recipients of list ORACLE-L
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:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: [EMAIL PROTECTED]
Fat City Network Services-- 8
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > Jared, > > Those tables are transit type of tables and depending on your volume of > data, there are lot of deletes and inserts all the time resuling index > fragmentation(holes due to deletes) and space usage. > > The rebuilding not only release the space but also reduces the index > fragmentation. If you don't have table truncation option for such tables > then it is much better to rebuid indexes on such tables at regular interval > to release space and for better performance. > > As regard quantification, you many release sufficient amount of space if > your usage is higher. Here it was 7.3.4 database so no LMT involved. > > Regards > Rafiq > > > > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 15 Oct 2003 13:19:24 -0800 > > Please explain why these indexes must be built. > > What benefits do you see from it? > > Are they quantifiable? > > Jared > > > > > > "M Rafiq" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/14/2003 03:49 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: > RE: Separate > > > John > What about gl_interface table indexes? I think indexes on all *interface( > tables must be rebuild on a regular interval...I was building indexes on > gl_interfaces and fnd_request* tables on monthly basis. > > Regards > Rafiq > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 14 Oct 2003 13:34:24 -0800 > > Hemant, > > This applies on 11i only. I would rebuild all indexes supporting the > WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been > working on some AOL table(space) problems in the background and noticed > that > in 11i by default, we are not be purging _all_ the WF data that we should > be > purging. I believe the current Purge routine purges activity rows whose > persistence has expired and are marked 'TEMPORARY' and ignores those that > are COMPLETE (see below). My contention is that it should be deleting old > rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes > 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. > > You could check this using the following SQLs > > select activity_status, count(*) > from applsys.wf_item_activity_statuses > group by activity_status; > > select item_type,activity_status,count(*) > from > applsys.wf_item_activity_statuses where activity_status='COMPLETE' > group by item_type,activity_status; > > Once the 'correct' purge is complete, the 'holey' indexes will need to be > rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to > reasonable levels. > > Let me know what your install shows up. > John Kanagaraj > DB Soft Inc > Phone: 408-970-7002 (W) > > Grace - Getting something we do NOT deserve > Mercy - NOT getting something we DO deserve > Click on 'http://www.needhim.org' for Grace and Mercy that is freely > available! > > ** The opinions and facts contained in this message are entirely mine and > do > not reflect those of my employer or customers ** > > -Original Message- > Sent: Tuesday, October 14, 2003 8:39 AM > To: Multiple recipients of list ORACLE-L > > > > 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: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: John Kanagaraj > 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:
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
> On Wed, 2003-10-15 at 18:04, M Rafiq wrote:> > Jared,> > > > Those tables are transit type of tables and depending on your volume of > > data, there are lot of deletes and inserts all the time resuling index > > fragmentation(holes due to deletes) and space usage.> > > > The rebuilding not only release the space but also reduces the index > > fragmentation. If you don't have table truncation option for such tables > > then it is much better to rebuid indexes on such tables at regular interval > > to release space and for better performance.> > Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL> create table bowie_test (ziggy number); Table created. SQL> insert into bowie_test values (1); 1 row created. SQL> insert into bowie_test values (2); 1 row created. SQL> insert into bowie_test values (3); 1 row created. SQL> insert into bowie_test values (4); 1 row created. SQL> insert into bowie_test values (6); 1 row created. SQL> insert into bowie_test values (7); 1 row created. SQL> insert into bowie_test values (8); 1 row created. SQL> insert into bowie_test values (9); 1 row created. SQL> insert into bowie_test values (10); 1 row created. SQL> insert into bowie_test values (100); 1 row created. SQL> commit; Commit complete. SQL> create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL> analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 0 0 We now delete a number of rows ... SQL> delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL> commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum ..." SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL> insert into bowie_test values (5); 1 row created. SQL> commit; Commit complete. SQL> analyze index bowie_test_idx validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be "detrimental" to performance. Cheers Richard
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
All of that is fair enough but the number of rows and the values you've chosen fit the point you wished to prove. The value "5" conveniently fits the range for an existing leaf block with empty space. The facts as I understand them are this : Index space freed by deleted entries can be reused ( by subsequent transactions ) so long as the indexed value 'belongs' in the leaf block which has free space. Index leaf blocks are only placed back on the free list when they are empty of entries. This means that given a constantly incrementing index value no free space will be reused unless whole index blocks are emptied by deletes. This is fine for working tables which are constantly filled and (totally) emptied but it can lead to large indexes for tables which preserve small amounts os data across the range of keys. Such monotonically increasing key values are pretty common in my experience. I agree though that index rebuilds are often necessary. For a while now we've had useful commands like coalesce that could combine logically adjacent, sparsely populated leaf blocks at far less cost than a rebuild. Regards, Mike Hately -Original Message- Sent: 16 October 2003 14:29 To: Multiple recipients of list ORACLE-L > On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > > Jared, > > > > Those tables are transit type of tables and depending on your volume of > > data, there are lot of deletes and inserts all the time resuling index > > fragmentation(holes due to deletes) and space usage. > > > > The rebuilding not only release the space but also reduces the index > > fragmentation. If you don't have table truncation option for such tables > > then it is much better to rebuid indexes on such tables at regular interval > > to release space and for better performance. > > Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL> create table bowie_test (ziggy number); Table created. SQL> insert into bowie_test values (1); 1 row created. SQL> insert into bowie_test values (2); 1 row created. SQL> insert into bowie_test values (3); 1 row created. SQL> insert into bowie_test values (4); 1 row created. SQL> insert into bowie_test values (6); 1 row created. SQL> insert into bowie_test values (7); 1 row created. SQL> insert into bowie_test values (8); 1 row created. SQL> insert into bowie_test values (9); 1 row created. SQL> insert into bowie_test values (10); 1 row created. SQL> insert into bowie_test values (100); 1 row created. SQL> commit; Commit complete. SQL> create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL> analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows ... SQL> delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL> commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum ..." SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL> insert into bowie_test values (5); 1 row created. SQL> commit; Commit complete. SQL> analyze index bowie_test_idx validate structure; Index analyzed. SQL> select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be "detrimental" to performanc
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Correction. Paragraph 4 should begin, "I agree though that index rebuilds are often unnecessary." Mike -Original Message- Sent: 16 October 2003 15:20 To: '[EMAIL PROTECTED]' All of that is fair enough but the number of rows and the values you've chosen fit the point you wished to prove. The value "5" conveniently fits the range for an existing leaf block with empty space. The facts as I understand them are this : Index space freed by deleted entries can be reused ( by subsequent transactions ) so long as the indexed value 'belongs' in the leaf block which has free space. Index leaf blocks are only placed back on the free list when they are empty of entries. This means that given a constantly incrementing index value no free space will be reused unless whole index blocks are emptied by deletes. This is fine for working tables which are constantly filled and (totally) emptied but it can lead to large indexes for tables which preserve small amounts os data across the range of keys. Such monotonically increasing key values are pretty common in my experience. I agree though that index rebuilds are often necessary. For a while now we've had useful commands like coalesce that could combine logically adjacent, sparsely populated leaf blocks at far less cost than a rebuild. Regards, Mike Hately -Original Message- Sent: 16 October 2003 14:29 To: Multiple recipients of list ORACLE-L Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be "detrimental" to performance. Cheers Richard E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Jared, Unfortunately at this stage I cannot quantify in numbers as I have left that job 5 months back. But dealing with Oracle Financials 10.7 with version 7.3.4, I observed it practically that this table and it is indexes (i think 4 or 5 indexes) require special attention for performance reasons. At my last employment that table was also used by customized application specially Manufactruring and stock locator application and heavy usage of inserts and deletes. If indexes were not rebuilt on that tablespace then I have seen that users were complaining about slowness of thier jobs. So I made it a maintenance routine to rebuild indexes on gl_interface table after monthly closing. Apart from this, as you cannot change code in Oracle Financials(although I did) , you to deal with indexes either through rebuilding them at regular intervals (may be six moths or a year) or adding new indexes based on your observation of certain codes. One monthly job called ACCRUAL REBUILD RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2 tables and time went down to 1 hour. In certain codes they were suppresing indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and after correcting that code it took less than second. Now another database of Order Entry System. When I joined I observed a lot of performance issues. After consulting with Development team,tracked all those tables with lot of regular deletes and inserts, rebuilt all indexes and got back 5GB of tablespace and performance was at their peak. All those application was based on RULE optimizer so we were not analyzing any table/indexes but based on experience with those applications, I was tracking those tables with large deletes and inserts through application(not data load) and rebuilding indexes with regular interval to keep smooth performance. In my opinion, we always need performance satisfaction of end user instead of numbers. If you have any specific question, please let me know. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 15 Oct 2003 22:04:24 -0800 The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > Jared, > > Those tables are transit type of tables and depending on your volume of > data, there are lot of deletes and inserts all the time resuling index > fragmentation(holes due to deletes) and space usage. > > The rebuilding not only release the space but also reduces the index > fragmentation. If you don't have table truncation option for such tables > then it is much better to rebuid indexes on such tables at regular interval > to release space and for better performance. > > As regard quantification, you many release sufficient amount of space if > your usage is higher. Here it was 7.3.4 database so no LMT involved. > > Regards > Rafiq > > > > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 15 Oct 2003 13:19:24 -0800 > > Please explain why these indexes must be built. > > What benefits do you see from it? > > Are they quantifiable? > > Jared > > > > > > "M Rafiq" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/14/2003 03:49 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: > RE: Separate > > > John > What about gl_interface table indexes? I think indexes on all *interface( > tables must be rebuild on a regular interval...I was building indexes on > gl_interfaces and fnd_request* tables on monthly basis. > > Regards > Rafiq > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 14 Oct 2003 13:34:24 -0800 > > Hemant, > > This applies on 11i only. I would rebuild all indexes supporting the > WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been > working on some AOL table(space) problems in the background and noticed > that > in 11i by default, we are not be purging _all_ the WF data that we should > be > purging. I believe the current Purge routine purges activity rows whose > persistence has expired and are marked 'TEMPORARY' and ignores those that > are COMPLETE (see below). My contention is that it should be deleting old > rows that are COMPLETEd... (Fyi, this is
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Thanks for the info. Too bad you can't get some metrics to show what was happening. Yes, user satisfaction is the ultimate indicator of tuning success, but there are also metrics to back it up, they just need to be collected before and after. Thanks, Jared "M Rafiq" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/16/2003 10:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate Jared, Unfortunately at this stage I cannot quantify in numbers as I have left that job 5 months back. But dealing with Oracle Financials 10.7 with version 7.3.4, I observed it practically that this table and it is indexes (i think 4 or 5 indexes) require special attention for performance reasons. At my last employment that table was also used by customized application specially Manufactruring and stock locator application and heavy usage of inserts and deletes. If indexes were not rebuilt on that tablespace then I have seen that users were complaining about slowness of thier jobs. So I made it a maintenance routine to rebuild indexes on gl_interface table after monthly closing. Apart from this, as you cannot change code in Oracle Financials(although I did) , you to deal with indexes either through rebuilding them at regular intervals (may be six moths or a year) or adding new indexes based on your observation of certain codes. One monthly job called ACCRUAL REBUILD RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2 tables and time went down to 1 hour. In certain codes they were suppresing indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and after correcting that code it took less than second. Now another database of Order Entry System. When I joined I observed a lot of performance issues. After consulting with Development team,tracked all those tables with lot of regular deletes and inserts, rebuilt all indexes and got back 5GB of tablespace and performance was at their peak. All those application was based on RULE optimizer so we were not analyzing any table/indexes but based on experience with those applications, I was tracking those tables with large deletes and inserts through application(not data load) and rebuilding indexes with regular interval to keep smooth performance. In my opinion, we always need performance satisfaction of end user instead of numbers. If you have any specific question, please let me know. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 15 Oct 2003 22:04:24 -0800 The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: > Jared, > > Those tables are transit type of tables and depending on your volume of > data, there are lot of deletes and inserts all the time resuling index > fragmentation(holes due to deletes) and space usage. > > The rebuilding not only release the space but also reduces the index > fragmentation. If you don't have table truncation option for such tables > then it is much better to rebuid indexes on such tables at regular interval > to release space and for better performance. > > As regard quantification, you many release sufficient amount of space if > your usage is higher. Here it was 7.3.4 database so no LMT involved. > > Regards > Rafiq > > > > > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 15 Oct 2003 13:19:24 -0800 > > Please explain why these indexes must be built. > > What benefits do you see from it? > > Are they quantifiable? > > Jared > > > > > > "M Rafiq" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 10/14/2003 03:49 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: > RE: Separate > > > John > What about gl_interface table indexes? I think indexes on all *interface( > tables must be rebuild on a regular interval...I was building indexes on > gl_interfaces and fnd_request* tables on monthly basis. > > Regards > Rafiq > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Tue, 14 Oct
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
You are right. As you agreed our ultimate goal is user satisfaction and I
believe in that, may be a old habit. I came into computer area because of
our dissatisfaction(being enduser) with our IT shop otherwise professionaly
I used to be a qualified professional accountant playing with numbers.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Thu, 16 Oct 2003 14:59:26 -0800
Thanks for the info.
Too bad you can't get some metrics to show what was happening.
Yes, user satisfaction is the ultimate indicator of tuning success, but
there are also metrics to back it up, they just need to be collected
before and after.
Thanks,
Jared
"M Rafiq" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/16/2003 10:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE:
RE: Separate
Jared,
Unfortunately at this stage I cannot quantify in numbers as I have left
that
job 5 months back. But dealing with Oracle Financials 10.7 with version
7.3.4, I observed it practically that this table and it is indexes (i
think
4 or 5 indexes) require special attention for performance reasons.
At my last employment that table was also used by customized application
specially Manufactruring and stock locator application and heavy usage of
inserts and deletes. If indexes were not rebuilt on that tablespace then I
have seen that users were complaining about slowness of thier jobs. So I
made it a maintenance routine to rebuild indexes on gl_interface table
after
monthly closing.
Apart from this, as you cannot change code in Oracle Financials(although I
did) , you to deal with indexes either through rebuilding them at regular
intervals (may be six moths or a year) or adding new indexes based on your
observation of certain codes. One monthly job called ACCRUAL REBUILD
RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2
tables
and time went down to 1 hour. In certain codes they were suppresing
indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and
after correcting that code it took less than second.
Now another database of Order Entry System. When I joined I observed a lot
of performance issues. After consulting with Development team,tracked all
those tables with lot of regular deletes and inserts,
rebuilt all indexes and got back 5GB of tablespace and performance was at
their peak.
All those application was based on RULE optimizer so we were not analyzing
any table/indexes but based on experience with those applications, I was
tracking those tables with large deletes and inserts through
application(not
data load) and rebuilding indexes with regular interval to keep smooth
performance.
In my opinion, we always need performance satisfaction of end user instead
of numbers.
If you have any specific question, please let me know.
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 15 Oct 2003 22:04:24 -0800
The 'better performance' part is what I would like
to see some metrics on.
How much better? Is it worth the trouble?
If your indexes continually build up to the same
size, what is being gained by saving some space
for a period of time?
Thanks,
Jared
On Wed, 2003-10-15 at 18:04, M Rafiq wrote:
> Jared,
>
> Those tables are transit type of tables and depending on your volume of
> data, there are lot of deletes and inserts all the time resuling index
> fragmentation(holes due to deletes) and space usage.
>
> The rebuilding not only release the space but also reduces the index
> fragmentation. If you don't have table truncation option for such
tables
> then it is much better to rebuid indexes on such tables at regular
interval
> to release space and for better performance.
>
> As regard quantification, you many release sufficient amount of space
if
> your usage is higher. Here it was 7.3.4 database so no LMT involved.
>
> Regards
> Rafiq
>
>
>
>
>
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Wed, 15 Oct 2003 13:19:24 -0800
>
> Please explain why these indexes must be built.
>
> What benefits do you see from it?
>
> Are they quantifiable?
>
> Jared
>
>
>
>
>
> "M Rafiq" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 10/14/2003 03:49 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
> cc:
> Subject:RE: re Rebuilding Indexes in Oracle Apps --
was
RE:
> RE: Separate
>
>
> Jo
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0->9.0) > >Index fragmentation occurs when a key value changes, and the index row is >deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are >subject to value change must be rebuilt periodically since they become naturally fragmentated. > >An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is >empty because of the implicit deletes caused by indexed columns value changes. > >Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
My experience in contacting Oracle regarding modifying of notes on MetaLink has not been very satisfying. I did take the opportunity to voice my dissatisfaction by using the poll at the top of the article to indicate that I would not recommend this article to others. Jared Richard Foote <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 10/17/2003 04:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0->9.0) > >Index fragmentation occurs when a key value changes, and the index row is >deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are >subject to value change must be rebuilt periodically since they become naturally fragmentated. > >An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is >empty because of the implicit deletes caused by indexed columns value changes. > >Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about "index fragmentation" have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0->9.0) > >Index fragmentation occurs when a key value changes, and the index row is >deleted from one place (Leaf Block) and inserted into another. > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are >subject to value change must be rebuilt periodically since they become naturally fragmentated. > >An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is >empty because of the implicit deletes caused by indexed columns value changes. > >Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Thanks for the info Cary. Jared On Mon, 2003-10-20 at 09:29, Cary Millsap wrote: > Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements > about "index fragmentation" have been removed. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney > - SQL Optimization 101: 12/8-12 Dallas > - Hotsos Symposium 2004: March 7-10 Dallas > - Visit www.hotsos.com for schedule details... > > > -Original Message- > Richard Foote > Sent: Friday, October 17, 2003 6:29 AM > To: Multiple recipients of list ORACLE-L > Separate > > Hi Hemant, > > One word perfectly describes the Metalink article you highlighted: > > Crap ;) > > A nice example of how Oracle Corp is the greatest myth generator of > them > all !! It's all rather sad and embarressing isn't. > > Thanks for the headsup. Anyone in a position to get the note removed ? > > Cheers > > Richard > > >Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds > Fragmentated Indexes (8.0->9.0) > > > >Index fragmentation occurs when a key value changes, and the index row > is > >deleted from one place (Leaf Block) and inserted into another. > > > > Deleted Leaf Rows are not reused. Therefore indexes whose columns are > >subject to value change must be rebuilt periodically since they become > naturally fragmentated. > > > >An index is considered to be 'fragmentated' when more than 20% of its > Leaf > Rows space is > >empty because of the implicit deletes caused by indexed columns value > changes. > > > >Fragmentated indexes degrade the performance of index range scan > operations. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Richard Foote > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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).
