Oracle9i中监视索引的使用

最新推荐文章于 2025-04-15 10:13:50 发布

taocsdn

最新推荐文章于 2025-04-15 10:13:50 发布

阅读量1k

点赞数

文章标签： oracle object table system 数据库脚本

本文链接：https://blog.youkuaiyun.com/taocsdn/article/details/75585

版权

介绍

DBA和开发者都喜欢索引。它们可以加速查询搜索，特别是在一个数据仓库的环境中，因为这时数据库会接收到许多ad-hoc请求。要避免全表搜索，我们一般在每个可能被搜索的列中建立索引。不过索引会占用许多的表空间；在许多的情况下，索引比被索引的表消耗更多的存储空间。在插入和删除行的时候，索引还会引入额外的开销。在Oracle9i之前，要知道一个索引是否被使用是困难的，因此许多数据库都有许多没用的索引。这篇文章的目的就是向你介绍通过Oracle9i中的新特性来辨别未使用的索引。

辨别未使用的索引

Oracle9i提供了一个新的技术来监控索引以辨别索引有否被使用。要开始监控一个索引的使用，使用这个命令：

 ALTER INDEX index_name MONITORING USAGE;

要停止监控一个索引，输入：

 ALTER INDEX index_name NOMONITORING USAGE;

在v$objec_usage视图中包含有索引监控的使用信息。

CREATE OR REPLACE VIEW SYS.V$OBJECT_USAGE (
  INDEX_NAME,
  TABLE_NAME,
  MONITORING,
  USED,
  START_MONITORING,
  END_MONITORING
)
AS
  select io.name, t.name,
         decode(bitand(i.flags, 65536), 0, "NO", "YES"),
         decode(bitand(ou.flags, 1), 0, "NO", "YES"),
         ou.start_monitoring,
         ou.end_monitoring
  from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
  where io.owner# = userenv("SCHEMAID")
    and i.obj# = ou.obj#
    and io.obj# = ou.obj#
    and t.obj# = i.bo#
/

COMMENT ON TABLE SYS.V$OBJECT_USAGE IS "Record of index usage"
/

GRANT SELECT ON SYS.V$OBJECT_USAGE TO "PUBLIC"
/

该视图显示了由数据库中收集来的索引使用统计。以下就是该视图中的列的描述：

　　INDEX_NAME: sys.obj$.name 中的索引名字
　　TABLE_NAME: sys.obj$obj$name 中的表名
　　MONITORING: YES (索引正在被监控), NO (索引没有被监控)
　　USED: YES (索引已经被使用过), NO (索引没有被使用过)
　　START_MONITORING: 开始监控的时间
　　END_MONITORING: 结束监控的时间

所有被使用过至少一次的索引都可以被监控并显示到这个视图中。不过，一个用户只可以接收它自己模式中的索引使用。Oracle并没有提供一个视图来接收所有模式中的索引。要接收所有模式的索引使用，以SYS用户登录并且运行以下的脚本（注意：这并不是Oracle提供的一个脚本。v$all_object_usage是一个自定义的视图。它包含多一个列，即索引的拥有者）

$ cat all_object_usage.sql
  CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE
  (
    OWNER,
    INDEX_NAME,
    TABLE_NAME,
    MONITORING,
    USED,
    START_MONITORING,
    END_MONITORING
  )
  AS
    select u.name, io.name, t.name,
           decode(bitand(i.flags, 65536), 0, "NO", "YES"),
           decode(bitand(ou.flags, 1), 0, "NO", "YES"),
           ou.start_monitoring,
           ou.end_monitoring
    from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u
    where i.obj# = ou.obj#
      and io.obj# = ou.obj#
      and t.obj# = i.bo#
      and io.owner# = u.user#
  /

  COMMENT ON TABLE SYS.V$ALL_OBJECT_USAGE IS 
  "Record of all index usage - developed by Daniel Liu"
  /

  GRANT SELECT ON SYS.V$ALL_OBJECT_USAGE TO "PUBLIC"
  /

  CREATE PUBLIC SYNONYM V$ALL_OBJECT_USAGE
  FOR SYS.V$ALL_OBJECT_USAGE 
  /

每次你使用MONITORING USAGE，视图就会为特别的索引而复位。所有以前的使用信息都会被清除和复位，并且会记录下一个新的启动时间。每次你执行NOMONITORING USAGE，就不会进行进一步的监控；监视期间的结束时间就会被记录下来。如果你删除一个正在被监控的索引，该索引的相关信息就会由V$OBJECT_USAGE和V$ALL_OBJECT_USAGE视图中删除。

