oracle笔记——索引

数据库运行一定的时间后,索引方面可能出现两种问题:


一、新建了从未被命中的索引,占用空间资源,增加DML操作的性能消耗;

二、数据的变动导致索引碎片的增加,具体表现为索引所占用空间增加,查询效率下降;


问题一,可以对占用空间过大的索引进行分析,具体步骤:

1、查看当前用户所用表空间下的索引情况

select t.segment_name,t1.TABLE_NAME,t.bytes / 1024 / 1024 / 1024 sizeG
  from user_segments t,user_indexes t1
 where t.segment_name=t1.INDEX_NAME order by sizeG desc

2、将占用空间较大的索引开启监控

alter index {index_name} monitoring usage

3、过段时间查看监控结果

select * from SYS.V$OBJECT_USAGE

4、如果USED字段一直为NO,则表示此索引从未被使用过;


问题二:对索引进行分析,分析过程中会造成锁表,建议将数据异地恢复至从库后进行

以下为我的索引分析操作步骤:

1、新建一张临时表存放分析结果

create table TEST
(
  name        VARCHAR2(30),
  lf_rows     NUMBER,
  lf_blks     NUMBER,
  del_lf_rows NUMBER,
  ratio       NUMBER
)

2.通过存储过程循环分析,并将分析结果写入结果表中,当前会话分析第二个索引后,分析结果会前第一个覆盖,因此每分析完一笔保存一笔

[以下过程只分析索引占用空间排名前100的索引]

create or replace procedure p_index_check is
  cursor index_name is
    select segment_name from (select t.segment_name,t1.TABLE_NAME,t.bytes / 1024 / 1024 / 1024 sizeG
  from user_segments t,user_indexes t1
 where t.segment_name=t1.INDEX_NAME order by sizeG desc) where rownum<=100;
  sqlt varchar(200);
begin
  for iname in index_name loop
    begin
      sqlt := 'analyze index ' || iname.segment_name || '  validate structure ';
      EXECUTE IMMEDIATE sqlt;
      insert into TEST select t.name,
       t.lf_rows,
       t.lf_blks,
       t.del_lf_rows,
       (t.del_lf_rows / t.lf_rows) * 100 ratio
  from index_stats t where t.name=iname.segment_name;
  commit;
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
    end;
  end loop;
end;

3、查看结果表上的ratio字段,其值为碎片比例,如索引程度过高

使用ALTER INDEX indexname REBUILD ONLINE;重建

【生产环境记得添加online,以免锁表】

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wysyl1990/article/details/51556698
个人分类: oracle调优
上一篇oracle调优笔记——归档
下一篇Oracle11g count使用函数时在性能与返回结果的差别
想对作者说点什么? 我来说一句

oracle索引分区实践笔记

2014年03月12日 412KB 下载

Oracle 入门文档

2011年05月12日 62KB 下载

Oracle 入门文档2

2011年06月20日 122KB 下载

oracle笔记总结

2011年06月23日 20KB 下载

Oracle笔记Oracle笔记

2011年08月22日 147KB 下载

oracle学习笔记.docx

2010年08月25日 34KB 下载

oracle学习详解 笔记

2016年09月25日 43KB 下载

没有更多推荐了,返回首页

关闭
关闭