OEM 9i.

From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 13 Nov 2008 09:48:47 +0000
Hi all,

Does anyone here know what the criteria used for the 9i OEM event Test
'Index Rebuild' are? I have to say that the Event Reference is not promising
since it does rather seem to reinforce an old myth or two.


> When an indexed value is updated in the table, the old value is deleted
> from the index and the new value is inserted into a separate part of the
> index. The space released by the old value can never be used again. As
> indexed values are updated or deleted, the amount of unusable space within
> the index increases, a condition called index stagnation. Because a
> stagnated index contains a mixture of data and empty areas, scans of the
> index will be less efficient.
>
> This event test monitors whether indexes specified by the Index name, Index
> owner, Indexed object name, and Indexed object owner parameters suffer from
> index stagnation. If an index has stagnation, an alert is generated
>

The documentation then says that the test runs ANALYZE INDEX ... VALIDATE
STRUCTURE but not what criteria are used to recommend indexes for rebuild.
Further more the list of indexes that are emailed and output ends in ....
suggesting that more have been identified, but not listing them.

I'm not after rerunning the "when to rebuild an index" discussion, but
rather after the criteria that OEM2 used to identify such indexes, so that I
can generate a complete list. My 'guess' would be that it's the old

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

as referenced in now removed metalink notes, but I thought someone on this
list might know for sure.

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info
OEM 9i.

Other related posts:
