Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuild Indexes

RE: Rebuild Indexes

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Tue, 17 Feb 2004 12:20:24 +0000
Message-Id: <s032072e.010@bristol21.bristol.ac>


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_at_sermatica.es=20
> Sent: 17 February 2004 10:41
> To: j.miranda_at_sermatica.es; oracle-l_at_freelists.org
> 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_at_freelists.org=20
> [mailto:oracle-l-bounce_at_freelists.org] En
> nombre de Richard Foote
> Enviado el: viernes, 13 de febrero de 2004 14:59
> Para: oracle-l_at_freelists.org
> 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_at_sermatica.es>
> To: <oracle-l_at_freelists.org>
> 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_at_freelists.org=20
> [mailto:oracle-l-bounce_at_freelists.org] =3D
> En
> nombre de vidya kalyanaraman
> Enviado el: viernes, 13 de febrero de 2004 9:46
> Para: oracle-l_at_freelists.org
> 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_at_freelists.org
> 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
> -----------------------------------------------------------------
>=20

> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> 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
> -----------------------------------------------------------------
>=20
>=20
>=20

> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> 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
> -----------------------------------------------------------------
>=20

> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> 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
> -----------------------------------------------------------------
>=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_at_freelists.org 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
-----------------------------------------------------------------
Received on Tue Feb 17 2004 - 06:20:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US