 | | | Rebuild Indexes | Rebuild Indexes 2004-02-18 - By John Kanagaraj
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@(protected)
>[mailto:oracle-l-bounce@(protected)] On Behalf Of vidya kalyanaraman
>Sent: Tuesday, February 17, 2004 8:10 PM
>To: oracle-l@(protected)
>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@(protected)
> >
> >-- --Original Message-- --
> >From: Juan Miranda [mailto:j.miranda@(protected)]
> >Sent: Tuesday, February 17, 2004 7:24 AM
> >To: oracle-l@(protected)
> >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@(protected)
>[mailto:oracle-l-bounce@(protected)] En
> >nombre de Niall Litchfield
> >Enviado el: martes, 17 de febrero de 2004 13:20
> >Para: oracle-l@(protected)
> >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?
> >
> >Niall Litchfield
> >Oracle DBA
> >Audit Commission
> >+44 117 975 7805
> >
> >
> >
> > >-- --Original Message-- --
> > >From: j.miranda@(protected)
> > >Sent: 17 February 2004 10:41
> > >To: j.miranda@(protected); oracle-l@(protected)
> > >Subject: RE: Rebuild Indexes
> > >
> > >
> > >
> > >Hi
> > >
> > >May script do just original post (vidya kalyanaraman) as for.
> > >No more.
> > >
> > >May be is not adecuate for your system.
> > >Anyway you can stop it when you want: a simple CTRL+C.
> > >
> > >In my mail I say:
> > > "Take care in production because VALIDATE STRUCTURE do some
> > >tipe of lock. "
> > >
> > >I have a very busy database and I execute this script very
> > >care and control.
> > >I do it once a year and this reduced to 1/3 the space ocupied
> > >by indexes.
> > >
> > >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. "
> > >
> > >Sorry if it caused problems in your system but ask Oracle
>for a better
> > >rebuild method, not me.
> > >
> > >I forgot to add an "alter session set
>SORT_AREA_SIZE=25000000; ". This > > >
> > >
> > > do it faster.
> >
> >
> > >
> > >Grettings.
> > >
> > >
> > >-- --Mensaje original-- --
> > >De: oracle-l-bounce@(protected)
> > >[mailto:oracle-l-bounce@(protected)] En
> > >nombre de Richard Foote
> > >Enviado el: viernes, 13 de febrero de 2004 14:59
> > >Para: oracle-l@(protected)
> > >Asunto: Re: Rebuild Indexes
> > >
> > >When to rebuild indexes, hummm, this is all rather new and
>exciting ;)
> > >
> > >Juan, I notice with interest that your script:
> > >
> > > - 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
> > >
> > >Running such a script on our production databases at my
> > >current site would:
> > >
> > > - cripple performance for up to approximately 14 hours
> > >(depending on
> > >database) performing just the analyze step
> > > - rebuild every single one of our larger, 3+ level
> > >indexes (don 't want
> > >to think about the cost of this)
> > >
> > >for practically *no* benefit.
> > >
> > >The 3+ criteria does "accidentally " rebuild the handful we 've
> > >identified as
> > >being candidates for an occasional rebuild but boy, what an
> > >incredible price
> > >to pay !!
> > >
> > >I think not ...
> > >
> > >Richard Foote
> > >
> > >-- -- Original Message -- --
> > >From: "Juan Miranda " <j.miranda@(protected) >
> > >To: <oracle-l@(protected) >
> > >Sent: Friday, February 13, 2004 7:14 PM
> > >Subject: RE: Rebuild Indexes
> > >
> > >
> > >
> > >Hi
> > >
> > >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
> > >tipe of lock.
> > >
> > >
> > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >+++++++++++=
> > >+++
> > >-- Juan Miranda Serm=E1tica 06/AGO/2002
> > >-- Utiliza VALIDATE STRUCTURE - > OJO en producci=F3n.
>Puede generar =
> > >errores
> > >ORA-00054 (See ORA-00054.ora-code.com).
> > >-- OJO nolog - > NO usar con stand by
> > >
> > >
> > >set serveroutput on size 1000000
> > >set pagesize 0
> > >set feedback off
> > >set echo off
> > >set trimspool on
> > >
> > >spool c:\reb_index1.sql
> > >
> > >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 ');
> > >
> > > 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 ');
> > >
> > > FOR t IN (select owner, index_name from dba_indexes where
> > >owner not =
> > >in
> > >( 'SYS ', 'SYSTEM ') order by owner,index_name) LOOP
> > >
> > > dbms_output.put_line( 'prompt --Analizando
> > > '||t.owner|| '. '||t.index_name);
> > >
> > > dbms_output.put_line( 'Analyze index
> > > '||t.owner|| '. '||t.index_name|| '
> > >validate structure; ');
> > >
> > > dbms_output.put_line( 'select ' || ' ' ' ' || 'Alter index '
> > >|| t.owner =
> > >||
> > > '. ' || t.index_name || ' rebuild online; ' || ' ' ' '|| ' from
> > >index_stats =
> > >where
> > >(height > 2) or
> > >(10 <=3Ddecode(lf_rows_len,0,NULL,((del_lf_rows_len/lf_rows_len
> > >)*100))); ')=
> > >;
> > >
> > > END LOOP;
> > >
> > > dbms_output.put_line( 'prompt spool off ');
> > > dbms_output.put_line( 'spool off ');
> > > dbms_output.put_line( '@(protected):\reb_index2.sql ');
> > >
> > >END;
> > >/
> > >spool off
> > >
> > >@(protected):\reb_index1.sql
> > >
> > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > >+++++++++++=
> > >+++
> > >
> > >
> > >
> > >
> > >-- --Mensaje original-- --
> > >De: oracle-l-bounce@(protected)
> > >[mailto:oracle-l-bounce@(protected)] =
> > >En
> > >nombre de vidya kalyanaraman
> > >Enviado el: viernes, 13 de febrero de 2004 9:46
> > >Para: oracle-l@(protected)
> > >Asunto: Rebuild Indexes=20
> > >
> > >Hi
> > > It may be a silly thing to ask, but I am stuck right now.=20
> > > I have been given a task to find out the indexes which need
>to be=20
> > >rebuilt. There are around 3000 Indexes. I know I can run
> > >the following =
> > >
> > >command
> > > "analyze index <Index > VALIDATE STRUCTURE " =20
> > >
> > >for a single index and then find the rows from index_state
> > >based on=20
> > >del_lf_rows_len/lf_rows_len > 20%. =20
> > >Does anyone have a script for dynamically finding out the
> > >indexes that=20
> > >are the candidates for rebuilding? How do you normally handle=20
> > >situations like this?
> > >
> > >TIA
> > >Vidya
> > >
> > >
> > >
> > >
> > >
> > >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> > >To unsubscribe send email to: oracle-l-request@(protected)
> > >put 'unsubscribe ' in the subject line.
> > >--
> > >Archives are at http://www.freelists.org/archives/oracle-l/
> > >FAQ is at http://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@(protected)
> > >put 'unsubscribe ' in the subject line.
> > >--
> > >Archives are at http://www.freelists.org/archives/oracle-l/
> > >FAQ is at http://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@(protected)
> > >put 'unsubscribe ' in the subject line.
> > >--
> > >Archives are at http://www.freelists.org/archives/oracle-l/
> > >FAQ is at http://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@(protected)
> > >put 'unsubscribe ' in the subject line.
> > >--
> > >Archives are at http://www.freelists.org/archives/oracle-l/
> > >FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
> > >
> > >
> > >
> > >
> >
> >
> >
> >**********************************************************************
> >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@(protected)
> >put 'unsubscribe ' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://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@(protected)
> >put 'unsubscribe ' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://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@(protected)
> >put 'unsubscribe ' in the subject line.
> >--
> >Archives are at http://www.freelists.org/archives/oracle-l/
> >FAQ is at http://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@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|