Re: RBO and Rebuild Index !!! sorry We still use RBO !!!!
- From: Chris Dunscombe <chris@xxxxxxxxxxxxxxxxxxxxx>
- To: bnsarma@xxxxxxxxx
- Date: Wed, 01 Nov 2006 09:47:50 +0000
Quoting BN <bnsarma@xxxxxxxxx>:
On 10/30/06, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 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@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.
>
>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
