方法一:在索引上应用monitoring usage子句,然后查询v$object_usage表,如下所示:
BEGIN
FOR r IN (SELECT index_name FROM user_indexes) LOOP
BEGIN
EXECUTE IMMEDIATE 'alter index ' || r.index_name ||
' monitoring usage';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
SELECT t.index_name, t.table_name, t.used, t.start_monitoring
FROM v$object_usage t
WHERE t.monitoring = 'YES';
注意:查询结果used为NO的表示从监控开始,该索引从未使用过。
方法二:通过检查视图v$sql_plan。这样可以避免对索引使用 monitoring usage子句,但是一些使用过的索引的sql有可能被挤出缓存,查询语句如下所示:
WITH in_plan_objects AS
(SELECT DISTINCT t.object_name
FROM v$sql_plan t
WHERE t.object_owner = 'H2')
SELECT t1.table_name,
t1.index_name,
CASE
WHEN t2.object_name IS NULL THEN
'NO'
ELSE
'YES'
END AS in_cached_plan
FROM user_indexes t1
LEFT OUTER JOIN in_plan_objects t2
ON (t1.index_name = t2.object_name);
注意:查询结果 in_cached_plan为NO的表示在缓存sql计划中找不到该索引。
没有被使任何查询使用的非键索引(非主键、非唯一键、非外键)或许可以考虑删除,使用monitoring usage或者查询v$sql_plan可以找出这些索引,方式如上面所提。
BEGIN
FOR r IN (SELECT index_name FROM user_indexes) LOOP
BEGIN
EXECUTE IMMEDIATE 'alter index ' || r.index_name ||
' monitoring usage';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
SELECT t.index_name, t.table_name, t.used, t.start_monitoring
FROM v$object_usage t
WHERE t.monitoring = 'YES';
注意:查询结果used为NO的表示从监控开始,该索引从未使用过。
方法二:通过检查视图v$sql_plan。这样可以避免对索引使用 monitoring usage子句,但是一些使用过的索引的sql有可能被挤出缓存,查询语句如下所示:
WITH in_plan_objects AS
(SELECT DISTINCT t.object_name
FROM v$sql_plan t
WHERE t.object_owner = 'H2')
SELECT t1.table_name,
t1.index_name,
CASE
WHEN t2.object_name IS NULL THEN
'NO'
ELSE
'YES'
END AS in_cached_plan
FROM user_indexes t1
LEFT OUTER JOIN in_plan_objects t2
ON (t1.index_name = t2.object_name);
注意:查询结果 in_cached_plan为NO的表示在缓存sql计划中找不到该索引。
没有被使任何查询使用的非键索引(非主键、非唯一键、非外键)或许可以考虑删除,使用monitoring usage或者查询v$sql_plan可以找出这些索引,方式如上面所提。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1991286/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1991286/