Saturday, October 16, 2010

Index tune using job scheduler in Oracle 10g

0 comments
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:

1. A temporary table  TEMP_INDEX_STAT created to store the index statistics.

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

2. Create procedure GEN_INDEX_STAT to generate index statistics and store

CREATE OR REPLACE PROCEDURE GEN_INDEX_STAT
(PRM_SCHEMA VARCHAR2)
IS
CURSOR INDEX_CUR IS
SELECT INDEX_NAME
FROM DBA_INDEXES d
WHERE OWNER = prm_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
'||PRM_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;

3. Create a job class

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;

4. Create a job

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

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 a table to log the operations

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.

Procedure to rebuild index

CREATE OR REPLACE PROCEDURE REBUILD_INDEX(PRM_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 '||PRM_SCHEMA||'.'||REC.NAME||' REBUILD ONLINE';
INSERT INTO RUNNING_CMDS
VALUES(V_STR);
COMMIT;
EXECUTE IMMEDIATE v_str;
END LOOP;
END REBUILD_INDEX;

8. Create a job to execute index rebuilding process

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.

Leave a Reply

Labels