Index Rebuild Using Job Scheduler

Indexes are the key element for any Database Tuning. In Oracle 10g, REBUILD ONLINE is one of the key features that makes it possible to reorganize the

indexes during production time.
An index should be considered for rebuilding under any of the following conditions:
• The percentage of deleted rows exceeds 30% of the total, i.e. if DEL_LF_ROWS / LF_ROWS > 0.3.
• If the ‘HEIGHT’ is greater than 4.
• If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the

index should be rebuilt.
• BLOCK_GETS greater than 5
In our system we have 180000 indexes. So rebuilding manually is not an easy task. So, we considered job scheduler for index rebuilding.
Steps:
1. Create a table TEMP_INDEX_STAT, which is similar to INDEX_STATS
2. Create a procedure GEN_INDEX_STAT. It will analyze an index and store that data in TEMP_INDEX_STAT
3. Create a job class LOW_PRIORITY_CLASS with LOW_GROUP resource consumer group, so that the job might not hamper production transaction.
4. Create a job GET_INDEX_STAT_JOB
5. Create a table RUNNING_CMDS
6. Create a procedure REBUILD_INDEX, which will REBUILD the indexes
7. Create another job REBUILD_INDEX_JOB. This job will call the above procedure to rebuild the indexes.
Please be sure that the load in the database is not at the peak. If yes, you may get resource timeout error during the job execution period and the job may

be failed.

Scripts:

CREATE TABLE TEMP_INDEX_STAT
AS
SELECT *
FRO INDEX_STATS
WHERE 1=2;

 

CREATE OR REPLACE PROCEDURE GEN_INDEX_STAT
(HONCHO_SCHEMA VARCHAR2)
IS
CURSOR INDEX_CUR IS
SELECT INDEX_NAME
FROM DBA_INDEXES d
WHERE OWNER = 'HONCHO_SCHEMA'
AND NOT EXISTS
(
SELECT 1
FROM temp_index_stat b
WHERE d.index_Name = b.NAME
)
AND INDEX_TYPE = 'NORMAL';
v_str VARCHAR2(500);
BEGIN
FOR INDEX_REC IN INDEX_CUR LOOP
v_str := 'ANALYZE INDEX
'||HONCHO_SCHEMA||'.'||INDEX_REC.INDEX_NAME||'
VALIDATE STRUCTURE ';
EXECUTE IMMEDIATE v_str;
v_str := 'insert into TEMP_INDEX_STAT select * from index_stats';
EXECUTE IMMEDIATE v_str;
COMMIT;
END LOOP; --
END GEN_INDEX_STAT;

You may set START_DATE if you want to schedule the following job.

begin
dbms_scheduler.create_job_class(
job_class_name => 'LOW_PRIORITY_CLASS',
resource_consumer_group => 'LOW_GROUP',
logging_level => DBMS_SCHEDULER.LOGGING_FULL,
log_history => 60,
comments => 'LOW PRIORITY JOB CLASS');
end;

BEGIN
dbms_scheduler.create_job
(
job_name=> 'GEN_INDEX_STAT_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin GEN_INDEX_STAT(''T24''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Generate Index Stat',
job_class=>'LOW_PRIORITY_CLASS'
);
END;

Upto this point, we have collected statistics for all indexes in our schema. Now we need to REBUILD the indexes according to the conditions described above.

CREATE TABLE RUNNING_CMDS
(
CMD VARCHAR2(200)
);

This table will store the commands so that we could easily identify which index is in rebuild process during the running of the following job.

CREATE OR REPLACE PROCEDURE REBUILD_INDEX(HONCHO_SCHEMA VARCHAR2)
IS
CURSOR CUR IS
SELECT NAME
FROM TEMP_INDEX_STAT a
WHERE (HEIGHT >= 4
OR
(del_lf_rows/lf_rows > 0.3 AND lf_rows > 0)
or (blks_gets_per_access > 4)
)
AND EXISTS
(
SELECT 1
FROM dba_indexes d
WHERE a.NAME = D.index_name
AND d.index_type <> 'LOB'
);
v_str varchar2(200);
BEGIN
execute immediate 'truncate table RUNNING_CMDS';
FOR REC IN CUR LOOP
v_str := 'ALTER INDEX '||HONCHO_SCHEMA||'.'||REC.NAME||' REBUILD ONLINE';
INSERT INTO RUNNING_CMDS
VALUES(V_STR);
COMMIT;
EXECUTE IMMEDIATE v_str;
END LOOP;
END REBUILD_INDEX;

BEGIN
dbms_scheduler.create_job
(
job_name=> 'REBUILD_INDEX_JOB',
job_type=> 'PLSQL_BLOCK',
job_action=>'begin REBUILD_INDEX(''T24''); end;',
START_DATE=>NULL,
REPEAT_INTERVAL=>NULL,
ENABLED=>TRUE,
AUTO_DROP=>FALSE,
COMMENTS=>'Rebuild Index',
job_class=>'LOW_PRIORITY_CLASS'
);
END;

You may set START_TIME according to your requirement.