RE: Rebuild Indexes
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Feb 2004 22:59:49 -0800
Vidya,
If you are collecting (and storing) STATSPACK data, you might want to
baseline V$SYSSTAT values and look for spurts in 'leaf node splits' after
rebuilds....
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Disappointment is inevitable, but Discouragement is optional!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
>-----Original Message-----
>From: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of vidya kalyanaraman
>Sent: Tuesday, February 17, 2004 8:10 PM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: Re: Rebuild Indexes
>
>
>Hi
> At times, it becomes really very very difficult to make the customer
>accept and understand the fact that the rebuilding is not
>going to be of
>any great help regarding performance. They have also read some
>documents/whitepapaers, which claim that the performance
>improvement is
>great after rebuild.
>How to handle situations like this?
>What we have decided to do is, take some couple of harmless
>indexes and
>then try to rebuild them. This is just to keep the customer happy.
>Any thoughts on this?
>
>Thanks and Regards
>Vidya
>
>DENNIS WILLIAMS wrote:
>
>>Juan
>> In the Dec 2001 edition of the Student Guide, that
>statement appears on
>>page 14-12. I think the paragraph preceding the one you
>quoted sets the
>>context for the statement a bit:
>>
>>How to Solve B-Tree Index Performance Degradation
>>
>>The more levels an index has, the less efficient it may be.
>Additionally, an
>>index with many rows deleted might not be efficient.
>Typically, if 15% of
>>the index data is deleted, then you should consider
>rebuilding the index.
>>
>>You should rebuild your indexes regularly. However, this can be a
>>time-consuming task, especially if the base table is very
>large . . . the
>>paragraph then goes on to describe the index rebuilding options.
>>
>>Juan - If you search the archive for this list, you will find
>where the
>>topic of rebuilding indexes has been discussed many times.
>This should give
>>you some more background on the issue. Second, while the
>Student Guides are
>>great for helping pass the OCP, they are primarily oriented
>toward people
>>new to Oracle. As you become more experienced in Oracle, you
>should dig
>>deeper, particularly on vague topics like "when to rebuild
>indexes". Third,
>>while the sentence in the Student Guide does say "rebuild your indexes
>>regularly", the context of the statement is really discussing all the
>>wonderful new Oracle9i features that make rebuilding indexes
>a less onerous
>>task.
>>
>>Dennis Williams
>>DBA, OCP
>>Lifetouch, Inc.
>>dwilliams@xxxxxxxxxxxxx
>>
>>-----Original Message-----
>>From: Juan Miranda [mailto:j.miranda@xxxxxxxxxxxx]
>>Sent: Tuesday, February 17, 2004 7:24 AM
>>To: oracle-l@xxxxxxxxxxxxx
>>Subject: RE: Rebuild Indexes
>>
>>
>>
>>Oracle 9i Performance Tuning
>>Student Guide Vol1.
>>D11299GC10
>>July 2001
>>
>>12- Application tuning
>> 12-11 bitmap indexes.
>>
>>Juan Miranda
>>Oracle Certified Professional.
>>
>>
>>-----Mensaje original-----
>>De: oracle-l-bounce@xxxxxxxxxxxxx
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] En
>>nombre de Niall Litchfield
>>Enviado el: martes, 17 de febrero de 2004 13:20
>>Para: oracle-l@xxxxxxxxxxxxx
>>Asunto: RE: Rebuild Indexes
>>
>>Hi Juan
>>
>>I couldn't actually find your quote in the Performance Tuning
>Guide for 8.1=
>>.7, 9.2 or 10.1. Is it perhaps some other book?=20
>>
>>Niall Litchfield
>>Oracle DBA
>>Audit Commission
>>+44 117 975 7805=20
>>
>>
>>
>>>-----Original Message-----
>>>From: j.miranda@xxxxxxxxxxxx=20
>>>Sent: 17 February 2004 10:41
>>>To: j.miranda@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>>>Subject: RE: Rebuild Indexes
>>>=20
>>>=20
>>>=20
>>>Hi
>>>=20
>>>May script do just original post (vidya kalyanaraman) as for.
>>>No more.=20
>>>=20
>>>May be is not adecuate for your system.=20
>>>Anyway you can stop it when you want: a simple CTRL+C.
>>>=20
>>>In my mail I say:=20
>>> "Take care in production because VALIDATE STRUCTURE do some=20
>>>tipe of lock."
>>>=20
>>>I have a very busy database and I execute this script very=20
>>>care and control.
>>>I do it once a year and this reduced to 1/3 the space ocupied=20
>>>by indexes.
>>>=20
>>>I will no discuss if it is good or not to rebuild indexes but...
>>>Oracle Performance Tuning Manual 12-12:
>>>"You sould rebuild your indexes regularly. However, this can be a
>>>time-consuming task, especially if the base table es very large."
>>>=20
>>>Sorry if it caused problems in your system but ask Oracle
>for a better
>>>rebuild method, not me.
>>>=20
>>>I forgot to add an "alter session set
>SORT_AREA_SIZE=3D25000000;". This=
>>>
>>>
>> > do it faster.
>>
>>
>>>=20
>>>Grettings.
>>>=20
>>>=20
>>>-----Mensaje original-----
>>>De: oracle-l-bounce@xxxxxxxxxxxxx=20
>>>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] En
>>>nombre de Richard Foote
>>>Enviado el: viernes, 13 de febrero de 2004 14:59
>>>Para: oracle-l@xxxxxxxxxxxxx
>>>Asunto: Re: Rebuild Indexes
>>>=20
>>>When to rebuild indexes, hummm, this is all rather new and
>exciting ;)
>>>=20
>>>Juan, I notice with interest that your script:
>>>=20
>>> - performs an analyze validate structure on all indexes
>>> - rebuilds all indexes if guilty of having more than 2 levels
>>> - rebuilds all indexes with more than 10% deleted rows
>>>=20
>>>Running such a script on our production databases at my=20
>>>current site would:
>>>=20
>>> - cripple performance for up to approximately 14 hours=20
>>>(depending on
>>>database) performing just the analyze step
>>> - rebuild every single one of our larger, 3+ level=20
>>>indexes (don't want
>>>to think about the cost of this)
>>>=20
>>>for practically *no* benefit.
>>>=20
>>>The 3+ criteria does "accidentally" rebuild the handful we've=20
>>>identified as
>>>being candidates for an occasional rebuild but boy, what an=20
>>>incredible price
>>>to pay !!
>>>=20
>>>I think not ...
>>>=20
>>>Richard Foote
>>>=20
>>>----- Original Message -----
>>>From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
>>>To: <oracle-l@xxxxxxxxxxxxx>
>>>Sent: Friday, February 13, 2004 7:14 PM
>>>Subject: RE: Rebuild Indexes
>>>=20
>>>=20
>>>=20
>>>Hi
>>>=20
>>>Try this.
>>>This do not use a cursor so you can stop it when you what.
>>>Take care in production because VALIDATE STRUCTURE do some=20
>>>tipe of lock.
>>>=20
>>>=20
>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>+++++++++++=3D
>>>+++
>>>-- Juan Miranda Serm=3DE1tica 06/AGO/2002
>>>-- Utiliza VALIDATE STRUCTURE -> OJO en producci=3DF3n.
>Puede generar =3D
>>>errores
>>>ORA-00054.
>>>-- OJO nolog -> NO usar con stand by
>>>=20
>>>=20
>>>set serveroutput on size 1000000
>>>set pagesize 0
>>>set feedback off
>>>set echo off
>>>set trimspool on
>>>=20
>>>spool c:\reb_index1.sql
>>>=20
>>>DECLARE
>>> dbname varchar2(20);
>>> wday varchar2(11);
>>>BEGIN
>>> dbms_output.put_line('set echo off');
>>> dbms_output.put_line('set feedback off');
>>> dbms_output.put_line('set head off');
>>>=20
>>> dbms_output.put_line('spool c:\reb_index2.sql');
>>> dbms_output.put_line('prompt set feedback on');
>>> dbms_output.put_line('prompt set echo on');
>>> dbms_output.put_line('prompt spool c:\reb_index2.log');
>>>=20
>>> FOR t IN (select owner, index_name from dba_indexes where=20
>>>owner not =3D
>>>in
>>>('SYS','SYSTEM') order by owner,index_name) LOOP
>>>=20
>>> dbms_output.put_line('prompt --Analizando
>>>'||t.owner||'.'||t.index_name);
>>>=20
>>> dbms_output.put_line('Analyze index=20
>>>'||t.owner||'.'||t.index_name||'
>>>validate structure;');
>>>=20
>>> dbms_output.put_line('select ' || '''' || 'Alter index '=20
>>>|| t.owner =3D
>>>||
>>>'.' || t.index_name || ' rebuild online;' || ''''|| ' from=20
>>>index_stats =3D
>>>where
>>>(height > 2) or
>>>(10<=3D3Ddecode(lf_rows_len,0,NULL,((del_lf_rows_len/lf_rows_len
>>>)*100)));')=3D
>>>;
>>>=20
>>> END LOOP;
>>>=20
>>> dbms_output.put_line('prompt spool off');
>>> dbms_output.put_line('spool off');
>>> dbms_output.put_line('@c:\reb_index2.sql');
>>>=20
>>>END;
>>>/
>>>spool off
>>>=20
>>>@c:\reb_index1.sql
>>>=20
>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>+++++++++++=3D
>>>+++
>>>=20
>>>=20
>>>=20
>>>=20
>>>-----Mensaje original-----
>>>De: oracle-l-bounce@xxxxxxxxxxxxx=20
>>>[mailto:oracle-l-bounce@xxxxxxxxxxxxx] =3D
>>>En
>>>nombre de vidya kalyanaraman
>>>Enviado el: viernes, 13 de febrero de 2004 9:46
>>>Para: oracle-l@xxxxxxxxxxxxx
>>>Asunto: Rebuild Indexes=3D20
>>>=20
>>>Hi
>>> It may be a silly thing to ask, but I am stuck right now.=3D20
>>> I have been given a task to find out the indexes which need
>to be=3D20
>>>rebuilt. There are around 3000 Indexes. I know I can run=20
>>>the following =3D
>>>=20
>>>command
>>> "analyze index <Index> VALIDATE STRUCTURE " =3D20
>>>=20
>>>for a single index and then find the rows from index_state=20
>>>based on=3D20
>>>del_lf_rows_len/lf_rows_len > 20%. =3D20
>>>Does anyone have a script for dynamically finding out the=20
>>>indexes that=3D20
>>>are the candidates for rebuilding? How do you normally handle=3D20
>>>situations like this?
>>>=20
>>>TIA
>>>Vidya
>>>=20
>>>=20
>>>=20
>>>=20
>>>=20
>>>----------------------------------------------------------------
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>----------------------------------------------------------------
>>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>>put 'unsubscribe' in the subject line.
>>>--
>>>Archives are at //www.freelists.org/archives/oracle-l/
>>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>>-----------------------------------------------------------------
>>>=20
>>>----------------------------------------------------------------
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>----------------------------------------------------------------
>>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>>put 'unsubscribe' in the subject line.
>>>--
>>>Archives are at //www.freelists.org/archives/oracle-l/
>>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>>-----------------------------------------------------------------
>>>=20
>>>=20
>>>=20
>>>----------------------------------------------------------------
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>----------------------------------------------------------------
>>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>>put 'unsubscribe' in the subject line.
>>>--
>>>Archives are at //www.freelists.org/archives/oracle-l/
>>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>>-----------------------------------------------------------------
>>>=20
>>>----------------------------------------------------------------
>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>>----------------------------------------------------------------
>>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>>put 'unsubscribe' in the subject line.
>>>--
>>>Archives are at //www.freelists.org/archives/oracle-l/
>>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>>-----------------------------------------------------------------
>>>=20
>>>=20
>>>
>>>
>>
>>
>>
>>**********************************************************************
>>This email contains information intended for
>>the addressee only. It may be confidential
>>and may be the subject of legal and/or
>>professional privilege. Any dissemination,
>>distribution, copyright or use of this
>>communication without prior permission of
>>the sender is strictly prohibited.
>>**********************************************************************
>>
>>----------------------------------------------------------------
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>----------------------------------------------------------------
>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>put 'unsubscribe' in the subject line.
>>--
>>Archives are at //www.freelists.org/archives/oracle-l/
>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>-----------------------------------------------------------------
>>
>>----------------------------------------------------------------
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>----------------------------------------------------------------
>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>put 'unsubscribe' in the subject line.
>>--
>>Archives are at //www.freelists.org/archives/oracle-l/
>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>-----------------------------------------------------------------
>>----------------------------------------------------------------
>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>>----------------------------------------------------------------
>>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>>put 'unsubscribe' in the subject line.
>>--
>>Archives are at //www.freelists.org/archives/oracle-l/
>>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>>-----------------------------------------------------------------
>>
>>
>>
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
