Re: Rebuild Indexes
- From: "Michael McMullen" <ganstadba@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Feb 2004 08:08:17 -0500
Also, monitor your redo logs, you'll see lots of redo that goes along with
the leaf node splits.
----- Original Message -----
From: "vidya kalyanaraman" <vidya.kalyanaraman@xxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 18, 2004 4:34 AM
Subject: Re: Rebuild Indexes
> John
> Thanks for the pointer. We have to start collecting STATSPACK data, as
> this is the first time we are doing this exercise for this customer.
> Regards
> Vidya
>
> John Kanagaraj wrote:
>
> >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
> >-----------------------------------------------------------------
> >
> >
> >
>
>
> ----------------------------------------------------------------
> 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:
