Rebuild Indexes 2004-02-13 - By Richard Foote
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ática 06/AGO/2002
-- Utiliza VALIDATE STRUCTURE - > OJO en producción. 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 <=decode(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
Hi
It may be a silly thing to ask, but I am stuck right now.
I have been given a task to find out the indexes which need to be
rebuilt. There are around 3000 Indexes. I know I can run the following
command
"analyze index <Index > VALIDATE STRUCTURE "
for a single index and then find the rows from index_state based on
del_lf_rows_len/lf_rows_len > 20%.
Does anyone have a script for dynamically finding out the indexes that
are the candidates for rebuilding? How do you normally handle
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
