 | | | Rebuild Indexes | Rebuild Indexes 2004-02-17 - By Juan Miranda
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|