简单总结了下 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 正在被联机建立或重建
可使用下面方法清掉报错索引对象的相关信息
如果还报下面错误
ORA-08104: this index object xxxx is being online built or rebuilt
可用下面方式清理
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