oracle 索引的维护

Posted 2021-12-10 by wukaiqiang; tagged with none

索引的优化和维护
目的：
通过对数据库中的索引进行优化和维护，提交检索的速度和效率。
原理：
随着数据库的使用，不可避免地对基本表进行插入，更新和删除，这样导致叶子行在索引中被删除，使该索引产生碎片。插入删除越频繁的表，索引碎片的程度也越高。碎片的产生使访问和使用该索引的I/O成本增加。碎片较高的索引必须重建以保持最佳性能。当索引的层数增大时，I/O的成本增加，检索的效率开始降低，oracle建议当索引的层数大于3时，则应当对此索引进行重建以提交效率。随着表记录的增加，相应的索引也要增加。如果一个索引的next值设置不合理(太小)，索引段的扩展变得很频繁。索引的extent太多，检索时的速度和效率就会降低。当然还有就是由于一些人为的原因或者系统表的迁移，有可能造成索引的失效，也会降低检索的效率和速度。
通过对索引进行分析，找出碎片比例占索引20%以上的索引；通过查询和索引相关的数据字典和表找出失效的索引、层数大于3的索引和被扩展超过10次的索引；将这些问题索引数据统计到临时表。分别对这些索引进行重建或重新设置 next值（尽量增大，到合理的数值），从而达到优化索引和提高数据库效率的目的。
脚本：
DECLARE

   v_index_name varchar2(100);
   v_analyze_str varchar2(300);
   height        number;
   lf_rows       number;
   del_lf_rows   number;
   v_ex_count    number;
   v_idx_owner   varchar2(30);
   v_tab_name    varchar2(50);
   v_tabspa_name varchar2(50);      
   v_idx_status  varchar2(10);      
   CURSOR analyze_index IS
   SELECT index_name,table_owner,table_name,tablespace_name,status
   FROM user_indexes; 

BEGIN

   OPEN analyze_index;
   FETCH  analyze_index INTO

v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

   WHILE analyze_index%FOUND LOOP 
   if  v_idx_status ='INVALID' then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,null,null,

‘该索引已失效’,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);

   end if;
   if v_idx_status ='VALID' then
   v_analyze_str := 'analyze index '||v_index_name||' validate structure';
   EXECUTE IMMEDIATE v_analyze_str;       
   SELECT HEIGHT,DECODE(LF_ROWS,0,1,LF_ROWS),DEL_LF_ROWS
   INTO   height,lf_rows,del_lf_rows
   FROM   index_stats;
   if (del_lf_rows/lf_rows)>0.2 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,del_lf_rows/lf_rows,null,v_ex_count,'该索引的碎片太多，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);
   end if;
   if height>=4 and (del_lf_rows/lf_rows) <=0.2 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,height,v_ex_count,'该索引的层数超过3，建议重建',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);
   end if;
   select count(*)
   into v_ex_count
   from user_extents
   where segment_name =v_index_name;     
   if v_ex_count>=11 and (del_lf_rows/lf_rows)<=0.2  and height<4 then
   insert into lifemenu.index_stats_prob
   values(v_index_name,null,null,v_ex_count,'该索引扩展超过10次，建议重建时增大参数next',v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status);       
   end if;
   end if;
   commit;
   v_index_name :='';
   v_analyze_str :='';
   v_idx_status :='';

FETCH analyze_index INTO
v_index_name,v_idx_owner,v_tab_name,v_tabspa_name,v_idx_status;

   END LOOP;
   CLOSE analyze_index;

END;
执行结果：
执行结果存储在表index_stats_prob中，表的结构和说明如下：
create table INDEX_STATS_PROB
(
INDEX_NAME VARCHAR2(30) not null,//索引名称
FRAG_PCT NUMBER, //碎片比例
HEIGHT NUMBER, //索引层数
EXTENT_TIME NUMBER, //扩展次数
COM VARCHAR2(50), //建议处理方法
OWNER VARCHAR2(30) not null,//所属用户
TABLE_NAME VARCHAR2(50), //所属表
TABLESPACE_NAME VARCHAR2(50), //所在表空间
STATUS VARCHAR2(10) //状态
)
其中问题索引主要就有四类：
1 碎片比例大于20%的
2 索引层数大于3的且不属于第一类的
3 索引扩展次数超过10的且不属于第一类和第二类的
4 失效的索引
之所以这样处理，是因为碎片比较多的问题索引必须进行处理，其次是层数多的索引，再其次是扩展次数比较多的索引。
在测试数据库ld05sh中的执行结果中，没有发现第二类和第四类的问题索引。
下列是分别在各个用户下执行脚本所耗费的时间的列表：
用户 脚本执行时间
ACCTMAN 1115秒
LIFEBASE 2576秒
LIFEDATA 22633秒
LIFELOG 1741秒
LIFEMAN 143秒
LIFEREPT 1836秒
REINSMAN 1475秒

处理建议：
1． 对于第一类和第二类问题索引，即碎片比例大于20%的问题索引和索引层数大于3的且不属于第一类的，都建议进行重建。可以执行下列语句来找出这些索引。同时我们对这两类问题索引也提供了它们扩展的次数，因此如果发现它们被扩展的次数过大，那么在重建的时候也要注意增大索引的next参数。
SELECT *
FROM INDEX_STATS_PROB
WHERE FRAG_PCT IS NOT NULL OR HEIGHT IS NOT NULL
ORDER BY FRAG_PCT DESC;
重建语句如下：
alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging
如果出于空间或其他考虑，不能重建索引，可以整理索引：
alter index用户名.索引名 coalesce
2． 对于第三类索引，由于发现在ld05sh中此类索引特别多，所以建议重建扩展次数在50次以上的索引，当然如果在其他环境中发现这类索引的数量不是很多，需要综合考虑，按照扩展次数的多少来确定重建的优先次序，即扩展次数越多的，越优先考虑重建，重建时主要是增大索引的next参数。
执行下列语句来找出这些索引：
SELECT *
FROM INDEX_STATS_PROB
WHERE FRAG_PCT IS NULL AND HEIGHT IS NULL AND EXTENT_TIME>10
ORDER BY EXTENT_TIME DESC;
重建语句相同，注意重建时要增大next 参数：
alter index 用户名.索引名 rebuild
tablespace 表空间名
storage(initial 初始值 next 扩展值)
nologging
3．重建索引后，原有的索引被删除，这样会造成表空间的碎片。
整理表空间的碎片　alter tablespace 表空间名 coalesce

4. 另外，优化索引还有一些普遍的原则，如：
   1）定期对数据更新（主要是删除）频繁的表重建索引
   2）建议索引不要建立在系统表空间内
   3）建议索引建立在db_block_size比较大的表空间中
   4）记录太少的表，应当少建或不建索引；经常处理的业务表（插入、删除、修改），应在查询允许的情况下尽量减少索引。

具体操作：
登录要分析的数据库
在lifemenu用户下创建存放分析数据的表index_stats_prob，脚本如下：
create table INDEX_STATS_PROB
(
INDEX_NAME VARCHAR2(30) not null,
FRAG_PCT NUMBER,
HEIGHT NUMBER,
EXTENT_TIME NUMBER,
COM VARCHAR2(50),
OWNER VARCHAR2(30) not null,
TABLE_NAME VARCHAR2(50),
TABLESPACE_NAME VARCHAR2(50),
STATUS VARCHAR2(10)
)
将此表授权给其他的用户：
grant all on index_stats_prob to 用户名
用其他用户登录要进行索引优化的数据库，执行PL/SQL脚本，然后参照处理建议对索引进行重建，从而达到优化索引，提高效率的目的。
各个用户下的执行时间都不同，具体可以参照执行结果中的脚本耗费时间来设计脚本执行计划。
验证说明：
重建索引后，检索的速度和效率都会得到提高，用户可以通过前后的检索所需时间的比较来验证。

建议：
1．首先需要肯定的一点，该文章总结的非常好，基本上考虑到了在实际使用过程中对索引维护尤其是在判断索引要不要重建的问题。
2．一般情况下，建议对数据库统计信息的收集采用dbms_stats来进行。
3．需要注意的是‘3）建议索引建立在db_block_size比较大的表空间中’，这句话需要调整为，如果你对数据库的表和索引根据业务需求进行优化，制定了该表和索引的基本数据块的大小，并且相应的创建了相应数据块大小的表空间，而且在数据库内存参数中也要相应的运用不同数据块大小的内存的情况下，需要注意该用法。在一般的情况下，没有对表和索引的数据块大小进行设计，默认采用数据库db_block_size的大小，表空间也没有考虑不同的数据块大小的设置，所以，简单起见，就不需要考虑它了。换句话说，想要考虑，就必须从全局的角度来考虑和设计。
4．明显看到在测试系统中信息收集的PL/SQL执行的效率并不是太高，当然选择在数据库维护窗口阶段执行，还是可以的。
5．从以上脚本来看，建议先进行评估对数据库进行统计信息的收集，包括表和索引收集对业务运行的影响，收集的时间长度估算等，然后再进行统计信息的收集，收集后，才来分析那些20%的高水位的指标的索引进行重新创建。否则，有可能按照规则筛选出来的结果很多，在实际实施的时候，限制的因素就多，存储空间，维护时间，系统资源处理能力以及重新创建后产生的效果等。
6．在确定了需要创建的索引之后，就需要分析创建索引的可行性。
1）。创建索引执行过程的时间。
2）。创建索引的过程对系统运行的影响，尤其是索引扩展段比较多的情况下，后台进程SMON 需要清理临时段的时间也需要考虑。
3）。重建索引所需要注意索引的存储参数以及表空间的存储参数控制。
4）。结合业务应用对表和索引的使用，尽量对索引的重建有个比较清晰的认识，尽量做到全局的权衡。
5）。尽量具体索引问题具体分析解决。