RE: Rebuild Indexes
- From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Feb 2004 11:41:02 +0100
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@xxxxxxxxxxxxx [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
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@xxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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.
-- 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('@c:\reb_index2.sql');
END;
/
spool off
@c:\reb_index1.sql
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
+++
-----Mensaje original-----
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] =
En
nombre de vidya kalyanaraman
Enviado el: viernes, 13 de febrero de 2004 9:46
Para: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
