| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: any problem rebuilding indexes used for replication
Re the space savings: that may or not be important. Guess it depends on
how tight storage space is in your environment, and how much of an impact
it may make on your backups.
Re the performance: Some queries could perform better due to the index data being packed into fewer blocks, as you are expecting.
It could also result in decreased performance for inserts, for the same reason.
An index with all data tightly packed into nearly full blocks makes it a
good
candidate for excessive block splits when data is inserted into the table.
You could allow a generous PCTFREE when you rebuild to alleviate this, but then there would not be much point in rebuilding the index.
You might consider targeting your most expensive operations ( eg. the SQL that causes the users and/or the system the most pain ) and then look at the indexes involved to see if rebuilding them would increase or decrease performance.
Lots of discussion lately on this list on how to go about doing that.
( Optimizing Oracle Performance -
http://www.oreilly.com/catalog/optoraclep/ )
HTH Jared
renu r <renu_p_r_at_yahoo.com>
Sent by: ml-errors_at_fatcity.com
11/06/2003 02:29 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: any problem rebuilding indexes used for replication
Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes.
One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want.
I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table.
One other simple useful script is :
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel > 2
This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild.
Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help.
Jared.Still_at_radisys.com wrote:
I'm curious, how have you identified the fragmentation?
What benefits do you expect from the rebuild of the indexes?
Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes?
Jared
renu r <renu_p_r_at_yahoo.com>
Sent by: ml-errors_at_fatcity.com
11/05/2003 06:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
cc:
Subject: any problem rebuilding indexes used for
replication
Hello,
I have to rebuild some primary key indexes due to excessive
fragmentation. It is rebuild not drop and create. We have multi master
replication running. Is there any problem to replication if I do that. Has
anyone tried it? TIA.
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: Jared.Still_at_radisys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Nov 06 2003 - 16:54:33 CST
 |
 |