使用job scheduler重建索引

使用job scheduler重建索引

http://www.dbaliu.com/?p=159

Posted on November 7, 2011 by admin

在数据库优化中Index 是一个重要因素。在Oracle 10g中，rebuild online是组建索引的一个重要特性。
在以下情况下应该考虑重建索引：
• 删除的行数超过了总数的30%, 就是说 DEL_LF_ROWS / LF_ROWS > 0.3.
• 索引的高度大于4.
• 叶子节点的行数 (‘LF_ROWS’) 明显小于叶子块的数量（‘LF_BLKS’）这表明大量的数据被删除了, 需要重建索引.
• BLOCK_GETS 大于 5

在一个数据库中可能存在成千上万条索引，这样手工的去重建可能不是一件容易的事情，所以可以考虑使用job scheduler 来重建索引。

具体步骤如下：
1.创建一张temp_index_stat表，其结构和index_stats类似；
2.创建一个gen_index_stat存储过程，它用于分析索引同时将数据存放在temp_index_stat表中；
3.为了不对生产库造成影响，创建一个LOW_GROUP级别的资源用户组的job class — low_priority_class；
4.创建一个job — get_index_stat_job；
5.创建另外一张表running_cmds；
6.创建另外一个procedure — rebuild_index，用于重建索引；
7.创建另外一个job rebuild_index_job 来调用上述的程序来重建索引。

请确保不是在业务高峰期，如果这样可能会出现resource timeout的错误，而导致job运行失败。

脚本如下：

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 WNER = ‘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;
/

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;
/

如果你想按照预定的时间安排job，可以设置start_time

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;
/

到这时我们可以收集schema中所有index，现在可以按照我们所说的条件rebulid index。

CREATE TABLE RUNNING_CMDS
(
CMD VARCHAR2(200)
);

这张表用于存放操作命令，这样当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;
/

可以根据自己的需求设置start_date。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-747933/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17252115/viewspace-747933/