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@xxxxxxxxx>
To: "oracle_L_list" <oracle-l@xxxxxxxxxxxxx>
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


Other related posts: