RBO and Rebuild Index !!! sorry We still use RBO !!!!

Greetings,

Yes, one of the APP still uses RBO,  we are pushing the Vender to test the
app in CBO.

The DB is:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production


Certain queries  slow down until we rebuild the Indexes,  Most of these
indexes are  1,2 or 3 column indexes.

Identifed a few indexes that grow as big as Table (Blocks, and Size from
user_segments), we rebuild them and the query is back to normal.

Please note all these tables and indexes are on LMTS
SEGMENT_SPACE_MANAGEMENT=MANAUL

Most of the SQL is using sequential Reads. Disk responce time is < 20 milli
secs for all the datafiles.

Storage people have cleared the storage.

I have asked the DEV Team to send me the sql, so that I can do a TKPROF to
get more details.

I want to track  DML (alter table monitoring)  , is this doable for RBO

Is there any thing else I can look into?
--
Regards & Thanks
BN

Other related posts: