oracle索引的监控

有研究表明,oracle数据库使用的索引不会超过总数的25%,或者不以它们被期望的使用方式使用.在实际应用中,调优速度较慢的查询时,经常发现执行的sql调用了垃圾索引,而不是我们设计时建立的索引.所以我们有必要通过监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能.

在oracle8i中,确定使用了哪个索引的方法意味着要对存在于共享区中的所有语句运行EXPLAIN PLAN然后查询计划表中的operation列,从而识别有object_owner和object_name列所确定的那个索引上的索引访问.

在oracle9i中,情况会简单一些,因为有一个新的数据字典V$SQL_PLAN存储了实际计划,这些计划用于执行共享SQL区中的语句.

SQL> desc v$sql_plan;

Name              Type           Nullable Default Comments

----------------- -------------- -------- ------- --------

ADDRESS           RAW(8)         Y                        

HASH_VALUE        NUMBER         Y                        

CHILD_NUMBER      NUMBER         Y                        

OPERATION         VARCHAR2(60)   Y                        

OPTIONS           VARCHAR2(60)   Y                         

OBJECT_NODE       VARCHAR2(20)   Y                        

OBJECT#           NUMBER         Y                        

OBJECT_OWNER      VARCHAR2(30)   Y                        

OBJECT_NAME       VARCHAR2(64)   Y                        

OPTIMIZER         VARCHAR2(40)   Y                        

ID                NUMBER         Y                        

PARENT_ID         NUMBER         Y                        

DEPTH             NUMBER         Y                        

POSITION          NUMBER         Y                        

SEARCH_COLUMNS    NUMBER         Y                        

COST              NUMBER         Y                        

CARDINALITY       NUMBER         Y                        

BYTES             NUMBER         Y                         

OTHER_TAG         VARCHAR2(70)   Y                        

PARTITION_START   VARCHAR2(10)   Y                        

PARTITION_STOP    VARCHAR2(10)   Y                        

PARTITION_ID      NUMBER         Y                         

OTHER             VARCHAR2(4000) Y                        

DISTRIBUTION      VARCHAR2(40)   Y                        

CPU_COST          NUMBER         Y                        

IO_COST           NUMBER         Y                        

TEMP_SPACE        NUMBER         Y                        

ACCESS_PREDICATES VARCHAR2(4000) Y                        

FILTER_PREDICATES VARCHAR2(4000) Y                        

我们可以通过联立动态字典v$sql_plan和dba_indexes获得索引的使用信息.

SQL> SELECT DISTINCT SP.OPERATION, DI.INDEX_NAME, DI.TABLE_NAME

  2    FROM V$SQL_PLAN SP, DBA_INDEXES DI

  3   WHERE SP.OBJECT_OWNER = DI.OWNER

  4     AND SP.OBJECT_NAME = DI.INDEX_NAME

  5     AND SP.OPERATION = 'INDEX'

  6     AND SP.OBJECT_OWNER <> 'SYS'

  7   ORDER BY DI.TABLE_NAME;

SQL> /

    这种基于共享SQL区的信息来识别索引使用情况的方法的最大弊端在于可能搜集不到完整的信息.共享SQL是一个动态结构,除非能对它进行足够频繁的采样,否则在有关索引使用的情况被搜集之前,sql语句可能就已经因为老化被移出缓冲区了.

    Oracle9i提供了解决这个问题的方案.即它为alter index提供了一个monitoring usage子句.当启用monitoring usage时,oracle在数据字典v$object_usage中的used字段记录简单的yes或no值,以指出在监控间隔期间某个索引是否被使用.

SQL> desc v$object_usage;

Name             Type         Nullable Default Comments                                        

---------------- ------------ -------- ------- ------------------------------------------------

INDEX_NAME       VARCHAR2(30)                  Name of the index                               

TABLE_NAME       VARCHAR2(30)                  Name of the table upon which the index was build

MONITORING       VARCHAR2(3)  Y                Whether the monitoring feature is on             

USED             VARCHAR2(3)  Y                Whether the index has been accessed             

START_MONITORING VARCHAR2(19) Y                When the monitoring feature is turned on        

END_MONITORING   VARCHAR2(19) Y                When the monitoring feature is turned off   

下面介绍一下这种索引监控的方法与部署步骤.下面的测试在plsql developer中测试通过.

1,查询v$object_usage,确定监控还没开始,没有返回记录

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

2,如果是监控某个用户建立的索引,则可以使用该用户名称登陆,然后执行下面语句获得alter index的语句.

select 'alter index '||index_name||' monitoring usage;'

from user_indexes

where index_type='NORMAL';

alter index CR_INVENTORY_ITEM_PK monitoring usage;

alter index CR_INVENTORY_ITEM_U1 monitoring usage;

…

alter index T_GOODS_SKU_ITEMTYPE monitoring usage;

alter index ZZZZZ monitoring usage;

如果需要监控多个用户的索引,则需要使用拥有sysdba权限的用户登陆plsql developer,然后执行下面语句.

SELECT 'alter index ' || OWNER || '.' || INDEX_NAME || ' monitoring usage;'

  FROM DBA_INDEXES

 WHERE INDEX_TYPE = 'NORMAL'

   AND owner IN (‘TCLZB’,'PERFECT');

alter index TCLZB.I_U_TRANSET monitoring usage;

alter index TCLZB.I_U_TRANTABLE monitoring usage;

alter index TCLZB.I_U_SIMSID monitoring usage;

alter index TCLZB.I_U_AREA monitoring usage;

alter index TCLZB.I_U_AREA1 monitoring usage;

alter index TCLZB.I_U_BAND monitoring usage;

…

alter index TCLZB.I_U_BAND1 monitoring usage;

将上面得到的语句在plsql developer中执行之后则可以开始对对应的索引进行监控.初始化的所有索引used状态值都是no.

3, 经过合适的时间(比如一个星期)之后我们可以继续下面的步骤,首先我们可以查看一下索引的使用比率.

SELECT ROUND(100 * SUM(DECODE(USED, 'YES', 1, 0)) / COUNT(INDEX_NAME), 2)

  FROM V$OBJECT_USAGE;

4,然后我们可以找出那些没有使用过的索引.

SELECT INDEX_NAME,table_name, MONITORING, USED, START_MONITORING, END_MONITORING

  FROM V$OBJECT_USAGE

 WHERE USED = 'NO';

5,找出之后我们就要判断哪些是我们建立时希望他使用的,根据找出的表名称table_name我们可以到dba_source中找出那些使用到这个表的查询语句.针对这些语句执行优化操作.

SELECT ds.owner||'.'||ds.NAME OName,ds.type,ds.line,ds.text

  FROM DBA_SOURCE DS

 WHERE INSTR(UPPER(DS.TEXT), UPPER('&请输入要检索的对象名称')) > 0  ;

6,停止索引监控,可以使用如下代码生成停止监控的语句.就是将启用中的monitoring修改为nomonitoring.

SELECT 'alter index ' || OWNER || '.' || INDEX_NAME || ' nomonitoring usage;'

  FROM DBA_INDEXES

 WHERE INDEX_TYPE = 'NORMAL'

   AND owner IN (‘TCLZB’,'PERFECT');

7,执行以下查询,确认监控结束,一定切记终止索引监控,因为监控也会使用一定的资源.返回记录条数为0表示监控已终止.

SELECT INDEX_NAME, MONITORING, USED, START_MONITORING, END_MONITORING

  FROM V$OBJECT_USAGE

  WHERE end_monitoring IS NULL;

9,删除v$object_usage中的记录.

需要使用sysdba权限用户登陆.

①grant delete on v$object_usage to public;

②sqlplus /nolog

connect sys/change_on_install as sysdba

delete from v$object_usage;

commit;

③revoke delete on v$object_usage from public;