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%000000; ". 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�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
-- --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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
