Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: LS Cheng <exriscer_at_gmail.com>
Date: Wed, 1 Nov 2006 12:03:20 +0100
Message-ID: <6e9345580611010303w1ed65da9w3af13d4592dba470@mail.gmail.com>


Be cautious in 9.2.0.4 to use v$sql_plan, quite a few bugs, for example ora-600 in alert everytime you access it :-P

On 11/1/06, Chris Dunscombe <chris_at_thedunscombes.f2s.com> wrote:
>
> Quoting BN <bnsarma_at_gmail.com>:
>
> > 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
>
> >>
> >> ----- 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.
> >> >
> >> >
> >> > 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
> >> >
> > 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
> >
>
>
> Hi,
>
> Just a couple of other questions that might help.
>
> 1. Do the bulk deletes always run every 15 mins? I.e. is it possible that
> at
> some time the deletes don't run and the table HWM gets bumped and hence
> the
> index grows as well.
>
> 2. What is the PCTUSED on the table? This could also lead to the table
> being
> larger than it really needs to be. Although this isn't really related to
> the
> index issue.
>
> 3. Is the offending SQL doing a full index scan (or a large range scan),
> if so
> then the possible impact of 1. will cause a big performance impact. With
> the
> RBO you can easily get a full index scan if there's an ORDER BY in the
> statement.
>
> I've seen exctly this type of situation before and the only real fix was
> to
> schedule a regular index rebuild as occasionly the bulk deletes didn't run
> for
> some reason and the index grew to an unacceptable size.
>
> As you're on Oracle 9 you could look at v$sql_plan to find the
> execution plan if
> it's still in the sga.
>
> Hope this helps.
>
> Cheers,
>
> Chris Dunscombe
>
> www.christallize.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 01 2006 - 05:03:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US