RE: Rebuild Indexes
- From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Feb 2004 14:24:29 +0100
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
-----------------------------------------------------------------
Other related posts:
