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

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