|
[oracle-l]
||
[Date Prev]
[11-2006 Date Index]
[Date Next]
||
[Thread Prev]
[11-2006 Thread Index]
[Thread Next]
Re: RBO and Rebuild Index !!! sorry We still use RBO !!!!
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: chris@xxxxxxxxxxxxxxxxxxxxx
- Date: Wed, 1 Nov 2006 12:03:20 +0100
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@xxxxxxxxxxxxxxxxxxxxx> wrote:
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
--
http://www.freelists.org/webpage/oracle-l
|
|
