Manage rebuilding of indexes...
Submitted by dave on Tue, 09/23/2008 - 11:56
I've been doing some speed testing of rebuilding indexes. I have been adding the PARALLEL clause to my alter index ... rebuild; statements but it has been suggested to me to also run multiple indexes rebuilds in parallel. So if I am wanting to run 4 rebuilds in parallel I could try to make 4 .SQL scripts with the appropriate commands. But what if one finishes early? Then I will be left with only 3 running. Here is a solution: 1) Create a table to hold the list of indexes you want to rebuild:
create table rebuild_indexes ( index_name varchar2(30), sid number, start_date date, end_date date ); insert into rebuild_indexes (index_name) select index_name from user_indexes where status='UNUSABLE' order by index_name; commit;2) Open up 1..n SQLPLUS sessions and run the following anon PL/SQL block. It will pick the next index from the list and start rebuilding it. It marks the index it is building so that multiple processes will not rebuild the same index:
SET SERVEROUTPUT ON DECLARE my_sid NUMBER; my_total NUMBER; my_processed NUMBER; BEGIN FOR c1 in (SELECT index_name FROM rebuild_indexes) LOOP SELECT COUNT(*) INTO my_total FROM rebuild_indexes; SELECT COUNT(*) INTO my_processed FROM rebuild_indexes WHERE sid IS NOT NULL; SELECT sid INTO my_sid FROM rebuild_indexes WHERE index_name=c1.index_name; -- If no sid is working on this index yet... IF my_sid IS NULL THEN -- Mark as in progress UPDATE rebuild_indexes SET sid=USERENV('sid'), start_date=SYSDATE WHERE index_name=c1.index_name; COMMIT; -- Rebuild the index dbms_output.put_line('Rebuilding ' || c1.index_name || ' Status: ' || my_processed || ' of ' || my_total); execute immediate 'alter index ' || c1.index_name || ' rebuild nologging parallel 64'; UPDATE rebuild_indexes SET end_date=SYSDATE WHERE index_name=c1.index_name; COMMIT; END IF; END LOOP; END; /3) Status queries to check progress:
-- Rebuild Detail - Completed select index_name, round((end_date-start_date)*1440) as minutes from rebuild_indexes where end_date is not null; -- Number completed select count(*) from rebuild_indexes where sid is not null; -- In progress select * from rebuild_indexes where start_date is not null and end_date is null;4) When you are done:
select TO_CHAR(MIN(START_DATE), 'Month DD, YYYY HH24:MI:SS') START_DATE, TO_CHAR(MAX(END_DATE), 'Month DD, YYYY HH24:MI:SS') END_DATE from rebuild_indexes;-- Dave