Oracle 索引维护

96818

　　一、索引的分析
　　根据ORACLE文档所描述，如下情况要考虑重建索引
　　1. analyze index your_index_name validate structure;
　　2. 查询索引碎片：
　　select name,del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
　　frag_pct from index_stats
　　一般如果该索引的frag_pct>10%，那么就需要rebuild index
　　当然analyze index your_index_name validate structure除了可以分析素因是否需要重建外，还可以让还能获得其他有用信息，能让了解当前索引的大小结构高度等等，以确定优化方案。还要特别留意另一个知识点：“该命令将会导致锁！”而且操作大表，该命令执行时间会相当长，会导致生产因此挂起一堆处理该表的进程！
　　二、索引高度
　　Oracle在表示从索引根块到叶子块遍历所涉及的块数时用了两个含义稍有不同的术语。第一个是高度（HEIGHT），这是指从根块到叶子块遍历所需的块数。使用ANALYZE INDEX  VALIDATE STRUCTURE命令分析索引后，可以从INDEX_STATS视图找到这个高度（HEIGHT）值。另一个术语是BLEVEL，这是指分支层数，与HEIGHT相差1（BLEVEL不把叶子块层算在内）。收集统计信息后，可以在诸如USER_INDEXES之类的常规字典表中找到BLEVEL值。
　　select index_name, blevel, num_rows from user_indexes where table_name = 'BIG_TABLE';
　　INDEX_NAME          BLEVEL    NUM_ROWS
　　------------------    ----------  ----------
　　BIG_TABLE_PK           2      10441513
　　BLEVEL为2，这说明HEIGHT为3，要找到叶子需要两个I/O（访问叶子本身还需要第三个I/O）。所以，要从这个索引中获取任何给定的键值，共需要3个I/O：
　　三、索引问题
　　这里要注意索引有一个不利的地方，就是索引一定会影响DML的性能，往不带索引表中插入值的速度和带索引的表中插入的速度相比，不带索引的表的插入速度一定更快，原因很明显，DML语句要进行维护索引的开销，所以会慢，这无须做实验就能证明了。另外随着索引的不断增大，维护的开销将越来越大，如果一张表的索引数量再一增多，开销将会进一步增大！
　　三：控制索引数量
　　生产中要严格控制索引的数量，避免某些更新频繁的表更新的性能太低影响了系统的正常运行，如何控制索引的数量呢？
　　1、首先可以执行如下命令获取需要监控的索引的语句，然后执行提取出来的语句
　　select 'alter index '||owner||'.'||index_name||' monitoring usage;'
　　from dba_indexes
　　where table_owner='BILL'
　　2、保证上面提取的语句执行过后，通过一段时间的观察，查看下列语句得出索引使用的情况，获取那些索引不常用的信息
　　select io.name index_name, t.name table_name,
　　decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
　　decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
　　ou.start_monitoring start_monitoring,
　　ou.end_monitoring end_monitoring
　　from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
　　where io.owner# = (select user# from sys.user$ where name='&用户名')
　　and i.obj# = ou.obj#
　　and io.obj# = ou.obj#
　　and t.obj# = i.bo#
　　3、观察第2步查询出来的结果，删除不常使用的索引，控制索引的数量。
　　对此大家应该掌握了如何观察和控制索引的方法，希望大家能熟悉并经常在工作中应用。
　　4.在导入导出中最好禁用索引和nologging
　　使用EXP/IMP工具进行数据迁移时，可以考虑对索引进行两种方式控制以提高EXP/IMP的速度。
　　1、就是不使用索引，等数据迁移过来后，在新的数据库中手动建立自己需要的索引，毕竟数据更重要，索引可以慢慢建，选择性的建。这样就在EXP的选项中设置INDEXES=N，这样加快了导出的速度也加快的导入的速度。
　　2、在IMP的时候选择INDEXFILE参数，让索引先生成文件，先不要导入数据库中，等数据全部导入库后再建选择性的从INDEXFILE中建索引，即便把INDEXFILE的索引全部建到新库中，大数据迁移时，分步操作的速度也更快。