转载 检查索引碎片(oracle)

早上收到Olga Email, 希望我检查下某个oracle数据库索引碎片，并且rebuild index.

 

原因：每月定期执行的索引重建任务应为某些原因失败了，并且延续了半年，客户怀疑是否有索引碎片，希望重建索引。

 

情况:不知道这个oracle是干么用的，确切地说是第一次看到，所以只能用一般的常识去处理这个help call.

 

步骤如下：

 

1.  确认基本信息。　

登入数据库，找到专门存放index 的tablespace，并且这个tablespace下所有index的owner都是tax.将index专门存放在一个独立的tablespace, 与数据表的tablespace分离，是常用的数据库设计方法。

 

2. 查找哪些index需要重建

通过anlyze index .... validate structure命令可以分析单个指定的index，并且将单个index 分析的结果存放到 index_stats试图下。一般判断的依据是：

 

• height >4
• pct_used < 50%
• del_lf_rows / lf_rows +0.001 > 0.03

3. google上下载了遍历所有index脚本

发现anlyze index .... validate structure只能填充单个index分析信息，于是google了下，从网上下了个Loop 脚本，遍历索引空间下所有的索引名字，并且可以把所有index的分析信息存放到自己建立的一个用户表中。

 

4. anlyze index　锁定index

发现下载的脚本不好用，应为anlyze index　在分析索引前要争取独占锁，锁住index,  很明显有些index正在被应用系统的使用，所以运行anlyze失败。这里吸取的教训是，尽量晚上做这种事。但是本人比较喜欢准时回家，所以在语句中添加Exception Handler, 抛出anlyze index执行失败的那些index 名称，使脚本正常运行完毕。并且根据打印到前台的index name手动执行那些index分析。

 

5. 总结

虽然发现522个index中有160个符合上面的判断的依据。但是发现索引都不大，而那些拥有百万leaf的索引又没有符合上面的判断条件，所以结论是无需index rebuild online. 没有啥碎片。

 

６.客户于是问，什么时候可以rebuild index呢？

回答他，rebuild index online, 对那些有大量DML操作的大索引是有益的。可以每个月季度做一次针对较大索引的rebuild。通常哪怕rebuild index online也会造成I/O争用，所以有无online意义不大，可以放到３－５个晚上，分批执行rebuild index, 锁定index,不让用户用　（没有用户等入的时候），并且加上paralle 8关键字，应为发现数据库服务器有8个cpu processors.

 

后续: 啥事情也没有干，按时回家了。

analyze index ... validate structure 然后查询 index_stats

DEL_LF_ROWS于LF_ROWS的比例 这是最基本的了

可以通过程序来实现该功能

注意：analyze index 和查询index_stats 必须在同一个session中

create table MONITORINDEX
(
  INDEX_NAME  VARCHAR2(50),
  DEL_LF_ROWS NUMBER,
  LF_ROWS     NUMBER,
  RATE        NUMBER(4,2),
  MONITORDATE DATE default sysdate not null
)

create or replace procedure analyzeindex is
  v_sql varchar2(100);
begin
  for a in (select index_name from all_indexes where owner = USER) loop
    v_sql := ' analyze index ' || a.index_name || ' validate structure';
    execute immediate v_sql;
    insert into monitorindex
      (index_name, del_lf_rows, lf_rows, rate)
      select name,
             del_lf_rows,
             lf_rows,
             round(del_lf_rows * 100 / (lf_rows + del_lf_rows), 2)
        from index_stats;
  end loop;
end analyzeindex;

create table MONITORINDEX

(

  INDEX_NAME  VARCHAR2(50),

  DEL_LF_ROWS NUMBER,

  LF_ROWS     NUMBER,

  RATE        NUMBER(4,2),

  MONITORDATE DATE default sysdate not null

)

create or replace procedure analyzeindex is

  v_sql varchar2(100);

begin

  for a in (select index_name from all_indexes where owner = USER) loop

    v_sql := ' analyze index ' || a.index_name || ' validate structure';

    execute immediate v_sql;

    insert into monitorindex

      (index_name, del_lf_rows, lf_rows, rate)

      select name,

             del_lf_rows,

             lf_rows,

             round(del_lf_rows * 100 / (lf_rows + del_lf_rows), 2)

        from index_stats;

  end loop;

end analyzeindex;

call analyzeindex();

create or replace procedure clearallindex is

  v_sql varchar2(100);

begin

  for a in (select index_name from all_indexes where owner = USER) loop

    v_sql := ' alter index ' || a.index_name || ' rebuild';

    execute immediate v_sql;

  end loop;

end clearallindex;

call clearallindex();