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

On 10/30/06, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> 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
>
>
>

Greetings Jonathan,

Sorry, I couldn't get back to you immediatley.

Its a Bulk Delete every 15 minutes, they delete based on inactive flag.

-- 
Regards & Thanks
BN

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