OEM 9i.

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 13 Nov 2008 09:48:47 +0000
Message-ID: <7765c8970811130148h30d7314bue54dcd1ac726caab@mail.gmail.com>

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

Received on Thu Nov 13 2008 - 03:48:47 CST

Original text of this message