辨别数据库中所有未被使用的索引

这个脚本将会启动监控所有的索引：

#####################################################################
## start_index_monitoring.sh                                       ##
#####################################################################
#!/bin/ksh
# input parameter: 1: password
# 2: SID
if (($#<1))
then
  echo "Please enter "system" user password as the first parameter !"
  exit 0
fi
if (($#<2))
then
  echo "Please enter instance name as the second parameter!"
  exit 0
fi
sqlplus -s < system/$1@$2
set heading off
set feed off
set pagesize 200
set linesize 100
spool start_index_monitoring.sql
select "ALTER INDEX "||OWNER||"."||INDEX_NAME||" MONITORING USAGE;" 
from dba_indexes
where owner not in ("SYS","SYSTEM","OUTLN","AURORA/$JIS/$UTILITY/$");
spool off
exit
!
sqlplus -s < oracle/$1@$2
@./start_index_monitoring.sql
exit
!

这个脚本将会停止监控全部的索引：

#####################################################################
## stop_index_monitoring.sh                                        ##
#####################################################################
#!/bin/ksh
# input parameter: 1: password
# 2: SID
if (($#<1))
then
  echo "Please enter "system" user password as the first parameter !"
  exit 0
fi
if (($#<2))
then
  echo "Please enter instance name as the second parameter!"
  exit 0
fi
sqlplus -s < system/$1@$2
set heading off
set feed off
set pagesize 200
set linesize 100
spool stop_index_monitoring.sql
select "ALTER INDEX "||OWNER||"."||INDEX_NAME||" NOMONITORING USAGE;"
from dba_indexes
where owner not in ("SYS","SYSTEM","OUTLN","AURORA/$JIS/$UTILITY/$");
spool off
exit
!
exit
sqlplus -s < oracle/$1@$2
@./stop_index_monitoring.sql
exit
!

这个脚本将会为所有未被使用的索引产生一个报表：

#####################################################################
## identify_unused_index.sh                                        ##
#####################################################################
#!/bin/ksh
# input parameter: 1: password
# 2: SID
if (($#<1))
then
  echo "Please enter "system" user password as the first parameter !"
  exit 0
fi
if (($#<2))
then
  echo "Please enter instance name as the second parameter!"
  exit 0
fi
sqlplus -s < system/$1@$2
set feed off
set pagesize 200
set linesize 100
ttitle center "Unused Indexes Report" skip 2
spool unused_index.rpt
select owner,index_name,table_name,used
from v/$all_object_usage
where used = "NO";
spool off
exit
!

以下就是一个未被使用索引报表的例子：

Unused Indexes Report
  
OWNER  INDEX_NAME                     TABLE_NAME        USE
------ ------------------------------ ----------------- --- 
HR     DEPT_ID_PK                     DEPARTMENTS       NO
HR     DEPT_LOCATION_IX               DEPARTMENTS       NO
HR     EMP_DEPARTMENT_IX              EMPLOYEES         NO
HR     EMP_EMAIL_UK                   EMPLOYEES         NO
HR     EMP_EMP_ID_PK                  EMPLOYEES         NO
HR     EMP_JOB_IX                     EMPLOYEES         NO
HR     EMP_MANAGER_IX                 EMPLOYEES         NO
HR     EMP_NAME_IX                    EMPLOYEES         NO
HR     JHIST_DEPARTMENT_IX            JOB_HISTORY       NO
HR     JHIST_EMPLOYEE_IX              JOB_HISTORY       NO
HR     JHIST_EMP_ID_ST_DATE_PK        JOB_HISTORY       NO
HR     JHIST_JOB_IX                   JOB_HISTORY       NO
HR     JOB_ID_PK                      JOBS              NO
HR     LOC_CITY_IX                    LOCATIONS         NO
HR     LOC_COUNTRY_IX                 LOCATIONS         NO
HR     LOC_ID_PK                      LOCATIONS         NO
HR     LOC_STATE_PROVINCE_IX          LOCATIONS         NO
HR     REG_ID_PK                      REGIONS           NO
OE     INVENTORY_PK                   INVENTORIES       NO
OE     INV_PRODUCT_IX                 INVENTORIES       NO
OE     INV_WAREHOUSE_IX               INVENTORIES       NO
OE     ITEM_ORDER_IX                  ORDER_ITEMS       NO
OE     ITEM_PRODUCT_IX                ORDER_ITEMS       NO
OE     ORDER_ITEMS_PK                 ORDER_ITEMS       NO
OE     ORDER_ITEMS_UK                 ORDER_ITEMS       NO
OE     ORDER_PK                       ORDERS            NO