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

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.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "BN" <bnsarma_at_gmail.com> To: "oracle_L_list" <oracle-l_at_freelists.org> Sent: Monday, October 30, 2006 6:52 PM Subject: 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
>

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

--
http://www.freelists.org/webpage/oracle-l