 | | | Rebuild Indexes | Rebuild Indexes 2004-02-18 - By Michael McMullen
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@(protected) >
To: <oracle-l@(protected) >
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@(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
> >-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
> >
> >
> >
>
>
> -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
> 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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|