Oracle索引监控,重建,不可见

原创 2013年12月05日 22:22:47
       简单总结了下 Oracle索引管理的一些东东。命令都很简单,但要注意下我列出的一些功能特性,执行后果,常见问题。
   以及一点点小技巧。
   一. 如何监控索引
   二 10g失效及重建索引及常见问题
   三. 11g的索引不可见特性

一.监控索引
1.  从DBA用户下执行下面代码,    
     alter index DEMO.  IDX_TEST monitoring usage;  
2. 以索引或表所属用户登录后在  v$object_usage 查看监控结果。
   SELECT * FROM v$object_usage; 
3. 取消监控
   alter index DEMO.IDX_TEST nomonitoring usage; 

  技巧1: 
  可通过修改下面代码,批量执行监控和取消监控动作。   
set serveroutput on
declare
  v_owner varchar2(20) := 'DEMO';  -- 指定要监控哪个用户下的表
  v_table varchar2(60) := '%';            -- 依表名过滤, '%' 代表为所有可访问表
  v_type  varchar2(15) := 'monitoring'; -- 值 monitoring / nomonitoring 分别对应监控和取消监控
  --v_cmd   varchar2(255);
begin      
  -- 如使用表所属用户登录,将dba_indexes改为下面这个user_indexes 为准的即可。
   -- select table_owner||'.'||index_name as idxname  
   --   from user_indexes where  table_name like v_table
   for i in (select table_owner||'.'||index_name as idxname 
              from dba_indexes   
              where owner = v_owner  and table_name like v_table
             ) loop
     execute immediate 'alter index '||i.idxname||' '||v_type||' usage';     
     -- v_cmd := 'execute immediate ''alter index '||i.idxname||' '||v_type||' usage''';      
     -- dbms_output.put_line(v_cmd);      
  end loop;
 end;
技巧2:
    你会发现,虽然取消索引监控,或者很久之前有一个索引被执行过,这些索引在v$object_usage 视图中
    仍能查到,很不爽。 
         执行    delete from object_usage; 即可清理掉这些。

 二.10g失效及重建索引及常见问题
   
1. 使用索引失效
   命令:   alter index  idx_test2_id unusable;
   索引失效后:
        a. 索引并不会被删掉,即还会占用数据库空间.
        b. 可在索引视图中查到状态为 "UNUSABLE"。
        c. 表不会再与这个索引做同步更新。 
        d. 执行计划不会再使用这个索引
        c. 对表的影响:
           对表insert 会出现 ORA-01502错误,提示 "这类索引的分区处于不可用状态"
           对表update正常。
           对表 select  正常。

      --查询所有失效索引
      select a.status,a.* from dba_indexes a where status = 'UNUSABLE'
 2.  通过重建索引,使用索引重新可用。
       alter index  idx_test2_id rebuild;
       重建尽量不要在业务高峰期做,大表将会花很多时间。
       对于唯一索引的重建,有可能报ORA-01452,说明列有重复值存在,不能重建这个索引。

 注意事项:
    除了手工执行命令使索引失效外。 还会有其它原因造成的一些索引自动失效。
    最常见的是更改了表的表空间,则此表相关的所有索引全自动失效。
      -- 更改表空间
      alter table 表名 move tablespace 表空间;
   另一种常见情况是10g的数据库自动采集程序造成的索引失效。
     索引的失效有时后果是很严重的,所以DBA要经常检查索引状态,特别是对于一些关键索引。

三.11g的索引不可见特性
    1. 索引变为不可见
        alter index idx_test2_id invisible;
          不可见后:
            a. 照样表会与这个索引同步更新。
            b. 执行计划不会再使用这个索引 
            c. 对表的影响:
                  除了执行计划看不到外,索引的约束力仍在。
    2. 使 索引可见
        alter index idx_test2_id  visible;
        比起重建索引,省事多了,因为一直有在同步更新。

另外,在项目开发和维护中,如何正确使用索引,有兴趣的话可以看下我写的这篇:  DBA写给开发的索引经验

 再附上点重建索引问题处理
 如果重建索引异常中止,会报:
ORA-08104: 该索引对象 xxxxx 正在被联机建立或重建

 可使用下面方法清掉报错索引对象的相关信息
 DECLARE
    RetVal BOOLEAN;
    OBJECT_ID BINARY_INTEGER;
    WAIT_FOR_LOCK BINARY_INTEGER;
  
  BEGIN
    OBJECT_ID := xxxxx ;
    WAIT_FOR_LOCK := NULL;
    RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
    COMMIT;
 END;
 /

如果还报下面错误
ORA-08104: this index object xxxx is being online built or rebuilt
可用下面方式清理
DECLARE
isClean BOOLEAN;
BEGIN
  isClean := FALSE;
  WHILE isClean=FALSE
  LOOP
    isClean := dbms_repair.online_index_clean(dbms_repair.all_index_id, dbms_repair.lock_wait);
    dbms_lock.sleep(5);
  END LOOP;
END;

MAIL:xcl_168@aliyun.com
Blog:http://blog.csdn.net/xcl168
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

另一篇 Oracle索引监控

昨天用脚本生成一堆索引 监控语句 select'alter index '||index_name||' monitoring usage;' from user_indexes; alter ...

Oracle 监控索引的使用率

Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管如此,该方式仅提供的是索引是否被使用。索引被使用...

Oracle 监控索引使用率脚本分享

Oracle提供了索引监控特性来判断索引是否被使用 。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在 。尽管如此,该方式仅提供的是索引是否被使用 。索引...

Oracle 索引监控(monitor index)

合理的为数据库表上创建战略性索引,可以极大程度的提高了查询性能。但事实上日常中我们所创建的索引并非战略性索引,恰恰是大量冗余或是根本没有用到的索引耗用了大量的存储空间,导致DML性能低下。Oracle...

Oracle 监控索引的使用率

Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管如此,该方式仅提供的是索引是否被使用。索引被使用...

73、oracle索引的监控

最近的研究发现 Oracle 数据库所使用的索引从来没有达到过可用索引数的1/4, 或者其用法与其开始设计的意图不相同。未用的索引浪费空间,而且还会降低 DML 的速度,尤其是 UPDATE 和 ...
  • shRootm
  • shRootm
  • 2012年03月08日 10:46
  • 93

oracle 对索引进行监控与分析

查看表索引信息需要用到dba_indexes, dba_ind_columns,查看表索引列信息,包括复合索引的脚本: WITH nonformat AS (SELECT ...

Oracle 数据库索引监控 注意事项

对于dml操作来说,索引对于数据库是一个性能负担.如果索引没有被有效的使用,那么其存在性就值得从新考虑. 1. 从oracle9i开始,oracle允许你监视索引的使用: sql> conne...

DB2中索引(index)失效的原因、确认、重建及监控

本文总结了DB2中索引失效的几种常见原因,如何找出所有失效的索引以及判断某个索引是否失效,如何重建失效的索引,以及如何监控索引创建/重建的进度 索引失效的原因 1.) 如果logindexbu...

从实例看oracle的索引监控与无效索引维护

一般观点认为oracle数据库使用的索引不会超过设计时创建索引总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle索引监控,重建,不可见
举报原因:
原因补充:

(最多只允许输入30个字)