Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RBO and Rebuild Index !!! sorry We still use RBO !!!!

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

From: Jonathan Lewis <>
Date: Mon, 30 Oct 2006 19:15:14 -0000
Message-ID: <02ff01c6fc57$ba5f7450$0200a8c0@Primary>

Is there anything special about the way the tables are used, or are do they simply have a steady rate of inserts, with some deletes, and updates of non-indexed columns ?

Anything you can think of that would lead to an unusual distortion in the way space in the indexes was gradually, but not fully, released and left in an unusable state ?

Typical examples - bulk deletes with subsequent inserts to higher values with some trailing data not deleted; FIFO implemented through indexes with some trailing data not deleted.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

> 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 - 64bit Production
> PL/SQL Release - Production
> CORE Production
> TNS for HPUX: Version - Production
> NLSRTL Version - 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
> 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

No virus found in this incoming message. Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.17/505 - Release Date: 27/10/2006

Received on Mon Oct 30 2006 - 13:15:14 CST

Original text of this message